CDC functionality may break after upgrading to the latest CU for SQL Server 2012, 2014 and 2016

Chris SkorlinskiSQL Server Escalation Services This problem was brought to our attention recently by one of our customers. CDC LogScan job fails, possible Error Message: Log Scan process failed in processing log records.Operand type clash: int is incompatible with uniqueidentifierOperand type clash: int is incompatible with datetimeThe Log-Scan Process failed to construct a replicated command…


“Replication Monitor could not insert the tracer token”

Chris SkorlinskiMicrosoft SQL Server Escalation Services We’re seeing an uptick in customers using combination of AlwaysOn and Transactional Replication. Some are configuring the Publisher for high-availability, and some are configuring the Subscriber with Read-Only Secondary to prevent contention from the Distribution Agent updates and “reporting” read queries. Once configured, Tracer Tokens are often used to…


Transaction Log won’t truncate when using Snapshot Replication to Azure SQLDB

Chris SkorlinskiMicrosoft SQL Server Escalation Services Customer encountered transaction log growth for a Published SQL Server 2016 database while nightly refreshing Azure SQLDB. We discovered the default publication setting “replicate_dll” was still enabled. SELECT [description],[name],[replicate_ddl] FROM <your published database>.[dbo].[syspublications] description name replicate_ddl —————————————————— ———- ————- Snapshot publication of database ‘AdventureWorks2014’ Customers 1 (1 row(s) affected)…


SQL Server Replication Upgrade Strategies

In the recent past, we have seen questions on the strategy/steps on upgrading a replication topology especially from customers using SQL Server 2008/2008 R2 or running SQL Server on Windows Server 2008 and 2008 R2. We have outlined the most common topologies for replication and the possible upgrade paths for all releases from SQL Server…


Clarification on MSDN Initialize a Transactional Subscription from a Backup

Chris SkorlinskiMicrosoft SQL Escalation Services I hope this blog posting will provide some clarification into the MSDN article providing steps on how to initialize a transactional subscription from a backup. https://msdn.microsoft.com/en-us/library/ms147834.aspx First I want to clarify the @backupdevicename parameter. The article reads like the “DISK = “should be included in the parameter, it should not….


Repltalk – Replication Tuning Knobs – LogReader

Chris SkorlinskiMicrosoft SQL Server Escalation Services If you’re familiar with Microsoft SQL Server Replication technology you already know each Replication Agent has various parameters or knobs you can set via Agent Profile or SQL Agent job parameter. This series of posts provide insight into tuning Replication performance using these knobs. Following the 80-20 rule, 80%…


Queries to build START and STOP commands for PUSH Distribution Agents

Chris Skorlinski, Microsoft SQL Server Escalation Services I had need other day to STOP and START all Distribution Push Agent.  Using the syssubscriptions and syspublications I can easily build list of SQL commands to either stop or start all the Distribution Push Agents.  With output to text, I can copy and execute from SQL Agent…


Script to show Replication Topology

Chris Skorlinski, Microsoft SQL Server Escalation Services I came across this forgotten but very handy script from my friends Suhas and Gaurav to display your Replication topology. The output below is from SQL 2016 sample publications using AdventureWorksLT.   https://gallery.technet.microsoft.com/Display-Replication-b15e730c ============================================================= REPLICATION TOPOLOGY ============================================================= SELECT THE PUBLICATION-SUBSCRIPTION PAIR FOR SCOPING THE CASE =============================================================   SQL2016…


Challenges running Continuous Merge Agents

Chris SkorlinskiMicrosoft SQL Server Escalation Services I wanted to cover in this blog challenges you’ll encounter running Merge Agent on continuous schedule.  These are Merge Agents whose SQL Server Agent jobs schedule are set for “Start automatically when SQL Server Agent starts”.     Each time a Merge Agent starts it logs start time in…


Suppported SQL Server versions in Replication Topology

Chris Skorlinski, Microsoft SQL Server Escalation Services Customer planning for migration to SQL Server 2016 asked for clarification on running mixed version of SQL Servers in their Replication Topology.  I searched for “mixed version”, but was unable to find our posted topic. My colleague George Mason tracked it down under “upgrade”.  I’m posting key paragraph…