Ask Learn
Preview
Please sign in to use this experience.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
SQL Server Replication provides multi-faceted data movement capabilities across SQL Server releases which has been used by customers across the globe for a large number of years. When moving from one major release of SQL Server to another, replication topology upgrade has been a constant topic of lengthy discussions. In this post, we shall outline some of the challenges of upgrading SQL Server replication environments to SQL Server 2016. The requirements of upgrading a replication topology need to abide by the following guidelines:
If you had to draw a support matrix for the major release versions for transactional and merge replication, the output would be the two tables shown below.
Publisher | Distributor | Subscriber |
SQL Server 2016 | SQL Server 2016 | SQL Server 2016 SQL Server 2014 SQL Server 2012 |
SQL Server 2014 | SQL Server 2016 SQL Server 2014 | SQL Server 2016 SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 |
SQL Server 2012 | SQL Server 2016 SQL Server 2014 SQL Server 2012 | SQL Server 2016 SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 |
SQL Server 2008 R2 SQL Server 2008 | SQL Server 2016 SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 | SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 SQL Server 2005 SQL Server 2000 |
Publisher | Distributor | Subscriber |
SQL Server 2016 | SQL Server 2016 | SQL Server 2016 SQL Server 2014 SQL Server 2012 |
SQL Server 2014 | SQL Server 2016 SQL Server 2014 | SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 |
SQL Server 2012 | SQL Server 2012 | SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 |
SQL Server 2008 SQL Server 2008 R2 | SQL Server 2008 R2 SQL Server 2008 | SQL Server 2008 R2 SQL Server 2008 SQL Server 2005 SQL Server 2000 |
If you notice the line items for SQL Server 2016, you will see that a topology is unable to support SQL Server 2016 in a number of scenarios when you are running SQL Server 2016 as a publisher. Replication topologies have three common deployment patterns as shown in the visio diagram below. The distributor could be on the publisher or subscriber or a remote distributor. We do come across different deployments of the publisher and subscriber which are a mix of standalone instances, SQL Server failover cluster instances or Always On Availability Group replica instances.
Depending on the deployment pattern, the upgrade path to SQL Server 2016 would be different. Let us explore the different possibilities. SQL Server offers two upgrade paths in general:
The scenarios below apply to Transactional Replication (without P2P Replication, Queued Updating Subscription and Immediate Updating Subscription) and Merge Replication. The options below outline how a phased approach can be adopted for your replication topology upgrade so that you don't have to upgrade all the SQL Server instances in one big upgrade operation.
A common approach that has been adopted for side-by-side upgrades of replication topologies is to move publisher-subscriber pairs in parts to the new side-by-side environment as opposed to a movement of the entire topology. This phased approach helps to control downtime and minimize the impact to a certain extent for the businesses dependent on replication.
Transactional Replication |
||
Upgrading from |
Distributor |
Publisher/Subscriber |
SQL Server 2014 SQL Server 2012 |
Step 1: In-place upgrade (Can be upgraded due to n-2 support) OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology* |
Step 2: In-place upgrade (Can be upgraded due to n-2 support) OR Side-by-side upgrade of subscriber requires reinitialization of subscriber Side-by-side upgrade of publisher requires reconfiguring all the publisher-subscriber pairs The publisher and subscriber can be upgraded in any order |
SQL Server 2008 R2 SQL Server 2008 |
Step 1: In-place upgrade OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology* |
Step 2: In-place upgrade would need to occur for both publisher and subscriber at the same time as publisher and subscriber need to be within two major releases. A SQL Server 2008/R2 publisher/subscriber cannot have a SQL Server 2016 publisher/subscriber. OR Intermediate In-place upgrade to SQL Server 2012/2014 of publisher or subscriber The other server in the publisher/subscriber pair can then be upgraded to SQL Server 2016 OR Side-by-side upgrade will require the upgrade of publisher and subscriber to happen together and requires a re-setup of the publisher/subscriber pairs |
*See "Side-by-side Upgrade of the Distributor without re-initialization" below
Merge Replication |
|||
Upgrading from |
Distributor |
Publisher |
Subscriber |
SQL Server 2014 SQL Server 2016 |
Step 1: In-place upgrade OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology* |
Step 2: In-place upgrade OR Side-by-side upgrade of publisher requires reconfiguring all the publisher-subscriber pairs |
Step 3: In-place upgrade OR Side-by-side upgrade of subscriber requires reinitialization of subscriber |
SQL Server 2008 R2 SQL Server 2008 |
Step 1: In-place upgrade OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology* |
Step 2: In-place upgrade OR Side-by-side upgrade of publisher requires reconfiguring all the publisher-subscriber pairs. Needs to happen with the upgrade of the subscriber. |
Step 3: In-place upgrade OR Side-by-side upgrade of subscriber requires reinitialization of subscriber. Needs to happen with the upgrade of the publisher. |
*See "Side-by-side Upgrade of the Distributor without re-initialization" below
Transactional Replication |
||
Upgrading from |
Publisher/Distributor |
Subscriber |
SQL Server 2014 SQL Server 2012 |
Step 1: In-place upgrade (Can be upgraded due to n-2 support) OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology* |
Step 2: In-place upgrade (Can be upgraded due to n-2 support) OR Side-by-side upgrade of subscriber requires reinitialization of subscriber |
SQL Server 2008 R2 SQL Server 2008 |
Step 1: In-place upgrade (Requires subscriber to be upgraded because publisher and subscriber need to be within two major releases. A SQL Server 2016 publisher cannot have a SQL Server 2008/R2 subscriber.) OR Intermediate in-place upgrade to SQL Server 2012/2014 for the publisher which is acting as the distributor also The subscriber can be upgrade to SQL Server 2016 post the intermediate publisher upgrade OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs served by this distributor in the replication topology. Needs to happen with the upgrade of the subscriber. |
Step 2: In-place upgrade would need to occur for both publisher and subscriber at the same time as publisher and subscriber need to be within two major releases. A SQL Server 2008/R2 publisher/subscriber cannot have a SQL Server 2016 publisher/subscriber. OR Intermediate In-place upgrade to SQL Server 2012/2014 of the publisher The subscriber can then be upgraded to SQL Server 2016 OR Side-by-side upgrade will require the upgrade of subscriber to happen together with the publisher and requires a re-initialization of the subscribers. Needs to happen with the upgrade of the publisher. |
*See "Side-by-side Upgrade of the Distributor without re-initialization" below
Merge Replication |
||
Upgrading from |
Publisher/Distributor |
Subscriber |
SQL Server 2014 SQL Server 2016 |
Step 1: In-place upgrade OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs served by this distributor in the replication topology* |
Step 2: In-place upgrade OR Side-by-side upgrade of subscriber requires reinitialization of subscriber |
SQL Server 2008 R2 SQL Server 2008 |
Step 1: In-place upgrade OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs served by this distributor in the replication topology. Needs to happen with the upgrade of the subscriber. |
Step 2: In-place upgrade OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology. Needs to happen with the upgrade of the publisher. |
*See "Side-by-side Upgrade of the Distributor without re-initialization" below
Transactional Replication |
||
Upgrading from |
Distributor/Subscriber |
Publisher |
SQL Server 2014 SQL Server 2012 |
Step 1: In-place upgrade (Can be upgraded due to n-2 support) OR Side-by-side upgrade requires re-setup of publisher/subscriber pairs in the replication topology served by this distributor* |
Step 2: In-place upgrade (Can be upgraded due to n-2 support) OR Side-by-side upgrade of subscriber requires re-setup of all the publisher/subscriber pairs |
SQL Server 2008 R2 SQL Server 2008 |
Step 1: In-place upgrade (Requires publisher to be upgraded also because subscriber and publisher need to be within two major releases. A SQL Server 2008/R2 publisher cannot have a SQL Server 2016 subscriber.) OR Intermediate in-place upgrade to SQL Server 2012/2014 for the subscriber which is acting as the distributor also The publisher can then be upgraded to SQL Server 2016 post this intermediate distributor upgrade OR Side-by-side upgrade of distributor/subscriber requires re-setup of all the publisher/subscriber pairs served by this distributor. Needs to happen with the upgrade of the publisher. |
Step 2: In-place upgrade would need to occur for both publisher and subscriber at the same time as publisher and subscriber need to be within two major releases. A SQL Server 2008/R2 publisher/subscriber cannot have a SQL Server 2016 publisher/subscriber. OR The publisher can then be upgraded to SQL Server 2016 (Requires intermediate In-place upgrade to SQL Server 2012/2014 of the subscriber) OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology. Needs to happen with the upgrade of the distributor/subscriber. |
*See "Side-by-side Upgrade of the Distributor without re-initialization" below
Merge Replication |
||
Upgrading from |
Distributor/Subscriber |
Publisher |
SQL Server 2014 SQL Server 2016 |
Step 1: In-place upgrade OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology served by this distributor*. This also requires upgrade of the publisher as the publisher version has to be higher than the subscriber. |
Step 2: In-place upgrade OR Side-by-side upgrade of subscriber requires reinitialization of all publisher/subscriber pairs. Requires simultaneous upgrade of the subscriber because it is acting as the distributor. |
SQL Server 2008 R2 SQL Server 2008 |
Step 1: In-place upgrade (Requires simultaneous upgrade of the publisher) OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology. Requires simultaneous upgrade of the publisher. |
Step 2: In-place upgrade (Requires simultaneous upgrade of the subscriber acting as the distributor) OR Side-by-side upgrade requires re-setup of all the publisher/subscriber pairs in the replication topology. Requires simultaneous upgrade of the subscriber because it is acting as the distributor. |
*See "Side-by-side Upgrade of the Distributor without re-initialization" below
If you are running your SQL Server instance to be upgraded on Windows Server 2008 or Windows Server 2008 R2, then you will need to perform a side-by-side upgrade of the distributor first to Windows Server 2012 R2 or Windows Server 2016 before upgrading to SQL Server 2016. The reason for this intermediate OS upgrade is that SQL Server 2016 cannot be installed on a Windows Server 2008/2008 R2 server. The side-by-side approach can also help reduce downtime if you are upgrading the hardware of the Windows Server hosting the distributor instance. Downtime of the publisher and subscriber can be reduced using SQL Server Failover Cluster instances or Always On Availability Groups.
The assumption here is that the edition of the SQL Server instance will not change and a failover cluster instance of SQL Server will be upgraded to a failover cluster instance where as a standalone instance will be upgraded to a standalone instance using the steps mentioned below.
If you want to reduce the downtime, we recommend that you perform the side-by-side migration of the distributor as one activity and the in-place upgrade to SQL Server 2016 as another activity. This will allow you to take a phased approach, reduce risk and minimize downtime.
Upgrading a replication topology is a multi-step process. We recommend attempting an upgrade of a replica of your replication topology in a test environment before running the upgrade on the actual production environment. This will help iron out any operational documentation that is required for handling the upgrade smoothly without incurring expensive and long downtimes during the actual upgrade process. We have seen customers reduce downtime significantly with the use of Always On Availability Groups and/or SQL Server Failover Cluster Instances for their production environments while upgrading their replication topology. Additionally, we recommend taking backups of all the databases including MSDB, Master, Distribution database(s) and the user databases participating in replication before attempting the upgrade.
Recovering a Deleted Cluster Name Object (CNO) in a Windows Server 2008 Failover Cluster Upgrade Replicated Databases Supported Version and Edition Upgrades for SQL Server 2016 Hardware and Software Requirements for SQL Server 2016 SQL Server Upgrade Rename a Computer that hosts a Standalone instance of SQL Server Rename a SQL Server Virtual Server Name
Please sign in to use this experience.
Sign in