Category: Inside Replication

Enabling Transactional Subscriber with Identity Ranges for Updates

Share this PostTransactional Replication published tables containing Identity Ranges are updated on subscriber via Replication Distribution Agent.  This processes does not increment the subscriber’s table Identity Seed starting value. If you drop a Subscriber and want to bring it online as an updatable user database, first execute CHECKINDENT reseeding the table to highest value +
Read More »

Inside Distribution Cleanup Watermark

Share this PostThe Distribution database is a cache of undistributed commands that the Distribution SQL Agent jobs are responsible to deliver to their subscribers.  This cache is populated by the Publisher LogReader Agent job as new data changes are detected. The primary tables holding the cache of undistributed data is MSrepl_transactions and MSrepl_commands. Share this
Read More »

Script to decrypt the @schema_option for a Transactional Replication Article

Share this PostThe code below helps decrypt @schema_option settings for Transaction Replication articles. Note the meaning of binary offset may change in future builds of SQL Server. Always check Microsoft docs for most accurate listing. You’ll find scheme_options listing documented at sp_addarticle. Code for this example now posted on https://github.com/ReplTalk/ReplScripts/blob/master/schema_option.sql Example: [crayon-5d60fe92bf1ed864517660/] Share this Post

ReplTip – Setting LogScanThreshold can result in LogReader failure

Share this PostContent provided by Sateesh Yele, Microsoft SQL Escalation Services BOL/MSDN documents –Logscanthreshold as “internal use only”. https://technet.microsoft.com/en-us/library/ms146878(v=sql.110).aspx -LogScanThreshold scan_threshold Internal use only. Other blog postings imply how to adjust this setting for better Logreader agent performance.  If you add this parameter to the Logreader SQL Agent command line and start the agent, Logreader
Read More »