All about “Not for Replication”

Rishi Maini | Microsoft SQL Server Escalation Services

All about “Not for Replication”

“Not for Replication” is a property which can be set for different objects like Check constraints, Foreign Key constraints, Triggers , Identity columns etc while using SQL Server Replication. Feature used when the DBA would like the transactions to behave differently when the changes are being made by the Replication Agents as compared to changes coming from a normal user transaction.

Let’s take a simple example

A company has various sales stores in different parts of the country, each sale store takes an order from the end user and replicates the request to the main production and dispatch unit of the company.

The main production and dispatch unit Server is acting as the Publisher whereas all the sale stores are acting as Subscribers in Merge Replication. Every Sale Store needs to have the information about the current stock level of a product and should not accept any new orders if the product is out of stock at the main production unit.

The tables participating in the Replication topology are as follows, with Products being the parent table and joined with PK-FK constraints with the Orders table on “Product_id” Column. The Product table also has another column named as “Stock_Available” which keeps track of the current available stock for the particular product.

Products:

Orders:

In Addition to this there is also an AFTER INSERT Trigger (named as “Update_Stock”) on the Orders Table which checks if we have sufficient stock available for the product_id, if yes, it updates the Products table by subtracting the Quantity ordered from the Available Stock.

Sample Trigger:

create TRIGGER [dbo].[Update_Stock]

   ON  [dbo].[Orders]

   AFTER  INSERT

AS

BEGIN

declare @quantity as int

declare @product_id as int

declare @stock_available as int

select @product_id= product_id , @quantity = quantity from Inserted

select @stock_available= stock_available from Products where product_id=@product_id

if (@Quantity > @stock_available)

      rollback

else

      update Products set stock_available= stock_available– @quantity where product_id=@product_id

END

Now let’s assume that a new order is to be placed for the product “Baseball Bats” (Product_id=88) at one of the subscriber servers , let’s call it “Subscriber 1”, after the insert the data at the “Subscriber 1” will look like as below:-

Orders:

This insert (3^rd row) will fire the insert trigger and will deduct 20 (100 – 20 =80 )from the “Stock_Available” column in the Products table for Product_id=88.

Products:

After this transaction at “Subscriber 1” , the “Subscriber 1” synchronizes with the Publisher. Now Replication detects that there has been in total two transactions made on “Subscriber 1” which needs to be uploaded to the main Publisher Server.

1. “Insert” made on the table Orders .
2. “Update” made for the table Products for the column “Stock_Available”. (Replication does not care if the update came from a trigger, for Replication it’s another normal update transaction made on the Products table).

After the Sync completes , we expect the data from “Subscriber 1” to get merged with the main Publisher and both the tables should look identical, but this what you will get on the Publisher after the first sync completes.

Orders:

Products:

Oops, what happened there !!! The “Stock_Available” column at Publisher should have been set to “80” and not “60”.

Let me explain

Products is the parent table and Orders is the child table, now by default Merge processing order will replicate the transactions for parent table first followed by child table. As said earlier the update made by the insert trigger is also considered as a normal user transaction , Products table being the parent table, this update transaction flows to the Publisher first which sets the “Stock_Available” column to “80”. Now the following transaction, Insert on Orders table, is also propagated to the Publisher which further kicks off the “AFTER INSERT” trigger present on the Orders table at the Publisher and this trigger goes back to Products table and updates again the “Stock_Available” column value to Current value- Ordered Quantity (80-20=60).

You guessed it right, if you make another sync followed by the first sync without making any further changes, both the Publisher and Subscriber will have the same but wrong value set for “Stock_Available” column (i.e. 60).

Why do we need the “AFTER INSERT” trigger at the Publisher? What if orders can be entered directly at the production server (Publisher) in addition to dispatch unit servers (Subscribers)? The trigger at the Publisher would be required to correctly decrement the Products table.

How to avoid this situation

This is where the “Not for Replication” property comes into picture. Our intention is that if the INSERT is coming from the replication Merge Agent, it does not need to fire the trigger , if the INSERT is coming from a normal user connection, the trigger should get fired to update the available stock level. We can achieve this by marking the trigger as “Not for Replication” at all the replicas.

You can do this by changing the trigger definition as below:

ALTER TRIGGER [dbo].[Update_Stock] 

   ON  [dbo].[Orders]

   AFTER  INSERT

NOT for Replication — ‘marked as not for replication’

Side Note

In the above scenario if Orders Table was the Parent table and Products the child table, you will *not* run into this issue. I think by now you know why. Try it!

The same “Not for Replication” setting applies for Check constraints, Foreign Key constraints and Identity columns on the Publisher and/or the Subscriber. For example, you may want to set a subscriber’s Check Constraint as “Not for Replication” to prevent Replication Agent from being restricted in what data it can replicated from the Publisher.  You can read more about NFR (“Not for Replication”) from the below link from SQL Books Online:-

Controlling Constraints, Identities, and Triggers with NOT FOR REPLICATION
http://msdn.microsoft.com/en-us/library/ms152529.aspx