Replication of procs/views/funcs with interdependencies.
-
I am trying to implement a snapshot replication for a bunch of stored procedures, views and user defined functions. Most of these procs/views/funcs reference each other, to avoid duplication of code and ease maintanence when changes to code are required.
First I tried placing them all in one publication, however I run into problems as views and funcs would get applied to the subscriber before there dependant objects get applied, causing the syncronisation to stop and fail.
I then tried splitting the procs/views/funcs into a series of separate snapshot publications, based on there dependencies, with each subseqent publication containing object dependant on the objects in previous ones. However when I select the first publication in the Replication monitor and start syncronising, it starts to apply the last publication, which promptly fails due to missing dependant objects!
How should I be handling the interdependancies of views and funcs?
Is there anyway to force the order publications are applied to the subscriber by the distribution agent?
Any other ideas, aside from duplicating code all over the shot?
I'm using SQL Server 2000 on publisher, distributor and subscriber.
-
OK, so I'm replying to my own posts, bit sad, but someone else in future may find this useful.
Basically my plan of attack was to still split everything into many publications, based on the interdependencies of objects. Before this wasn't working because all the snapshots were using the same distribution agent and you had no control over which one it applied first.
To overcome this, I found the option to have independent distribution agents for each snapshot. Check it in the 'Snapshot Properties', 'Subscription Options' tab. Only available if there's no subscribers for the publication, so you may have to remove all subscriptions before you can edit an existing publication.
Since each distribution agent is then separate, you can schedule them all to run one after another.
I would be interested to know if this dependency problem has been fixed in SQL Server 2005. Anyone know?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply