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 »
Jared Poché, MCSE, MCDBASQL Server Sr. Support Escalation EngineerProduct Support Services (PSS) – Charlotte, NC — updated 2/24/2019 One of the advantages of replication is that subscribing servers can be used for reporting thereby offloading RO query activity from the Publisher. Recently I worked on a performance case on such a replication subscriber, which would
Read More »
SQL Server Transaction LogReader Agent timeout executing sp_replcmds occurs most often when a large transaction(s) are written to the Published database transaction log, or 100s of millions of un-replicated commands from logged index maintenance or changes to replicated tables. Either problem requires the LogReader to read more log entries than the timeout parameter allows causing
Read More »
If you need to reset your Replication topology you can quickly rebuild subscribers using the “no initialize” also called “Replication Support Only (RSO)”. WARNING: These steps are designed to remove ALL of your Replication configuration and settings, recreating with new metadata tables. The RSO option skips the Snapshot data reinitialization phase. It only pushed down new
Read More »
I’m 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 monitor end-to-end Transactional Replication Latency. Tracer
Read More »