Sql server transactional replication queued updating
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.
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. 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.
While the actual data to be replicated are within user databases, SQL Server replication uses objects and artifacts outside the user databases, such as master database, msdb database, distribution databases, and even file systems.
SQL Server uses availability groups (AG) to provide physical data replication capability for a group of databases among a set of SQL Server instances.
Replication uses the distribution databases hosted on a distributor to centrally manage and control the configuration and operation of data replication.
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.
A SQL Server 2008/R2 publisher/subscriber cannot have a SQL Server 2016 publisher/subscriber.
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 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 : 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.
Search for sql server transactional replication queued updating:
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.