How merge replication initializes publications and subscriptions

Merge replication must initialize both the Publisher and Subscriber before data can flow between them. This article provides information on the steps that occur during initialization.

Initialize the publication

The following list details the initialization steps for a publication, which occur as you execute each stored procedure listed or after you complete the New Publication Wizard. Further initialization occurs after the Snapshot Agent runs for the first time for a publication.

• sp_replicationdboption

  • The publication database is marked for replication. The database can't be dropped unless replication is removed.

  • System tables are added to the publication database (unless a merge publication already exists in the database). For a complete list of system tables, see the section "System Tables Created in the Publication and Subscription Databases" in this article.

• sp_addmergepublication

  • Entries for the publication are added to the system tables.

• sp_addpublication_snapshot

  • A Snapshot Agent job is added to the SQL Server Agent system. The job name is in the form <Publisher>-<PublicationDatabase>-<Publication>-<Integer>.

• sp_addmergearticle

  • Each replicated object is marked for replication. The object can't be deleted unless the corresponding article is dropped from all publications.

  • Entries for each article are added to the system tables.

The remainder of the initialization for the publication database occurs during the initial run of the Snapshot Agent for a publication. The publication database isn't reinitialized during subsequent runs of the Snapshot Agent. If you use the New Publication Wizard, the initial snapshot is created by default after you complete the wizard. If you use stored procedures, you must run the agent job or run the agent directly. For more information about running agents, see Start and Stop a Replication Agent (SQL Server Management Studio) and Replication Agent Executables Concepts.

The first time the Snapshot Agent for a publication runs:

• A column named rowguid is added to each published table, unless the table already has a column of data type uniqueidentifier with the ROWGUIDCOL property set (in which case this column is used). The rowguid column is used to uniquely identify every row in every published table. If the table is dropped from the publication, the rowguid column is removed; if an existing column was used for tracking, the column isn't removed.

• The following objects are created in the publication database for each published table (all objects are created in the dbo schema):

  • Insert triggers, update triggers, and delete triggers are added to published tables to track changes. The triggers are named in the form MSmerge_ins_<GUID>, MSmerge_upd_<GUID>, and MSmerge_del_<GUID>. The GUID value is derived from the entry for the article in the system table sysmergearticles.

  • Stored procedures are created to handle inserts, updates, and deletes to published tables, and to perform several other replication-related operations.

  • Views are created to manage inserts, updates, deletes, and filtering.

  • Conflict tables are created to store conflict information. The conflict tables match the schema of the published tables: each published table is scripted, and then the script is used to create the conflict table in the publication database. Conflict tables are named in the form dbo.MSmerge_conflict_<Publication>_<Article>.

Every time the Snapshot Agent runs, the following types of files (with their corresponding file extensions) are created for each article in the publication database:

• Schema (.sch)

• Constraints and indexes (.dri)

• Triggers (.trg)

• System table data (.sys)

• Conflict tables (.cft)

• Data (.bcp): not created for publications with parameterized filters.

  If the publication doesn't use any parameterized filters, the snapshot contains the data for the published tables in a set of .bcp files. If the publication uses parameterized filters (which is typical for merge publications), the initial snapshot doesn't contain any data. Data is provided using a snapshot for a Subscriber's partition, which is discussed in the the next section.

Initialize a subscription

Each subscription is initialized when the Merge Agent for the subscription runs and copies the initial snapshot to the subscription database. In addition to the schema and data from replicated objects, the snapshot contains the system tables, views, triggers, and stored procedures that exist in the publication database. One or two extra system tables are also copied to the subscription database. For a complete list of system tables, see the section System tables created in the publication and subscription databases in this article. If a subscription is reinitialized, all replicated objects and replication system objects are overwritten.

If none of the tables in the publication database use parameterized filters, the same publication snapshot is copied to each Subscriber. If one or more parameterized filters are used, the way in which each subscription is initialized is governed by the following logic:

• If the snapshot location is provided to the Merge Agent on the command line:

  • Apply the snapshot from this location.

• Else if the snapshot was pregenerated:

  • Retrieve the location of the snapshot from MSmerge_dynamic_snapshots in the publication database and apply the snapshot from that location.

• Else if the publication allows Subscribers to initiate snapshots:

  • If a snapshot has already been generated for another Subscriber with the same partition, apply that snapshot to the Subscriber.

  • Else generate and apply a snapshot to the Subscriber.

• Else initialize the Subscriber using SELECT statements against the tables in the publication. This approach is slower than using a snapshot for the Subscriber's partition.

If the snapshot transfer is interrupted at any point, it automatically resumes, and doesn't resend any files that have already been completely transferred. The unit of delivery for the Snapshot Agent is the bcp file for each publication article, so files that are partially delivered must be completely redelivered. However, resuming the snapshot can significantly reduce the amount of data transmitted and ensure timely snapshot delivery even if the connection is unreliable. For more information about creating snapshots, see Parameterized Filters - Parameterized Row Filters.

Snapshot location

The snapshot location depends on: the path specified for the default or alternate snapshot location; whether the publication uses a UNC path or FTP share for the snapshot folder; and whether the publication uses parameterized filters. In these examples, assume that the snapshot folder location is: \\<MyComputer>\<MyFolder>:

• If the publication uses UNC, the first part of the path is: \\<MyComputer>\<MyFolder>\unc\. If it uses FTP, it's \\<MyComputer>\<MyFolder>\ftp\.

• If the publication uses UNC and doesn't use parameterized filters, the path is\\<MyComputer>\<MyFolder>\unc\<Publisher><Publicationdb><publication>

• If the publication uses UNC and uses parameterized filters, the location is based on the snapshot folder path and the parameterized row filtering criteria for the publication. For example, if the article is filtered using the HOST_NAME() function and the value of HOST_NAME() for the partition is SalesLaptop, the path to the snapshot for that partition is \\<MyComputer>\<MyFolder>\unc\<Publisher><Publicationdb><publication>\SalesLaptop_12, where 12 is the ID used internally for the partition.

System tables created in the publication and subscription databases

The following tables are created in the publication database and each subscription database.

In addition, the MSsnapshotdeliveryprogress table is created in each subscription database, and the MSsubscription_properties table is created in each subscription database that uses a pull subscription:

Related content

• Merge replication
• sp_addmergearticle (Transact-SQL)
• sp_addmergepublication (Transact-SQL)
• sp_addpublication_snapshot (Transact-SQL)
• sp_replicationdboption (Transact-SQL)