SQL Server Replication Upgrade Strategies

Share this Post

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 2008 – SQL Server 2016.

Below is a great blog post from Amit Banerjee on SQL Replication upgrade matrix.

Blog post: https://blogs.msdn.microsoft.com/sql_server_team/upgrading-a-replication-topology-to-sql-server-2016/

Books online link referring the blog post: https://msdn.microsoft.com/en-US/library/ms143699.aspx

As you explore upgrading SQL Server step back and look if newer technologies provide a better business fit. For example, customers using Transactional Replication to provide disaster recovery are now moving to AlwaysOn for SQL Server 2016. Offloading reporting functionality is also provided with AlwaysOn with readable secondary Replicas providing both disaster recovery and offloading publisher activity all with a single solution.

Speaking of integrating newer technologies, those using Transactional Replication Subscribers for reporting know “reporting” load and “transaction” load from Replication can lead to performance problems such as blocking impacting flow of data. One way creative way to reduce this blocking is to configure the Subscriber in an AlwaysOn Availability Groups. Transaction data flows to Primary Subscriber, then AlwaysOn ships log records to Subscriber secondary supporting Reporting functionality.

Chris Skorlinski
Microsoft SQL Server Escalation Services

Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.