Category: Inside Replication

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: ---------------------------- --Set the schema_option
Read More »

ReplTip – Foreign Key Check Constraints

Share this PostI recently worked with a customer seeing very slow transfer rate with Distribution Agent. Looking at RPC:Completed events we saw each sp_Msdel_ took .5 second generating over 200,000 reads. What, 200,000 reads to delete 1 row? Query plan should so small reads with 1 delete like shown below. StmtText -------- Clustered Index Delete(OBJECT:([AW2012_SUB].[SalesLT].[SalesOrderDetail].[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID])
Read More »

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 »

ReplTip – Subscribe to “all” restriction using “Initialize with backup”

Share this PostReplTip – Subscribe to “all” restriction using “Initialize with backup” Chris Skorlinski Microsoft SQL Server Escalation Services I ran into a couple of challenges using the Backup\Restore method to setup a SQL Server Replication Subscriber.  In particular, using this method you are unable to subscribe or un-subscribe to an individual article.  Using Backup\Restore
Read More »