December 12, 2013 at 1:33 pm
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.
December 12, 2013 at 3:35 pm
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
December 13, 2013 at 6:56 am
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.
December 13, 2013 at 7:34 am
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
December 16, 2013 at 7:08 am
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.
December 16, 2013 at 7:14 am
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
December 23, 2013 at 1:30 pm
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.
December 23, 2013 at 1:44 pm
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
December 23, 2013 at 1:46 pm
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
December 23, 2013 at 1:46 pm
Ah - Glad you found something that works. 🙂
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply