Microsoft KB Archive/320123

= HOW TO: Add Columns in a Rollup Subscriber Topology =

Article ID: 320123

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition

-



This article was previously published under Q320123



IN THIS TASK
SUMMARY
 * Example 1: Simple Rollup Subscriber
 * Example 2: Republisher Scenario
 * Example 3: Multi-Directional Replication

Additional Information REFERENCES



SUMMARY
This step-by-step article includes examples that describe how to add columns to a replication topology that includes a rollup subscriber. A rollup subscriber is a subscriber that consolidates multiple publishers into a single subscribing table. The same basic steps apply whether the publications are snapshot, transactional, merge, or a combination of one or more types of publications.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. back to the top

Topology

 * Publishing database A: dbA
 * Publishing database B: dbB
 * Rollup Subscriber: dbRollup
 * Data flows one way, from dbA and dbB to dbRollup.
 * End-users only make updates at dbA and dbB.

Steps

 * 1) Stop all activity in your source databases for the published table. In this example, stop all activity on your table in dbA and dbB. You can stop activity by removing access, placing the database in dbo use only (if none of your users have dbo access), or by whichever method you generally use to prevent access to the database system.. For more information about setting the dbo use only option, see the &quot;sp_dboption&quot; topic in SQL Server 2000 Books Online.
 * 2) Verify that all transactions have replicated completely from dbA and dbB to dbRollup. It is important that no transactions are pending.
 * 3) Stop the agents that move data between your source databases (dbA and dbB) and your rollup subscriber (dbRollup).
 * 4) Take any backups that are necessary for your computer.
 * 5) Add the columns to dbA and dbB by using the steps in the &quot;Schema Changes on Publication Databases&quot; topic in SQL Server Books Online.
 * 6) Verify that the columns have been added in dbRollup.
 * 7) Start the agents that move data between dbA and dbB.
 * 8) Resume normal activity. If you have turned on the dbo use only option, or otherwise limited access to your computer, you have to reverse those steps.

back to the top

Topology

 * Publishing database A: dbA
 * Publishing database B: dbB
 * Republisher database C: dbC
 * Republisher database D: dbD
 * Rollup Subscriber: dbRollup
 * Data flows one way, from dbA to dbC to dbRollup and from dbB to dbD to dbRollup.
 * Data at the republisher is only populated by way of replication from the source publishers.
 * End-users only make updates to dbA and dbB.

Steps

 * 1) Stop all activity in your source databases dbA and dbB for the published table. You can stop activity by removing access, placing the database in dbo use only (if none of your users have dbo access), or by whichever method you typically use to prevent access to the database system.. For more information about setting dbo use only, see the &quot;sp_dboption&quot; topic in SQL Server 2000 Books Online.
 * 2) Verify that all transactions have replicated completely from dbA to dbC, from dbB to dbD, from dbC to dbRollup, and from dbD to dbRollup. It is important that no transactions that reference the current table structure are pending.
 * 3) Stop the agents that move data to your rollup subscriber. In this case, the agents are the ones that move data from dbC to dbRollup, and from dbD to dbRollup.
 * 4) Take the backups that are necessary for your computer.
 * 5) Add the columns to dbA and dbB by using the steps that are outlined in the &quot;Schema Changes on Publication Databases&quot; topic in SQL Server Books Online. You must add the columns to all source databases that will rollup to the rollup subscriber before the replication agents are restarted.
 * 6) Verify that the columns have replicated from dbA to dbC, and from dbB to dbD.
 * 7) Start the agents that move data between dbC and dbRollup, and between dbD and dbRollup.
 * 8) Verify that the columns have been added in dbRollup.
 * 9) Resume normal activity. If you have turned on the dbo use only setting, or otherwise limited access to your system, you have to reverse those steps.

back to the top

Topology

 * Publishing and subscribing database A: dbA
 * Publishing database B: dbB
 * Republisher and subscribing database C: dbC
 * Republisher database D: dbD
 * Rollup Subscriber and publisher: dbRollup
 * Data flows both ways along the chain from dbA to dbC to dbRollup and back.
 * Data flows one way through dbB to dbD to dbRollup.
 * End-users make updates to dbA, dbB, and dbRollup.

Steps

 * 1) Stop all activity in any database where data modifications may be made. In this example, the database are dbA, dbB, and dbRollup. To stop activity in these databases, you can remove access permissions, place the database in dbo use only (if none of your users have dbo access), or use whichever method you typically use to prevent access to the database system.. For more information about setting dbo use only, see the &quot;sp_dboption&quot; topic in SQL Server 2000 Books Online.
 * 2) Verify that all transactions for all agents have replicated completely to their end destination. It is important that no transactions that reference the current table structure are pending.
 * 3) Stop all the agents in the topology. In this example, the agents include those that move data from dbA to dbC, from dbC to dbRollup, from dbC to dbA, from dbRollup to dbC, from dbB to dbD, and from dbD to dbRollup.
 * 4) Take the backups that are necessary for your computer.
 * 5) Add the columns to dbA and dbB by using the steps in the &quot;Schema Changes on Publication Databases&quot; SQL Server Books Online topic. You must add the columns to all source databases that rollup to the rollup subscriber before the replication agents are restarted.
 * 6) Start the agents that move data from dbA to dbC, and from dbB to dbD.
 * 7) Verify that the columns have replicated from dbA to dbC, and from dbB to dbD.
 * 8) Start the agents that move data between dbC and dbRollup, and between dbD and dbRollup.
 * 9) Verify that the columns have been added in dbRollup.
 * 10) Resume normal activity. If you have turned on the dbo use only option, or otherwise limited access to your system, you have to reverse those steps.

back to the top

Additional Information
When you use SQL Server Enterprise Manager (SEM) to add the column at the source database, you execute the sp_repladdcolumn stored procedure. The call to the stored procedure is replicated to all subscribers including those that come from a republisher. When you are making the change at multiple source databases to replicate to fewer destination databases, the call to the sp_repladdcolumn stored procedure is replicated multiple times. However, there is a check in the sp_repladdcolumn stored procedure that prevents an attempt to actually add the column multiple times. Additionally, the actual call that SEM generates to the sp_repladdcolumn stored procedure has an additional check so that it only calls the sp_repladdcolumn stored procedure if the column does not already exist; therefore, the column is only added one time on the rollup subscriber and no errors result.

back to the top

