Hey I Said NOT To Replicate Delete Statements!

<div><p>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:</p> <p><img alt="Statement delivery options for all table articles" border="0" height="508" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibYgzcnABV5bUyTasgp96WftPrRfKvYvV7ZUASfHH7av8VAyCnQhQ8-wVFW5toLN6qaHLLJgDQtQvHp1Es9VxLUzEp_q7guITv1Mjixrny2GOh286jbdY5S6gR1uXXqLdGPycr0l-Z6OwY/?imgmax=800" style="display: block; float: none; margin-left: auto; margin-right: auto" title="Statement delivery options for all table articles" width="456" /> </p> <p>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 <em>know</em> that you set the publication up properly. So what gives??</p> <p><img align="right" alt="Be careful when using this..." border="0" height="105" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2A4D9vUKE_VYHV0CMWOprPSOaZWFX-DAeBdr5qlzTLhdcMIEeH34maevLaW5lKElY4CNX7zHi8x0LkkV1U-3UUpOC5o8bRodfojr74KkeSZGcDu8W6-SrhoV5Wui4_8jL40FuLzntqLgm/?imgmax=800" style="border-top-width: 0px; display: inline; border-left-width: 0px; border-bottom-width: 0px; margin: 0px 0px 10px 10px; border-right-width: 0px" title="Be careful when using this..." width="325" />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 <a href="http://msdn.microsoft.com/en-us/library/ms173857(SQL.90).aspx" target="_blank">sp_addarticle</a> 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.</p> <p>There are three ways to get around this problem:</p> <ol> <li>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. </li> <li>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 <a href="http://msdn.microsoft.com/en-us/library/ms173857(SQL.90).aspx" target="_blank">sp_addarticle</a> call for the corresponding statements that you do not want delivered. </li> <li>If you publication already exists, call <a href="http://msdn.microsoft.com/en-us/library/ms175980(SQL.90).aspx" target="_blank">sp_changearticle</a> 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. </li> </ol> <p>Fortunately this behavior has been corrected in SSMS 2008.</p> </div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Replication SQL Server 2005 Tips Troubleshooting