Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Merge Replication - Views, Functions and Stored procedures - very complex database schema.

Unanswered Merge Replication - Views, Functions and Stored procedures - very complex database schema.

  • Friday, November 30, 2012 6:39 PM
     
     
    Sign In to Vote
    0

    I have been trying to replicate a database now for a few months using Merge replication with Web Sync. Replicating the tables is pretty straight forward. The tables are currently being replicated via the Intranet and the internet with no problem.

    The problem is to replicate the views, stored procedures and Functions because the database schema is extremely complex. The snapshot process keeps failing due to the relationships between these objects. Almost all the views, sp and functions are all referencing each other in their various TSQL codes. The relationship between these objects are so complex that after two months I don't seems to be making any process.

    I have tried using @processing_order to set the processing order of these objects for the snapshot process but due to the complex relation between these objects, the snapshot is still unsuccessful.

    Backup and Restore is not an option due to bandwidth and logistic reason.

    The Publisher is SQL Server 2012 and the subscribers are 2005.

    Please help.

    Regards

    MJ




     

All Replies

  • Monday, December 03, 2012 2:27 PM
     
     
    Sign In to Vote
    0

    I need help on how to replicate a database with a complex schema as described above. The issue is with the snapshot process. The tables are replicating with no problem.

    I’m beginning to think that maybe merge replication isn't design to replicate databases with complex schemas.

    I will greatly appreciate any help I can get.

    Regards

    MJ



    • Edited by Momentum101 Monday, December 03, 2012 2:28 PM
    •  
     
  • Monday, December 03, 2012 4:29 PM
    Moderator
     
     
    Sign In to Vote
    0
    Can you replicated the views/procs, etc as part of a post snapshot script.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Monday, December 03, 2012 6:20 PM
     
     
    Sign In to Vote
    0

    No I haven’t. I figure it will be unsuccessful because I have tried running the scripts manually after replicating the tables and the scripts failed.

    I separated the scripts into three categories: view, function and Procs. I ran them separately and they failed.

    These scripts are failing because:

    The codes in the views are joining tables, other views and functions.

    The codes in the function are also referencing other views and other function.

    The stored procedures have joins that are joining tables, Views, and are also referencing function.

    So, when I run the scripts they check for dependencies (other view, function, schemas etc.).

    It’s seemed as if all the objects in this database are connected to each other and it makes it extremely difficult to use @processing_order.

    Regards

    MJ

        

     
  • Monday, December 03, 2012 6:25 PM
    Moderator
     
     
    Sign In to Vote
    0
    Processing_Order controls which article is processed first after the snapshot has been applied. It has no role in snapshot creation.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Monday, December 03, 2012 8:14 PM
     
     
    Sign In to Vote
    0

    I just generated a script of all the Views/Procs/functions, set it to run as a post snapshot script, reinitialize a snapshot. Then I get this error message.

    The schema script 'XXXXXXXXX.sql' could not be propagated to the subscriber.

    This tells me that it really doesn’t make a difference whether the script is run manually or as part of a post snapshot script. It’s looking for objects at the subscriber that are not there.

    All the tables were replicated with no problem.

    Regards

    MJ




    • Edited by Momentum101 Monday, December 03, 2012 8:15 PM
    • Edited by Momentum101 Monday, December 03, 2012 8:15 PM
    •  
     
  • Monday, December 03, 2012 8:18 PM
    Moderator
     
     
    Sign In to Vote
    0
    That normally means that the merge agent can't access the script. Can you verify it has permission to access it? I normally put my script in c:\temp on the publisher.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Monday, December 03, 2012 9:30 PM
     
     
    Sign In to Vote
    0

    For  simplicity, the publisher and Subscriber are on the same instance of SQL Server 2012/Windows 2008 R2. The Merge Agent is running under the SQL Server Agent account, which has Administrator permission on the OS as well as SA in SQL Server.

    I rant the script from C:\temp and still get the same error message. I have tried running the same script from several other other directories with no success.

    Let me ask you a question:

    What is difference between running a script as a post snapshot script and manually running the same script on the subscriber besides automation?

    Regards

    MJ



    • Edited by Momentum101 Monday, December 03, 2012 9:31 PM
    • Edited by Momentum101 Monday, December 03, 2012 9:33 PM
    •