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 2008 – SQL Server 2016.
Below is a great blog post from Amit Banerjee on SQL Replication upgrade matrix.
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
