Sixth article Replication: Merge Replication-Publishing

This article is the sixth of the SQL Server replication series, please refer to the original text for more information.

Merge replication, similar to transactional replication, includes one publisher, one distributor, and one or more subscribers. Multiple publications can be defined at each publisher. You can also define merge publications, transactional publications, and snapshot publications at the same publisher at the same time. With transactional replication, each publication contains items that need to be objects in a single database. Different publications can be defined on different databases. A project can be used by multiple publications. All publications on a publisher, independent of their type, has the to use the same distributor.
The above section is similar to the publisher in the transactional replication that begins with the third chapter. Both are publish-subscribe patterns and move data from the Publisher to the Subscriber. The same is where it ends.
Merge replication allows the client (subscriber) to disconnect from the primary server (publisher). Every time you reconnect, all changes at the publisher are copied to the subscriber, and vice versa. Allows true synchronization, and does not limit work when disconnected.
Transactional replication uses the SQL Server transaction log to transfer data changes to subscribers. In contrast, merge replication views the data of published tables during each synchronization. That helps to reduce the storage requirements on the all server while being disconnected from the rest of the replication Setup. But it also introduces some storage overhead and inflexibility. The eighth chapter will describe in detail how it works.
Configure Publishing
The remainder of this article describes how to configure a merge publication. Suppose you have followed the second article to configure the Distributor and have connected the publisher to the Distributor.
who can create a publication
As long as members of the sysadmin fixed server role have databases enabled for replication, members of any db_owner fixed database roles in the database can define a publication. Enable the database for merge replication, connect to the Publisher under Object Explorer, right-click Replication, select Publisher Properties, 6.1

Figure 6.1 Publishing Server Properties
In the Publisher Properties dialog box that opens, select the Publish Database page, and then tick the Merge column copy box for the corresponding database, as shown in 6.2

Figure 6.2 Enabling the publication database for merge replication
New Publication
After you enable the database for merge replication, you can create a publication by following the steps below. First expand the Replication folder under the Publisher instance (the instance on which you plan to publish the database), right-click the local Publications subfolder, and select New Publication ..., as shown in 6.3

Figure 6.3 New Publication
The New Publication Wizard is opened, and the first thing to choose is the Publish Database page (Figure 6.4)

Figure 6.4 Selecting a publication database
Select the database you want to publish, and then go to the next page. The next page allows you to choose a publishing type, as shown in 6.5

Figure 6.5 Selecting a merge publication
This article is about merge replication, so choose merge publications here. Up to here, these steps are very similar to the steps for transactional replication. However, the following page is not in the configuration transaction replication. It allows you to select "Subscriber Type", as shown in 6.6

Figure 6.6 Subscriber Type
Here you can specify the version of SQL Server that subscribers will use for the publication. You can select one or more versions. In addition to the "SQL Server2008" alternative, there are functional limitations. For more information, please refer to books Online.
If your publication database already contains a merge publication, the options for the Subscriber Type page are significantly reduced. All merge publications under the same database need to use the same subscriber type. Figure 6.7 shows an example of the existence of another merge publication

Figure 6.7 Subscriber type when a merge publication already exists
Project
After the subscriber type is determined, the next step is to select the published database object. Each published object in transactional replication and merge replication is called a project.

Figure 6.8 Project
Figure 6.8 shows the project page of the wizard. You can choose tables, functions, views, indexed views, stored procedures, and synonyms to publish. In the merge replication most of these is "schema only" articles. This means that the definition of the database object is copied to the Subscriber (not the other). For example, transactional replication allows replication of the execution of stored procedures, but this is not possible in merge replication. The only project type that allows replication other than the definition is a table.
This limitation is due to the working mechanism of merge replication. Transactional replication reads the transaction log so that it can access a detailed list of each action that occurs sequentially in the database.
Merge replication relies on a series of triggers and is therefore restricted to capturing only data (schema) changes. Although this approach has limitations, some of the available project types become more flexible. For example, you can merge duplicate tables without a primary key, which is not possible in transactional replication. More on the internal working mechanism of merge replication is described in the eighth article.
When you select a table as an item, an additional check box appears under the Project Properties button. It allows you to select "Highlighted tables for download only". Download only means that the data in the table cannot be changed at the Subscriber.
You should choose this option for tables that contain data but cannot make changes at the Subscriber. This option causes a reduction in data collection at the publisher because there is no longer a need to resolve conflict issues. This has a huge impact on the performance and space requirements on the publisher.
This check box is an alternative to one of the properties in Project properties. This option controls the bidirectional nature of the table item. It is called the synchronization direction (Figure 6.9). You can open it by clicking on the project properties in Figure 6.8.
For the sync direction, you can choose from the following three options:
Two-way
Download to subscribers only, prohibit subscriber changes
Download to subscribers only, allow subscriber changes
The "highlighted tables for download only" check box allows you to select among the top two options. If you manually selected the third item--allows the subscriber to change, but does not pass back to the publisher and may be overridden.

