Configuring the Merge Replication System | Microsoft SQL Server 2000 Administrators Companion

3 4

Configuring merge replication is similar to configuring snapshot and transactional replication. First you must configure the publication, and then you configure that publication to be pushed to the subscriber or to be pulled by the subscriber.

NOTE
Before you configure any type of SQL Server replication, you must first configure publishing and distribution. See the section "Configuring Publishing and Distribution" in Chapter 26 for instructions.

Configuring Publications

Even though the process of configuring publications was described in Chapters 26 and 27, the procedures used to create a merge publication are listed here because they differ slightly from the procedures used to created the other types of publications. To configure a merge publication, follow these steps:

In Enterprise Manager, click the Tools menu. Next either point to Replication and then choose Create And Manage Publications, or choose Wizards, expand the Replication folder in the dialog box that appears, and choose Create Publication Wizard. Performing either procedure will display the Create And Manage Publications dialog box, shown in Figure 28-1. This dialog box allows you to select a database or table containing the data you want to publish.

Figure 28-1. The Create And Manage Publications dialog box.

If publications already exist, the following buttons will be available in addition to the Create Publication button:
- Push New Subscription Enables you to create a new push subscription for an already existing publication. This process is described in the section "Configuring Subscriptions" later in this chapter.
- Properties And Subscriptions Enables you to modify both publication and subscription properties.
- Script Publication Enables you to create a SQL script that can be used to create more publications.
- Delete Publication Enables you to delete an already configured publication.
Select the database you want to use for the publication (in Figure 28-1, Northwind is selected), and then click Create Publication to invoke the Create Publication Wizard. The wizard's welcome screen appears, as shown in Figure 28-2. Select the Show Advanced Options In This Wizard check box.

Figure 28-2. The Create Publication Wizard welcome screen.
Click Next to display the Choose Publication Database screen, shown in Figure 28-3. This screen allows you to (again) select the database containing the data you want to publish. By default, the database that you chose in step 2 is selected.

Figure 28-3. The Choose Publication Database screen.
Click Next to display the Select Publication Type screen, shown in Figure 28-4.

Figure 28-4. The Select Publication Type screen.

This screen allows you to choose one of the three replication types. The options on the Select Publication Type screen are described here:
- Snapshot Publication Creates a snapshot publication that periodically copies a snapshot of the article to the subscriber. A snapshot publication can be created from any table.
- Transactional Publication Creates a transactional replication publication that updates the subscription with changes made to the publisher, based on transactions. Articles can be created only from tables with a primary key.
- Merge Publication Creates a merge replication publication that allows two-way replication between the publisher and subscriber. Articles can be created from any tables.
Click Merge Publication, and click Next to display the Specify Subscriber Types screen, shown in Figure 28-5. This screen lets you specify whether all of the subscribers will be running Microsoft SQL Server. If possible, accept the default setting, which specifies that all subscriber servers are running SQL Server 2000. If you accept this setting, you are configuring replication to use native SQL Server 2000 data types. If Microsoft SQL Server 7 systems are in the replicated configuration, select the second check box. If SQL Server CE devices are being used, check the third box. If you have non-SQL Server systems in the configuration, you should select the fourth check box, which causes replication data to be converted to character format. This conversion of complex native data types causes additional overhead.

Figure 28-5. The Specify Subscriber Types screen.
Click Next to display the Specify Articles screen, shown in Figure 28-6. In this screen, you can specify the tables, stored procedures, and views that will be replicated as articles. These articles will make up the publication that you are creating. Select as many tables, stored procedures, and views as you want in the right-hand box, or select one or more check boxes in the Publish All column to select all items of one or more object types in the database. Remember, each object is considered an article, and a publication is a set of articles that are logically grouped together.

Figure 28-6. The Specify Articles screen.

NOTE
If stored procedures exist on the subscriber, replication can be configured to replicate the calls to the stored procedures rather than the results of the stored-procedure calls.
Because we are specifying merge replication, we can define additional attributes on the articles. To configure these attributes, first click the [...] button to the right of the article definition. You will then see the Table Article Properties window, shown in Figure 28-7.
In the tabbed pages of this window, you can configure various properties of the article. Figure 28-7 shows the General tab. Here you can specify the name of the article and the destination database owner, and you can define what constitutes a conflict. The default option specifies that modifications to a column by two sources will be treated as a conflict. You can broaden this definition by specifying that changes to a row by two sources will be treated as a conflict.

