SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication Synchronization Stalls


Replication Synchronization Stalls

Author
Message
alhajirr
alhajirr
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 215
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.
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 Visits: 3650
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

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
alhajirr
alhajirr
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 215
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.
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 Visits: 3650
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

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
alhajirr
alhajirr
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 215
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.
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 Visits: 3650
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

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
alhajirr
alhajirr
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 215
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.
alhajirr
alhajirr
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 215
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


David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 Visits: 3650
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

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3340 Visits: 3650
Ah - Glad you found something that works. Smile

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search