Ranga Babu

SQL Server Replication (Merge) – What gets replicated and what doesn’t

January 24, 2019 by

This article will show how triggers, indexes, constraints and foreign keys will replicate in snapshot and after snapshot in SQL Server replication.

Merge replication is used to replicate data from a publisher to a subscriber database and vice versa. Recently someone at our workplace came to us saying that SQL Server replication was not working in DEV environment and when we check replication monitor merge agents were running without issues and everything was working as expected. We asked him what was the issue and he said he created a new trigger and it did not replicate to the subscribers. He was under the assumption that adding a table to merge replication will simply replicate all the objects that were created on top of the table.

Triggers, Indexes, constraints and foreign keys created on a table show different behavior while applying snapshot and after applying snapshot.

Let us create sample tables, indexes, triggers, constraints and foreign keys before adding it to replication and create them after generating snapshot as well.

We’ll use the following databases.

  • Publisher DB: test_pub
  • Subscriber DB: test_sub

We’ll use Merge replication as the type of SQL Server replication.

Before Snapshot

In this example both subscriber database and publisher database are on the same server. To illustrate this, I created a sample table, triggers, non-clustered indexes and foreign keys on top of the tables in publisher database before adding the table to merge replication.

Below is the code to create one trigger, default Constraint, check constraint and non-clustered index on table PARENT1.

Now let us create SQL Server merge replication and add these tables to the merge publication.

Login to server using SQL Server Management studio -> Navigate to the SQL Server replication folder -> click on Local publications -> right click -> click on New Publication. Please refer to the below image.

Click on Next -> select the publisher database. Click Next and select Merge replication.

Click Next and check 2008 or later -> Add the tables you created earlier. Please refer to the below image.

After adding the tables to replication, click on Article properties -> set properties of all table articles -> make sure you set below properties to true.

Click Next -> Next -> add filter if you want to replicate specific data to the subscribers or you can skip this step and click Next -> check create snapshot immediately and uncheck schedule the snapshot agent to run at following times.

Click Next and add the credentials you want to use for snapshot agent and connecting publisher -> Next -> Next -> and input publication name and finish.

Right click on publication you just created, launch SQL Server replication monitor and make sure the snapshot is completed.

Navigate to the publication you just created, right click and click on New subscription -> Next -> Next -> select the type of subscription you want (Pull or push) as per your need -> Next -> add the subscriber server and select the subscriber database. Add the logins to run merge agent, connect the publisher, distributor and the subscriber -> Next -> select agent schedule as per your need. In my case I selected Run continuously -> Initialize immediately -> select subscription type as per your need -> check create subscriber and click Next -> Finish.

Once the snapshot is applied go to the subscriber and check the objects we created on PARENT1 table.

We can see that default constraint, check constraint, non-clustered index and trigger got replicated to the subscriber as we set article properties true for above object types.

 

After snapshot

Now the initial snapshot is applied on the subscriber and the data is being replicated bidirectionally.

Let us see the behavior of these objects by creating new non- clustered index, default constraint, check constraint, trigger and foreign key and check if they were replicated to the subscriber or not.

Triggers:

At publisher server, on the publisher database let us create new trigger on table PARENT1.

Wait for replication to sync. Now go to the subscriber and check if the new trigger is created at the subscriber.

The newly created trigger PARENT1_TRIG2 on table PARENT1 did not replicate.

At publisher server, on the publisher database let us modify the existing trigger PARENT1_TRIG which is on both publisher and subscriber.

Now wait for SQL Server replication sync and we can see “ALTER TRIGGER” got replicated to the subscriber.

At the publisher server, on the publisher database let us drop the existing trigger PARENT1_TRIG on table “PARENT1”

Wait for replication sync and on the subscriber we can see “DROP TRIGGER” did not replicate to the subscriber.

Results

  • Triggers which were created before snapshot got replicated to the subscribers while applying initial snapshot (Can turn off this in SQL Server replication settings)
  • Triggers which were created after snapshot are not replicated and if you want them on the subscriber, please create them manually on the subscriber
  • Alter trigger will replicate if the trigger exists on the publisher and the subscriber
  • Alter trigger does not replicate if the trigger is only on the publisher
  • Drop trigger does not replicate. If you want to drop them on the subscriber, do it manually

Default and Check Constraints:

At publisher server, on the publisher database let us create new default and check constraints on table PARENT2. Please refer to the below code.

Wait for SQL Server replication sync. Now go to the subscriber and check for the newly created constraints. We can see the new constraints got replicated to the subscribers.

Now let us drop the existing constraints on table PARENT1 at publisher.

Results

  • The newly created default constraints are replicated to the subscribers
  • The newly created check constraints are replicated to the subscribers
  • Drop constraint replicated to the subscribers
  • Check and default constraints which were created before snapshot got replicated while applying initial snapshot (We can turn off this in article properties)

Non-Clustered index:

At the publisher, on the publisher database let us create a new index on table PARENT1. Please refer to below T-SQL script to create index and disable index.

After SQL Server replication sync go to the subscriber and check for newly created index. The new index which we created did not replicated.

Let us disable the index on table PARENT1 at publisher database.

Disable index did not replicate to the subscriber. We can check that from below query.

On the publisher database let us drop the existing index on table PARENT1.

Results

  • Newly created index does not replicate to the subscriber
  • Disable index does not replicate to the subscriber
  • Drop index does not replicate to the subscriber
  • Existing indexes before snapshot got replicated while applying the initial snapshot on the subscriber. (We can turn off this in article properties)

Foreign key Constraints:

At the publisher server on the publisher database let us create a new foreign key on table CHILD1 which referrers to table PARENT2.

Wait for SQL Server replication sync. New foreign key got replicated to the subscriber.

Let us drop the foreign key constraint on CHILD1 at publisher.

Results

  • Newly created foreign keys are replicated to the subscriber
  • Drop foreign key got replicated to the subscriber
  • Foreign keys created before snapshot got replicated to the subscriber when initial snapshot is applied. (We can turn off this in article properties)

We have seen the behavior of triggers, foreign keys, indexes, default constraints and check constraints while applying snapshot and after applying snapshot as well during SQL Server replication.

Based on the above results we must make deployment/upgrade scripts on replicated tables as SQL Server replication compatible.

For example, if we are creating a new trigger on a table which is being replicated already and want it to be on both publisher and subscriber, we must create them on both the publisher and the subscriber. If we are creating a new constraint we can just create it on the publisher.

We can also control the replication of these objects in snapshot by setting article properties while configuring the publication.

Table of contents

SQL Server Replication with a table with more than 246 columns
Foreign key issues while applying a snapshot in SQL Server merge replication
SQL Server Replication (Merge) – What gets replicated and what doesn’t
SQL Server Replication (Merge) – Performance Issues in replicating schema changes
Merge SQL Server replication parameterized row filter issues

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
Replication

About Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies

4,188 Views