Figure 28-7. The General tab of the Table Article Properties window.
Click the Resolver tab (shown in Figure 28-8) to specify the resolver to use. If you use the default resolver, the publisher always wins a conflict with the subscriber. In addition, the first subscriber to synchronize wins a conflict between subscribers. This is normally the desired behavior. You can instead select one of a number of other resolvers, including custom resolvers that you define yourself.
The Merging Changes tab, shown in Figure 28-9, allows you to specify additional security for certain operations. By selecting one or more check boxes in the Check Permissions area, you specify that Merge Agent permissions to perform the specified operation or operations be checked before the operation or operations are executed. In addition, the tab contains a check box that is selected by default. This check box specifies that multiple column updates to one row be performed in one UPDATE statement operation. You should accept this default setting. Click OK when you are ready to continue.

Figure 28-8. The Resolver tab of the Table Article Properties window.

Figure 28-9. The Merging Changes tab of the Table Article Properties window.
Click Next. At this point, a check of the publication is made, and most likely, you will see a screen such as the one shown in Figure 28-10. Merge replication requires a unique identifier column. This column will automatically be added to the table for you. In addition, identity columns will be created with the NOT FOR REPLICATION option.

Figure 28-10. The Article Issues screen.
Click Next. The Select Publication Name And Description screen appears, as shown in Figure 28-11. In this screen, you specify simply a publication name and a description.

Figure 28-11. The Select Publication Name And Description screen.
Click Next to display the Customize The Properties Of The Publication screen, shown in Figure 28-12. In this screen, you specify whether you want to define data filters and customize other publication properties. Click No. (The options presented if you click Yes were described in the previous chapter.)

Figure 28-12. The Customize The Properties Of The Publication screen.
Click Next. The Completing The Create Publication Wizard screen appears. Click Finish and the publication will be created for you. You will see the progress as the various steps are being performed. Finally you will see a screen that shows you the steps were completed and that provides a summary of the operation.

If you now check the Replication Monitor folder for Merge Agents, you will find that there are none. Because the Merge Agents are used for two-way replication, it is necessary to have subscribers configured before replication begins. Once you configure the subscribers, the Merge Agents will appear in the Replication Monitor folder.

Configuring Subscriptions

As when you configure snapshot and transactional replication, the final step when you configure merge replication is to set up the subscribers. You must first enable subscribers in the distribution database; this process was outlined in the section "Enabling Subscribers" in Chapter 26. Then you configure subscriptions from either the subscriber or the publisher. From the subscriber, you can configure a pull subscription; from the publisher, you can configure a push subscription.

Configuring Pull Subscriptions

Pull subscriptions are controlled by and configured from the subscriber. Thus, you must run Enterprise Manager on the subscriber system to set up a subscription for it. We have already seen in Chapter 27, "Configuring Pull Subscriptions," how to configure a pull subscription. Because in this case steps are almost identical, we will summarize them here briefly and concentrate on differences instead.

Invoke the Pull Subscription Wizard.
The Pull Subscription Wizard welcome screen appears.
Click Next to display the Look for Publications screen. Choose registered servers for this example.
Click Next to display the Choose Publications screen, and select the publication that will be replicated.
Click Next to display the Specify Synchronization Agent Login screen, and specify which account the merge agent will use to go communicate with the Publisher and Distributor.
Click Next to display the Choose Destination Database screen, and choose the database.
Click Next to display the Initialize Subscription screen, and make your selections.
Click Next to display the Snapshot Delivery screen, and specify the snapshot location.
Click Next to display the Set Merge Agent Schedule screen. This screen is similar to the Set Distribution Agent Schedule screen you saw in the previous chapter. Make your selections.
Click Next to display the Set Subscription Priority screen, shown in Figure 28-13. Here you can set the priority of the subscription, which will be used to determine the winner in a conflict. The default setting (recommended) specifies that the priority setting on the publisher will be used for conflict resolution.
Click Next to display the Start Required Services screen, and start SQL Server Agent if it is not already started.
Click Next to display the Completing The Pull Subscription Wizard screen. Review your settings and then click Finish. Once you have completed this wizard, the push subscription is created and will be updated on a regular basis.

