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.
|
1 2 3 4 5 6 |
CREATE TABLE PARENT1 ( ID INT CONSTRAINT PK_PARENT1 PRIMARY KEY ) CREATE TABLE PARENT2 ( ID INT CONSTRAINT PK_PARENT2 PRIMARY KEY ) CREATE TABLE CHILD1 ( ID1 INT, ID2 INT , CONSTRAINT FK1_CHILD1 FOREIGN KEY ( ID1 ) REFERENCES PARENT1( ID ) ) CREATE TABLE KEYCHECK (ID INT) |
Below is the code to create one trigger, default Constraint, check constraint and non-clustered index on table PARENT1.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE NONCLUSTERED INDEX [NonClusteredIndex1] ON [dbo].[PARENT1] ( [ID] ASC ) GO CREATE TRIGGER PARENT1_TRIG ON [dbo].[PARENT1] FOR INSERT NOT FOR REPLICATION AS BEGIN PRINT 'Test' End GO ALTER TABLE PARENT1 ADD CHECK (id<>10); GO ALTER TABLE PARENT1 ADD CONSTRAINT DF_PARENT1 DEFAULT 0 FOR ID; GO |
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.
|
1 2 3 4 5 6 7 |
CREATE TRIGGER PARENT1_TRIG2 ON PARENT1 FOR INSERT NOT FOR REPLICATION AS BEGIN PRINT 'test2' END |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE [Z_TEST1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[PARENT1_TRIG] ON [dbo].[PARENT1] FOR INSERT NOT FOR REPLICATION AS BEGIN PRINT 'ALTER TEST' End |
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”
|
1 2 3 4 |
USE [Z_TEST1] GO DROP TRIGGER PARENT1_TRIG |
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.
|
1 2 3 4 5 6 7 |
ALTER TABLE PARENT2 ADD CHECK (id<>10); GO ALTER TABLE PARENT2 ADD CONSTRAINT DF_PARENT2 DEFAULT 0 FOR ID; GO |
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.
|
1 2 3 4 5 6 7 8 |
USE [Z_TEST1] GO ALTER TABLE [dbo].[PARENT1] DROP CONSTRAINT [DF_PARENT1] GO ALTER TABLE [dbo].[PARENT1] DROP CONSTRAINT [CK__PARENT1__ID__3D9E16F4] GO |
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.
|
1 2 3 |
USE [Z_TEST1] GO CREATE INDEX NonClusteredIndex2 ON PARENT1(ID) |
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.
|
1 2 3 |
USE [Z_TEST1] GO ALTER INDEX NonClusteredIndex1 ON PARENT1 DISABLE; |
Disable index did not replicate to the subscriber. We can check that from below query.
|
1 |
SELECT name,is_disabled FROM sys.indexes where name ='NonClusteredIndex1' |
On the publisher database let us drop the existing index on table PARENT1.
|
1 2 3 4 |
USE [Z_TEST1] GO DROP INDEX [NonClusteredIndex1] ON [dbo].[PARENT1] GO |
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.
|
1 2 3 |
ALTER TABLE CHILD1 ADD CONSTRAINT FK2_CHILD1 FOREIGN KEY (ID2) REFERENCES PARENT2(ID); |
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.
|
1 2 3 4 |
USE [Z_TEST1] GO ALTER TABLE [dbo].[CHILD1] DROP CONSTRAINT [FK1_CHILD1] GO |
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 |
- Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019
- Overview of the Collate SQL command - October 22, 2019
- Recover a lost SA password - September 20, 2019