Figure 6.9 Synchronization Direction
The fact that there was a single checkbox alternative for this option shows the importance of selecting the appropriate set Ting for your articles. When you click the Project Properties button you can choose to set options for the highlighted items, or you can set options for all items of the same type as the highlighted item. The list of options available for each object type reference books Online.
The second tab of the Project Properties dialog box is shown in tab 6.10. It allows specify how conflicts should is handled for each article by selecting a resolver.

Figure 6.10 Conflict Resolution Program
Like transactional replication, you can choose to publish an entire table, or to publish some columns. To select individual columns, expand the list of columns by clicking the + sign in front of the table name.

Figure 6.11 Filter Columns
The next page is a warning. Several warnings may be displayed here, and you will most likely see the warning shown in 6.12 When you configure merge replication

Figure 6.12 Project Issues
This warning indicates that all merge items must contain the Unipueidentifier column and the ROWGUIDCOL property with a unique index. If there are no Unipueidentifier columns in the table, SQL Server automatically adds the column. This change may disrupt the existing code that accesses this table. It also increases the size of the table (at least 16bytes per row).
Warning:If you delete merge replication, the Unipueidentifier columns that are automatically added in the project are also automatically removed. This will disrupt the code that is written by the Unipueidentifier column after replication is created. For these reasons, it is a good idea to manually add the Unipueidentifier column before you configure merge replication. So that when they delete you can have complete control.
Shown in the next page 6.13. Here you can add a row filter for the table item. This page is displayed only if a table item is added to the publication.

Figure 6.13 Filtering Table rows
A row filter or a horizontal filter is similar to a query statement, as shown in 6.14

Figure 6.14 Horizontal filtering
Snapshot
Next two pages (Figure 6.15, figure 6.16), deal with the Snapshot Agent and is almost identical to their counter parts on transactional replica tion. There is differences that stand out:
->1, for merge replication, the wizard recommends that you create a snapshot immediately and create a new snapshot every 14 days. The two check boxes for transactional replication are not checked by default. Merge replication is often disconnected from the publisher primarily for clients (subscribers) and is much more likely to resynchronize than transactional subscriptions. In order to have a newer snapshot available when needed, it is necessary to have it generate a new snapshot on a regular basis. This avoids running the Snapshot Agent at peak times. See the "Snapshot Agent" section of article eighth for more background information.
->2, merge replication does not require a Distribution agent. So you can only see the Snapshot Agent on the Agent Security page.
The credentials that you provide to the Snapshot agent need to have the same access rights as the Snapshot Agent account in the third transactional replication.These accounts need to be members of the db_owner fixed database role in the publication database and the distribution database. The account that the agent executes also requires write access to the snapshot folder/share.

Figure 6.15 Snapshot Agent

Figure 6.16 Agent Security
complete the Release configuration
The remaining pages of the wizard and transactional replication are the same. 6.17, figure 6.18, figure 6.19 shows. Here you can choose to have the wizard perform the configuration or generate a script file. You need to set a name for the publication.
Finally you get to watch the progress of either executing or scripting the necessary changes.

Figure 6.17 Wizard Actions

Figure 6.18 Setting the publication name

Figure 6.19 Creating a publication
Potential Problems
Similar to transactional replication, various issues may occur when you configure merge replication. The most common problem with merge replication is that the account does not have the appropriate permissions. If you encounter such a problem, you should check that all accounts have been granted the correct permissions. For more information on troubleshooting, please see the tenth article. You can also refer to the "potential problems" mentioned in the third article, and most of the recommendations apply to merge replication as well.
Summary
This article describes configuring a merge publication. Shows how to create a publication and add items. Lists the different types of projects and discusses their synchronization options. It also points out the difference from configuring a transactional publication.
Like transactional replication, you don't see a lot of things after you've configured a merge publication. To see what's going on, you need at least one subscription. Next we will discuss how to configure a merge subscription.

Sixth article Replication: Merge Replication-Publishing