Category: ReplTalk Blog

Replication and Distributed Availability Groups Limitation

Share this PostCurrently Microsoft SQL Server Always Distributed Availability Groups does not support one global listener for the entire distributed AG. Replication in an Availability Group is currently is configured using a single Listener. Until either Distributed AGs support a single Listener or Replication support multiple Listeners, Distributed AG and Replication environments are not compatible.
Read More »

Change Tracking fixes in SQL 2014 SP4 CU4

Share this PostThe Cumulative Update 4 for SQL 2014 SP3 contains these important fixes for Change Tracking technology. Cumulative Update Package 4 for SQL Server 2014 SP3 – KB4500181 https://www.microsoft.com/en-us/download/details.aspx?id=57663 FIX: Tlog grows quickly when you run auto cleanup procedure in SQL Server 2014, 2016 and 2017 https://support.microsoft.com/en-us/help/4500403/fix-tlog-grows-quickly-when-you-run-auto-cleanup-procedure-in-sql-serv FIX: Syscommittab cleanup causes a lock escalation
Read More »

SSMS 18.1 Replication Monitor missing support for Always On

Share this PostWith SQL Server Management Studio 17.7 release, Replication + Always On technology was fully supported allowing registration\connections via Always On Listeners. This update fully supported use of Tracer Tokens following failover to a secondary Replica. https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-2017#download-ssms-177 What’s new Replication Monitor: Replication monitor now supports registering a listener for scenarios where publisher database and/or
Read More »

How to skip a transaction

Share this PostStored procedure sp_setsubscriptionxactseqno allows Distribution Agent to skip transaction(s) in Transactional Replication. Internally this stored procedure sets the last delivered watermark (LSN) stored in subscriber’s MSreplication_subscriptions table. Upon restarting the Distribution Agent return transactions greater that this watermark (LSN) from the Distribution database cache (msrepl_commands). [crayon-5f32b8e99f3de972094089/] Skipping Errors Skipping Errors is useful when Distribution
Read More »

Setting commitbatchsize returns ‘An invalid parameter or value was specified in the command line.’

Share this PostRecently worked on a case where customer was getting this error when modifing the Distribution agent Job to use CommitBatchSize of 12K and above: Agent message code 20028. An invalid parameter or value was specified in the command line. (parameter = ‘-commitbatchsize’, value = ‘12000’) This error occurs when setting commitbatchsize greater than
Read More »