Merge replication, like transactional replication, involves a Publisher, a Distributor and one or more Subscribers. Each Publisher can have several publications defined on it. You can even have merge publications and transactional publications (and also snapshot publications) defined on the same publisher at the same time. As with transactional replication, each publication contains a list of articles that all need to reside in a single database. Different publications can however be defined on different databases. You can also have the same article be part of more than one publication. All publications on a publisher, independent of their type, have to use the same distributor.

The above paragraph is very similar to the one at the beginning of Level 3 which introduces the publisher for transactional replication. Both are built on the same publisher – subscriber model and move data from the publisher to the subscriber. The similarities however end there.

Merge replication was designed to allow client systems (subscribers) to work in a state disconnected from the main server system (publisher). On every reconnect all changes that happened on the publisher are copied to the subscriber, and vice versa. That allows for real synchronization and therefore uninhibited work while disconnected.

Transactional replication makes use of the SQL Server transaction log to replicate the data changes out to the subscribers. In contrast, merge replication looks at the data in the published tables during each synchronization. That helps to reduce the storage requirements on the each server while being disconnected from the rest of the replication setup. But it also introduces some storage overhead and inflexibility. The details of how this works are topic of Level 8 (Merge-Replication – How it works).

Setting up a publication

The rest of this Level will show how to set up a merge publication. It assumes that you followed the steps in Level 2 to setup a distributor and link your publisher to that distributor.

Who can create a publication?

After a database has been enabled for replication by a member of the sysadmin fixed server role, any member of the db_owner role of that database can define a publication on it.

To enable a database for merge replication, connect to the SQL server instance in SQL Server Management Studio, right click on the Replication folder and select "Publisher Properties…" This is shown in Figure 1.

Figure 1: Publisher Properties

In the Properties dialog that opens, select the "Publication Databases" page and check the "Merge" column next to your database, as shown in Figure 2.

Figure 2: Enable a database for merge replication

A New Publication

After you have enabled you database for merge replication, you can create a publication by going through the following steps. Start by expanding the Replication folder of the instance that holds the database you want to publish. Right click on the "Local Publications" sub folder and select "New Publication…" This step you can see in Figure 3.

Figure 3: Selecting "New Publication"

The "New Publication Wizard" opens and greets you with a welcome page. You can ignore that page and click the "Next" button. If you like to, you can check the box before pressing "Next" to disable the welcome page for all further visits to this wizard. The first page that is of interest to you is the second page, the "Publication Database" page. It is shown in Figure 4.

Figure 4: Choosing the publication database

Select the database that you want to define your publication in and move on to the next page. The next page allows you to pick the "Publication Type" and it is shown in Figure 5.

Figure 5: Selecting Merge Publication

As this Level is about merge replication, go ahead and select just that entry before moving on to the next page. Up to this point the steps have been very similar to what they were for transactional replication. The following page however has no equivalent when setting up transactional replication. It allows you to choose the "Subscriber Types" that can subscribe to this publication, and it is shown in Figure 6.

Figure 6: Subscriber compatibility

Here you can select the different SQL Server versions that this publication needs to be compatible with. You can select one or more versions here. Any selection other than "SQL Server 2008" will cause the publication to run with reduced functionality. (SQL Server 2008R2 did not introduce a new level.) For details about these restrictions, check out Books Online.

If your publication database already contains a merge publication, the "Subscriber Types" page has a significantly reduced number of options. With a few exceptions, all merge publications in the same database need to use the same subscriber types. Figure 7 shows an example of this page, when another merge publication already exists.

Figure 7: Subscriber Types for 2nd Merge Publication

Articles

After you have decided on the subscriber types, the next page allows you to pick the database objects that you want to be part of this publication. As in transactional replication, in merge replication every object that is part of a publication is called an article of that publication.

Figure 8: Articles

Figure 8 shows the "Articles" page of the wizard. You can select tables, functions, views and indexed views, procedures and synonyms to be published. In merge replication most of these are "schema only" articles. That means that the definition of that database object will be replicated to the subscriber but nothing else. Transactional replication allowed for example for procedure executions to be replicated. This is not possible with merge replication. The only article type that allows for more than just the definition to be replicated is the table.

The main reason for this restriction is the way merge replication works. Transactional replication reads the transaction log and therefore has access to a detailed list of every action that happened in that database in chronological order.

Merge replication instead relies on a series of triggers and is therefore restricted to capture only data (or schema) changes. While this method has limitations, some of which manifest in the list of available article types, it is also more flexible. For example you can merge replicate tables that do not contain a primary key, something that is impossible with transactional replication. More details about the inner workings of merge replication will be revealed in Level 8 of this Stairway.

When you select a table as an article, an additional checkbox appears under the "Article Properties" button. It allows you to select that the "Highlighted table is download-only". Download-only means that changes to the data in this table are not allowed to be made on the subscriber.

You should select this option for any table that contains data that will not be changed on the subscriber(s). This selection causes significantly less data to be collected on the publisher, as there is no need for data that helps with the resolving of conflicts (A conflict occurs when a row is changed in more than one place at a time – see Level 8 for details. If the publisher is the only place where changes to the data can be made, conflicts cannot happen). This can have a dramatic impact on performance and space requirements on the publisher.

