SQL Server Replication – Hey I Said NOT To Replicate Delete Statements!

SQL Server ReplicationOriginally published on KendalVanDyke.com.

You’ve been given the task to set up a new publication and one of the requirements is that delete statements should not be replicated. No problem, you know just what to do – set the delete delivery format for your articles in the publication properties to “Do not replicate DELETE statements”, like so:

Statement delivery options for all table articles

You create your publication, set up your subscribers, and go about your day…only to have Joe Salesguy come into your office the next day in a panicked frenzy yelling “The data’s gone, the data’s gone! What did you do with the data?!”. Not convinced he knows what he’s talking about, you pull up Management Studio, run a few queries, and much to your chagrin you discover that sure enough, the data’s gone. Someone issued delete statements on your publisher that replicated out to your subscribers, even though you know that you set the publication up properly. So what gives??

Be careful when using this...It turns out that there’s a nasty “feature” in the article properties window which affects all three delivery formats. When the option to set the properties of all table articles is selected the underlying script that is generated is missing the parameters @ins_cmd, @upd_cmd, or @del_cmd from sp_addarticle for whichever commands the “Do not replicate ins/upd/del statements” was selected for. When the parameters are missing  SQL tries to fill in the gap and what you end up with is statements that get replicated even though you didn’t want them to.

There are three ways to get around this problem:

  1. When setting up your publication change the properties of the article(s) that you do not want to replicate commands for individually, rather than setting the properties of all articles at once.
  2. When setting up your publication and setting the properties of all articles at once, instead of letting the UI create the publication specify that you want to script the commands to a file. Open the file, and add the parameters @ins_cmd, @upd_cmd, and\or @del_cmd with a value of “NONE” to each sp_addarticle call for the corresponding statements that you do not want delivered.
  3. If you publication already exists, call sp_changearticle for each article in your publication and supply the necessary parameters to change the delivery format for inserts, updates, and deletes. This option is a bit more intrusive though because it will force you to reinitialize your subscriptions.

Fortunately this behavior has been corrected in SSMS 2008.

 

About the Author

Microsoft SQL Server MVP & Principal Consultant

Kendal Van Dyke

UpSearch Alum Kendal Van Dyke is a database strategist, community advocate, public speaker and blogger. He is passionate about helping leaders use Microsoft's SQL Server to solve complex problems that protect, unlock and optimize data's value.

Since 1999, Kendal has specialized in SQL Server database management solutions and provided IT strategy consulting. Kendal excels at disaster recovery, high availability planning/implementation and debugging/troubleshooting mission critical SQL Server environments.

Kendal Van Dyke served the SQL Server community as Founder and President of MagicPass, the Orlando, FL based chapter of the Professional Association for SQL Server (PASS). In 2012, Kendal served as a member of the PASS Board of Directors.

Kendal remains active in the SQL Server community as a speaker and blogger. He teaches SQL Server enthusiast and technology leaders how to protect, unlock and optimize data’s value. Since 2008, Kendal has operated a SQL Server focused blog at http://www.kendalvandyke.com/.

Microsoft acknowledged Kendal for his support and outstanding contributions to the SQL Server community by awarding him Microsoft MVP (2011-15). Learn more about Kendal Van Dyke https://upsearch.com/kendal-van-dyke/.

About UpSearch

up-social-round

UpSearch is a leading Microsoft Gold Partner for organizations who rely on Microsoft’s Data Platforms, and its mission is to enable every leader to unlock data’s full potential.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *