Implementing the SQL Server Multiple Publishers and Central Subscriber Replication Topology

Extra Considerations

This article shows how to implement the central subscriber and multiple publishers topology. The article concentrates on the setup and configuration, but there are other issues to be aware of. For example:

(a) How do we go about adding new columns? Adding the column on one server will be done as per usual, with sp_repladdcolumn, which is then propagated to the subscriber on synchronization. Performing the same action on the other publisher might be expected to result in an error on synchronization, as an identical column can't be added again to the same central subscriber. However, it is only the column added to P1 which gets propagated to the subscriber. Adding the new column to P2 doesn't result in an error or the Alter Table being run on the subscriber because of the declared article properties - 'Keep the existing table unchanged' - mentioned above. So, as long as we know which publisher drives the changes, this is exactly what we want.

(b) How can sp_addscriptexec be used (to e.g., add a new index)? Really, there's no need to issue sp_addscriptexec on any publisher other than P1, and again use this server to drive proceedings. In fact, as we'll only ever have one subscriber, we could just as well run scripts manually on the subscriber itself. If we do try to use sp_addscriptexec on other publishers, we have to be more cautious and be sure to wrap any statements in 'If exists...' clauses, or alternatively set the @skiperror to 1 to ensure no problems.

Visit the author's website: www.replicationanswers.com.

Copyright 2005 by the author.