Walkthrough Publisher, Distributor, Subscriber in AlwaysOn Availability Groups

UPDATE: 4/10/2020 The steps below walk through setting SQL Server 2016 Replication Publisher, Distributor, and Subscriber each in an Always On Availability Group with one set of replicas residing one 1 subnet and 2nd set on another subnet simulating 2 different data centers (Pub1, Dist1, Sub2) <–> (Pub2, Dist2, Sub2). More Information: Configure replication with
Read More »

Customizing Distribution Agent Stored Procedures

You may have the need to customize the stored procedures used by the Distribution Agent to apply changes to the Subscriber. For example, if in your particular environment replicated DELETEs often fails with “Row not found” error, by now you’ve most likely change the Distribution Agent to use the “Continue on Errors” Profile or added
Read More »

Change Tracking fixes in SQL 2014 SP4 CU4

The 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 that will
Read More »

not been designated as a valid Publisher

Today while walking customer through Replication + Always On combination we encountered various errors like Msg 14080, Level 11, State 1, Procedure sp_MSvalidate_distpublisher, Line 17 [Batch Start Line 0] The remote server “SQL502” does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.
Read More »

SSMS 18.1 Replication Monitor missing support for Always On

With 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 distributor database
Read More »