Optimize your distribution database in Transactional Replication – Part 1

Tuesday, March 24, 2015

When thinking about optimizing the distribution database the very first and most basic thing that comes across my mind is to check the settings for the "immediate_sync" property of the publication. Having "immediate_sync" set to true means that delivered commands are not deleted from the distribution database by the next run of the clean up job (Distribution clean up: distribution) but only when the distribution retention period has been crossed which defaults to 3 days (72 hours) and that can be modified using sp_changedistributiondb, see bellow example.. This means that on a busy server the distribution size will grow...
one comment

Perform a count comparison on replicated tables between the publisher and subscriber– enhanced

Monday, April 23, 2012

Here is an improvement to a code posted in an older post Perform a count comparison on replicated tables between the publisher and subscriber. The code introduced in this post also generates (prints) the INSERT commands required to close the diff/gap between the subscriber and publisher. The script is designed to be executed on the subscriber and you will have to edit the 2 parameters: @LinkedServer and @database which represent the linked server to be used for accessing the publisher and the published database. /* Run this script on a subscriber...
one comment

Modify check constraints to NOT FOR REPLICATION

Sunday, December 4, 2011

Here is a script to modify the database schema and define all Check constraints as NFR (NOT FOR REPLICATION). The script does not execute anything but only generates the commands. NFR is required in a 2 way transactional replication and in p2p replication. In a 1 way transactional replication NFR is not required but is a good practice to have the subscriber’s schema set as NFR. For additional information regarding NFR: http://msdn.microsoft.com/en-us/library/ms152529.aspx     /* 1....
no comments

Define identity columns as NOT FOR REPLICATION

Monday, October 31, 2011

  In sql server 2000 there was no other way but the painful costly method of creating the table with a temp name while defining the ident col as NFR (NOT FOR REPLICATION ), move the data, drop the original table, rename the new table and create all other objects related to the table such as constraints. triggers etc. However, sql server 2005 introduced the system procedure sp_identitycolumnforreplication that allows you to achieve the same results while not needing the data movement process described above.   -- Mark Ident column as NOT FOR REPLICATION for...

Monitor Merge Replication Triggers

Tuesday, October 12, 2010

When it comes to monitoring production systems I usually try to think in the direction of what else could possibly go wrong here ? (and no, I am not pessimistic but realistic) This leads me to new ideas of what else can be monitored and how to improve on the current situation and prevent future failures.   Merge replication is trigger based; When a table is added to Merge replication the Snapshot agent creates 3 DML triggers (for INSERT, UPDATE and DELETE) when generating the snapshot. The triggers captures the DML statements excecuted against the table and inserts...

Monitor Identity Ranges in Merge Replication

Saturday, September 18, 2010

  Merge Replication has a built in Identity range management mechanism that automatically assigns identity values to identity columns of tables participating in the replication. The identity ranges are stored in the published database at table MSmerge_identity_range and the history of the range allocations is stored in the distribution database at table MSmerge_identity_range_allocations.  For a more detailed explanation check out this post. This mechanism works well but when things go wrong you may be in a deep trouble because the severity of such an incident is typically high resulting in INSERT statements failing and a downtime of a production system.   If you see...

Restoring a backup set of a published database under a new name removes replication settings from the published database

Sunday, October 18, 2009

Recently, together with Meir Dudai, a database expert from Valinor expertise in replication we found the answer to a situation we faced which remained unresolved for 4 single weeks until 3 day ago. The problem we had faced was that 2 Pull Agents to a 2-way Transactional Replication had disappeared thus causing the replication queue to grow which in terms caused our monitor that monitors the replication queue to alert. When I looked at the UI of SSMS I could not see the pull subscriber jobs and so I looked at msdb sysjobs but none of the 2 jobs where...
no comments