This checkbox is an alternative way to set one of the options in the "Article Properties". That option controls bidirectionality of a table article. It is called Synchronization direction and it is shown in Figure 9. You can get there by pressing the "Article Properties" button in the form that is shown in Figure 8.

For the "Synchronization direction" you can select between the following three settings:

• Bidirectional
• Download only to Subscriber, prohibit Subscriber changes
• Download only to Subscriber, allow Subscriber changes

The "Highlighted table is download-only" checkbox (Figure 8) allows you to pick between the first two options. If you manually select the third option, changes on the subscriber are allowed, but will not be transferred back to the publisher and might be overwritten.

Figure 9: Synchronization direction

The fact that there is a single checkbox alternative for this option shows the importance of selecting the appropriate setting for your articles. When you click the "Article properties" button you can select if you want to edit the properties for the one article that is highlighted, or if you want to affect all articles that are of the same object type as the highlighted article. For a complete list of the options available for each object type check out Books Online.

The "Article properties" dialog has a second tab that is shown in Figure 10. It allows you to specify how conflicts should be handled for each article by selecting a resolver. Resolvers will be introduced in Level 8 of this Stairway.

Figure 10: Resolver

As with transactional replication, you can select for each table that it be replicated in its entirety or you can individually select the columns to be included. To select individual columns expand the column list by clicking the plus sign next to the table. Figure 11 shows an example.

Figure 11: Select columns to be included

The next screen is a warning. While there a several possible warnings that can be displayed here, if you are setting up a merge publication you will most likely get to see the one shown in Figure 12.

Figure 12: Uniqueidentifier warning

The warning is telling you that merge replication requires a uniqueidentifier column with the ROWGUIDCOL property and a unique index to be part of every replicated table. If such a column does not exist in the table, it will be automatically added. That change can potentially break existing code accessing those tables. It also will increase the size of the table by at least 16 bytes per row.

Warning: If you ever drop the merge publication, every uniqueidentifier column that was automatically added to its articles will be removed automatically as well. That will break any code that was written after replication was established and that relies on that column being there. For that reason it is a good practice to manually add those columns before you set up merge replication. That way you have full control over when they get dropped.

The next page of the "New Publication Wizard" is shown in Figure 13. Here you can add row level filters to your table articles. It will only be shown if you added at least one table article to this publication.

Figure 13: Filter Table Rows

A row filter or "Horizontal Filter" looks like a select statement. An example is shown in Figure 14.

Figure 14: Horizontal Filter

Snapshot

The following two wizard pages, shown in Figure 15 and Figure 16, deal with the snapshot agent and are almost identical to their counter parts on transactional replication. There are two differences that stand out:

1. For merge replication the wizard suggests that you create a snapshot immediately and that you create a new one every 14 days. For transactional replication both checkboxes are unchecked. As merge replication is primarily meant for clients that are often disconnected from the publisher, the likelihood of a resynchronization being necessary is a lot higher than for "always on" transactional subscribers. To have a fresh snapshot available when needed, it is a good practice to regularly generate a new one. That avoids having to run the snapshot agent during high traffic times if the need for a snapshot arises at such a time. See the "Snapshot Agent" section in Level 8 of this Stairway for more background information on this recommendation.
2. Merge replication does not use a distribution agent. Therefore you will only find the snapshot agent on the "Agent Security" page.

The credentials you provide for the snapshot agent need to have the same access rights that were explained in Level 3 of this Stairway when introducing the snapshot agent for transactional replication. The account(s) provided need to be a member of the db_owner fixed database role in the publication database and respectively in the distribution database. The account that the agent executes under also needs to have write access to the snapshot share.

Figure 15: Snapshot Agent

Figure 16: Agent Security

Your Publication Name

The last few pages of the wizard are identical to the ones in transactional replication. They are shown in Figure 17, Figure 18 and Figure 19. Here you can select if you want the wizard to execute the setup or if you would rather script out all the action. You can (and have to) select a name for the publication.

Finally you get to watch the progress of either executing or scripting the necessary changes.

Figure 17: Wizard Actions

Figure 18: Reviewing the options and choosing a publication name

Figure 19: Creating the Publication

Potential Problems

As with transactional replication, there are a lot of things that could go wrong when setting up merge replication. The most common problem for merge replication (as well as for transactional replication) is that one of the accounts does not have appropriate rights. If you run into problems make sure all accounts used have the appropriate rights granted. For details on how to troubleshoot problems check out Level 10 of this Stairway. Also review the section "Potential Problems" in Level 3 of this Stairway (Transactional Replication – The Publisher), as most of the suggestions there are helpful for merge replication as well.

Summary

This Stairway level introduced the publisher in a merge replication setup. It showed how to create a publication and how to add articles to it. It listed the different types of articles and discussed their synchronization options. It also pointed out the differences to the setup of a publisher in transactional replication.

As it is the case with transactional replication, after doing all this work to setup a merge publisher, there is really not a lot to show for it. To be able to see something happening you have to at least have one subscriber as well. The next level of the Stairway (Level 7) will describe how to setup a merge replication subscriber.

This article is part of the Stairway to SQL Server Replication Stairway