Solution for “nightly batch” and Transactional Replication

“We need to reload a table weekly\nightly, batch job takes few minutes, but Replication takes long time to move the batch changes to Subscriber, how can we speed this up”? If Subscriber is mirror of Publisher, perhaps Replication is the wrong technology! A better solution may be to configure the database for AlwaysOn Availability Groups…


Impact of large batch on Transactional Replication topology

Chris SkorlinskiMicrosoft SQL Server Escalation Services Every write an update statement touching +50 million commands in a single transaction? Ever on a table being Published in Transaction Replication? How about to a subscriber half way around the world or on a satellite connected network? While nothing in Replication design prevents, there may be challenges that…


Distribution Agent fails with 'Cannot update identity column 'OrderID'.'

Chris SkorlinskiMicrosoft SQL Escalation Services I wanted to share this problem which started down a “your data is wrong” path, but ended up with “your customer scripts to create Transactional Replication need to be tweaked”. Customer reported the following error in Replication Monitor: Cannot update identity column ‘OrderID’. Looks like Distribution Agent is trying to…


Script to decrypt the @schema_option for a Transactional Replication Article

Chris SkorlinskiMicrosoft SQL Server Escalation Services The 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:…


ReplTip – Technique to exceed column limits for Merge Replication

Chris SkorlinskiMicrosoft SQL Server Escalation Services While working with a customer on a Merge Replication project, we encounter a potential road block. They had a published table which exceeded the number of columns allowed for Merge Replication. From this article https://technet.microsoft.com/en-us/library/ms151749(v=sql.105).aspx: If your application does not require column-level tracking, it is recommended that you use…


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….