Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication Synchronization Stalls Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 1:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:20 PM
Points: 28, Visits: 104
I am setting up a replication on SQL Server 2008 R2. I have three servers: P1, D1, S1 that each act as the Publisher, Distributor, and Subscriber. On P1, I am replicating 7 databases. Each database has a whole mess of SPs, views, indexed views, tables, and functions that I need to replicate. Initially, my thought was to create one publication that contains all these objects. However, during initial synchronization, it keeps stalling when it tries to replicate an object that contains another object that has not been replicated yet. For example, it could try to replicate a stored procedure that contains a function. But the function has not been replicated yet.

So I ended up creating a publication for each type of object. This way, while synchronizing, if it tries to synchronize an SP with the function, it will keep stalling until the function in the other publication has been replicated. So far it works. It's not exactly how I envisioned it since it will keep erroring out until the function is replicated. But I don't know any other way. Since there are dozens of objects in each type, it will be tough for me to go through each one to determine what uses what.

Is there any other solution? Is there a way to tell SQL Server don't worry about the objects within an object and bypass that error?

Thanks in advance for any suggestions.
Post #1522469
Posted Thursday, December 12, 2013 3:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,107, Visits: 3,581
There is a way to bypass data dependencies, but not object dependencies, as far as I know. I can't think why you would want to, honestly. If you require the function for the procedure to build, and the function is not there, then that would be a problem. So, you would want that to error. However, your situation definitely sounds interesting and I'm guessing that the way you fixed it is probably the best solution.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1522508
Posted Friday, December 13, 2013 6:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:20 PM
Points: 28, Visits: 104
Thanks for your reply David.

If a procedure requires a function, I am also replicating that function. However, if the procedure is replicated first, it will error out since the function has not been replicated yet. But if I create a publication for each object type, then it the SP will keep erroring out until the function is replicated under another publication. I know I can enter a -SkipError parameter in the Agent profile to bypass the error but I don't want to go that route in case there is a genuine error.
Post #1522672
Posted Friday, December 13, 2013 7:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,107, Visits: 3,581
Yes, you don't want to skip the error or it will just not replicate that command and then the stored procedure will not be there, or would not be updated. Of course this will only be true for the initialization of those objects. Once they are there it will still work to keep them in sync and they could be in the same publication. It is the initial create sequence that isn't maintained by replication which is what was causing you the problems, correct?

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1522689
Posted Monday, December 16, 2013 7:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:20 PM
Points: 28, Visits: 104
Correct. I ended up going another route. Rather than creating a publication for each object type, I did the first method instead where I created one publication for all objects and changed the parameter for -SkipError to skip errors where it couldn't find the embedded object. Once synchronization is complete, I rerun synchronization without skipping errors. Now since the object it needed is now in the Subscriber database, it replicated any object it skipped in the previous run. Not the cleanest method, but it made everything easier with one publication. Since I am replicating 9 databases, I did not want to create multiple publications per database. That would have been a nightmare.
Post #1523206
Posted Monday, December 16, 2013 7:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,107, Visits: 3,581
Having multiple publications per database isn't really a nightmare. Definitely forces you to use the metadata tables a bit more but nothing too difficult.

Anyway, glad you found a way to get it to work.


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1523209
Posted Monday, December 23, 2013 1:30 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:20 PM
Points: 28, Visits: 104
David, on a similar topic, is there a way I can find out the articles that failed during synchronization? I know I can check the error logs but it doesn't tell me which article failed. It'll just tell me the error (i.e. Invalid column name ''). I would like to get a report with all the errors and the articles to troubleshoot later.
Post #1525623
Posted Monday, December 23, 2013 1:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:20 PM
Points: 28, Visits: 104
David, never mind. I looked through the system replication tables and tried to piece them together to get what I needed. I think this query did it:

select e.id, a.article, e.error_code, e.error_text,  h.comments, e.time from MSrepl_errors e
left join MSrepl_commands c on e.command_id=c.command_id
left join MSarticles a on c.article_id=a.article_id
left join MSdistribution_history h on e.id=h.error_id

Post #1525627
Posted Monday, December 23, 2013 1:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,107, Visits: 3,581
There are a couple tables that you can query; msdb..sysreplicationalerts and distribution.dbo.MSrepl_errors. Both are on the distribution server. Let me know if that doesn't give you the information you need.



David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1525628
Posted Monday, December 23, 2013 1:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,107, Visits: 3,581
Ah - Glad you found something that works. :)

David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #1525629
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse