SQL Server Magazine

Configuring the Bridge Server to Accept the Replication Load
To configure the bridge server to accept the load from both merge and transactional replication, we modify the value of the published_in_tran_pub property to true in each article in the merge replication publication.This property, which was introduced in SQL Server 2000 Service Pack 3 (SP3) is false by default. When true, the published_in_tran_pub property indicates that an article in a merge publication is also published in a transactional publication.

When we change this parameter in sp_changemergearticle, we must invalidate the snapshot and reinitialize the subscribers. Listing 1 shows the complete command syntax for our case in this article. If you were to use the command in your environment, you would need to substitute the publication name and article name in Listing 1 with the proper names for your situation.

The value of 1 for the @force_invalidate_ snapshot parameter means that a new snapshot is required and gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.The value of 1 for the @force_reinit_subscription parameter means that changes to the merge article will cause existing subscriptions to be reinitialized, and it gives permission for the subscription reinitialization to occur.

This command will succeed only if the default setting of @pre_creation_cmd = N'drop' is used on the merge replication article.We can start snapshot agent jobs on the bridge server to regenerate snapshots for all merge publications.

Installing the Demo
I've built a small example to demonstrate the configuration of one SQL Server system as the OLTP server, one SQL Server system as the bridge server, and one SQL Server system as a mobile (merge replication) pull subscriber. Figure 2 shows this demo configuration.This demo uses one table,Test1,created in the Pubs database. We can implement this solution in SQL Server 2000 SP4 or in SQL Server 2005 SP1. It can't be configured in the original release of SQL Server 2005 due to a minor bug, but the problem was resolved in SP1.

To install the demo,follow the steps below:

1. Install three instances of SQL Server 2005 SP1 or SQL Server 2000 SP4. In my demo code, I use the following names for the SQL Server instances: A2005 for the OLTP server, B2005 for the bridge server (merge replication publisher), and C2005 for the mobile server (merge replication subscriber).
2. Create tableTest1 in the Pubs database on each of the three servers by running the script that Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 50532) shows.
3. Using SQL Server Management Studio, configure distribution and publishing on the bridge server. Make the B2005 server its own distributor and the only publisher. Enable the Pubs database for both transactional and merge replication. If you're running SQL Server 2000, enable the OLTP server A2005 and the mobile server C2005 as subscribers.
4. Using Management Studio, configure distribution and publishing on the OLTP server. Make the A2005 server its own distributor and the only publisher. Enable the Pubs database for transactional replication. If you're running SQL Server 2000, enable the bridge server B2005 as a subscriber.
5. Using Management Studio, create a merge publication in the Pubs database on the bridge server B2005. In the sample code,Pubs_Merge_Test1 is used as the name of the publication (see Listing 1). For the purposes of this demo only, configure the merge agent to run continuously.
6. 6. Using the Pull Subscription Wizard in Management Studio on the mobile server C2005, create a new pull subscription to the already configured merge publication (configuring the bridge server B2005 as the distributor and publisher).
7. In Management Studio on the bridge server B2005, run the script (in Listing 1) that changes the published_in_tran_pub property of the Test1 article. If desired, restart the snapshot agent to regenerate a snapshot, then restart the merge agent.
8. In Management Studio on the OLTP server A2005, run the script in Web Listing 2. This script creates the Pubs_Test1 transactional publication in the Pubs database on A2005, adds theTest1 article to this publication, and creates a subscription to this publication on the bridge server B2005. For additional information, see "How to: Configure Nonpartitioned, Bidirectional,Transactional Replication ( Replication Transact-SQL Programming)."
9. In Management Studio on the bridge server B2005, run the script in Web Listing 3. This script creates the Pubs_Test1 transactional publication in the Pubs database on B2005, adds the Test1 article to this publication,and creates a subscription to this publication on the OLTP server A2005.
10. In Management Studio on the bridge server B2005, run the script in Web Listing 4 to install custom stored procedures for transactional replication on the bridge server.
11. In Management Studio on the OLTP server A2005, run the script in Web Listing 5 to install custom stored procedures for transactional replication on the OLTP server.
12. For the purposes of the demo, you can modify the merge agent and distribution agent profiles to speed up data transmission. First, on the OLTP server, create a new profile called Speed for the distribution agent with the same values as the Default profile. Change the default value of the PollingInterval property to 2. Assign the Pubs_Test1 publication the new Speed profile, then stop and start the distribution agent on the OLTP server. Second, repeat these steps for the distribution agent on the bridge server.

    Third, on the bridge server, create a new profile called Speed for the merge agent with the same values as the Default profile. Change the default value of the PollingInterval property to 2. Figure 3 shows the windows that you need to open to configure the profile for the merge agent.Assign the Pubs_Merge_Test1 publication the new Speed profile, then stop and start the merge agent on the bridge server.

13. Test data replication among all three servers.

Prev. page     1 [2] 3     next page