click to view at full size.

Figure 28-13. The Set Subscription Priority screen.

Configuring Push Subscriptions

A push subscription is initiated on the publisher. You configure a push subscription by using the Push Subscription Wizard. To run this wizard, follow these steps:

Invoke the Push Subscription Wizard by using either of two methods. To use the first method, in Enterprise Manager, point to Replication on the Tools menu, and then choose Push Subscriptions To Others. The Create And Manage Publications dialog box appears, as shown in Figure 28-14.

Figure 28-14. A publication selected in the Create And Manage Publications dialog box.

Select a publication in the Databases And Publications list box, and then click Push New Subscription. To use the second method, choose Wizards from the Tools menu, expand the Replication folder in the Select Wizard dialog box that appears, choose Create Push Subscription Wizard, select a publication in the Create And Manage Publications dialog box that appears, and then click Push New Subscription.
The Push Subscription Wizard welcome screen appears, as shown in Figure 28-15.

Figure 28-15. The Push Subscription Wizard welcome screen.
Click Next to display the Choose Subscribers screen, shown in Figure 28-16. In this screen, you select the system that will be the recipient of the publication you just selected. Choose from the list of enabled subscribers.
Click Next to display the Choose Destination Database screen, shown in Figure 28-17. In this screen, you specify the database that will accept the publication on the subscriber. You can choose to use a database that already exists, or you can create a database, depending on your system configuration and needs. To see a list of existing databases, click Browse Or Create. If you want to create a database, click Browse Or Create, click Create New, and then create the database in the Database Properties window that appears.

Figure 28-16. The Choose Subscribers screen.

Figure 28-17. The Choose Destination Database screen.
Click Next to display the Set Merge Agent Location screen (Figure 28-18). Here you can select where the Merge Agent will run. You can accept the default setting, which specifies that the Merge Agent will run on the distributor, or you can select to run the agent on the subscriber. You should accept the default setting unless your distributor is extremely busy.

Figure 28-18. The Set Merge Agent Location screen.
Click Next to display the Set Merge Agent Schedule screen, shown in Figure 28-19. Here you can choose to have the subscription continually updated or to have it updated based on a schedule you specify. Click Change to display the Edit Recurring Job Schedule dialog box. Here you can easily configure a recurring schedule. When deciding how often the subscription should be updated, keep in mind that continuous updates use a lot of overhead.

Figure 28-19. The Set Merge Agent Schedule screen.
Click Next to display the Initialize Subscription screen, shown in Figure 28-20. In this screen, you specify whether the subscription needs to be initialized. The option to initialize the schema and data set on the subscriber is selected by default. In this screen, you can also start the Snapshot Agent if it is not already started. It's a good idea to start the Snapshot Agent when you initialize the snapshot; otherwise, you must start the agent by hand. Once the snapshot has been initialized and replication begins, you don't need to use a snapshot until the next time you create a subscription. Each time you create a subscription, create a new snapshot, and don't bother creating snapshots on a regular schedule unless you plan on resynchronizing the subscriber database with the snapshots.

Figure 28-20. The Initialize Subscription screen.
Click Next to display the Set Subscription Priority screen, shown in Figure 28-21. Here you can set the priority of the subscription, which will be used to determine the winner in a conflict. The default setting (recommended) specifies that the priority setting on the publisher will be used for conflict resolution.

Figure 28-21. The Set Subscription Priority screen.
Click Next to display the Start Required Services screen, shown in Figure 28-22, which enables you to start SQL Server Agent if it is not already started.

Figure 28-22. The Start Required Services screen.
Click Next to display the Completing The Push Subscription Wizard screen (Figure 28-23). Review your settings, and then click Finish to begin the copying of the snapshot to the subscriber. Once you have completed this wizard, the push subscription is created and will be updated on a regular basis.

Figure 28-23. The Completing The Push Subscription Wizard screen.