Merge Replication Setup

Merge replication is my favorite type of replication because of its robustness and its ease of configuration. Merge replication is the only type of replication that works with SQL Server CE Edition. Imagine the opportunities that creates for automation. You can use handheld devices running SQL Server CE Edition (which is less than a megabyte in size in most cases) to synchronize inventory data as a salesperson on the floor takes an order. You could also have employees who take surveys of customers synchronize the data with the home office on a daily basis.

Note 

In merge replication, you cannot fully publish articles that have columns with a timestamp data type in them to a SQL Server 7.0 database. This restriction was not lifted until SQL Server 2000. Tables that have timestamp values in them will have a clock icon next to them.

Configuring merge replication is very similar to configuring snapshot and transactional replication. The first screens are the same, but you select the Merge Replication option back on the Publication Type screen. Therefore, I'll begin this discussion at the Article Properties screen. The General tab has two new options:

• Treat Changes to the Same Row as a Conflict Any time SQL Server detects that the same row has been edited by two different sources, a conflict is raised and handled by the conflict resolver.

• Treat Changes to the Same Column as a Conflict This is the default option for this merge replication. I prefer to keep this item selected most of the time. With this option selected, you can have two people updating different columns in the same row but not raise a conflict. A conflict is only raised and handled when two people edit the same column of the same row. This is useful when you have various data entry representatives gathering different data on the same customer.

The Resolver tab (shown in Figure 12-12) holds information about the conflict resolver. The default resolver assigns a priority to each subscriber of 0.0, which is called a local subscription. Later, in the Push Subscription Wizard, you can assign other priorities to each subscriber (up to 99.99). The higher priority wins.

Figure 12-12: Resolver tab in merge replication

By default, if you don't change the priority, the first server to synchronize to the publisher wins and all others are rejected. If a change is made to the publisher, the publisher always wins with this resolver.

However, you can create a custom resolver to change this behavior. You can also select one of the other conflict resolvers. Here are a few of the common selections (there are eight preinstalled resolvers):

• Microsoft SQL Server DATETIME (Earlier Wins) Conflict Resolver With this resolver, you must provide the column that holds the DATETIME value that the resolver will use to fix conflicts. This will make whichever column is earlier the winner of the conflict. No adjustments are made for time zones, though.

• Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver With this resolver, you must provide the column that holds the DATETIME value that the resolver will use to fix conflicts. This will make whichever column is later the winner of the conflict. No adjustments are made for time zones.

• Microsoft SQL Server Maximum Conflict Resolver With this type of conflict resolution, you must provide the name of the numeric integer (such as int or smallint). This number is used to determine the winner based on whichever is larger.

• Microsoft SQL Server Minimum Conflict Resolver With this type of conflict resolution, you must provide the name of the numeric integer (such as int or smallint). This number is used to determine the winner based on whichever is smaller.

  Tip 

  You can list all the custom resolvers that are installed on your system by using the sp_enumcustomresolvers stored procedure with no parameters.

You can also develop your own custom conflict resolvers to build your own custom business logic into replication. To get a head start, uncompress the unzip_sqlrepl.exe file in the C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqlrepl folder. If you uncompress it into the same directory, some sample code will be uncompressed into the C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\ Samples\sqlrepl\resolver\subspres directory. The sample code is written in C++ and can be modified to suit your needs. Resolvers can also be written in other COM- compatible languages such as Visual Basic or Visual Basic.NET.

As you go through the wizard, you are warned about merge replication's need for uniqueidentifier columns in each table. This is done automatically for you when the publication is created.

The rest of the wizard is almost the same as when creating a transactional publication, with the exception of the filters. After you set the way you'd like to filter, you are asked if you'd like to filter the data through dynamic or static filters. Static filters apply the same logic to each subscriber, while dynamic filters allow you to create logic for each subscriber. Only data that the subscriber needs is sent during synchronization.

Tip 

When setting up your filters, use static filters whenever possible. They require less overhead than dynamic filters.