Upgrading a Replication Topology to SQL Server 2016


Cross posting from Tiger blog.

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:

  • A Distributor can be any version as long as it is greater than or equal to the Publisher version (in many cases the Distributor is the same instance as the Publisher).
  • A Publisher can be any version as long as it less than or equal to the Distributor version.
  • Subscriber version depends on the type of publication:
    • A Subscriber to a transactional publication can be any version within two versions (n-2) of the Publisher version. For example: a SQL Server 2012 Publisher can have SQL Server 2014 and SQL Server 2016 Subscribers; and a SQL Server 2016 Publisher can have SQL Server 2014 and SQL Server 2012 Subscribers.
    • A Subscriber to a merge publication can be any version less than or equal to the Publisher version.

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.

For the remainder of this post, transactional replication refers to Transactional Replication excluding heterogeneous replication, P2P replication and updateable subscribers.

Transactional Replication Matrix

Publisher Distributor Subscriber
SQL Server 2016 SQL Server 2016 SQL Server 2016SQL Server 2014

SQL Server 2012

SQL Server 2014 SQL Server 2016SQL Server 2014 SQL Server 2016SQL Server 2014

SQL Server 2012

SQL Server 2008 R2

SQL Server 2008

SQL Server 2012 SQL Server 2016SQL Server 2014

SQL Server 2012

SQL Server 2016SQL Server 2014

SQL Server 2012

SQL Server 2008 R2

SQL Server 2008

SQL Server 2008 R2SQL Server 2008 SQL Server 2016SQL Server 2014

SQL Server 2012

SQL Server 2008 R2

SQL Server 2008

SQL Server 2014SQL Server 2012

SQL Server 2008 R2

SQL Server 2008

SQL Server 2005

SQL Server 2000

Merge Replication Support Matrix

Publisher Distributor Subscriber
SQL Server 2016 SQL Server 2016 SQL Server 2016SQL Server 2014

SQL Server 2012

SQL Server 2014 SQL Server 2016SQL Server 2014 SQL Server 2014SQL Server 2012

SQL Server 2008 R2

SQL Server 2008

SQL Server 2012 SQL Server 2012 SQL Server 2012SQL Server 2008 R2

SQL Server 2008

SQL Server 2008SQL Server 2008 R2 SQL Server 2008 R2SQL Server 2008 SQL Server 2008 R2SQL 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.

image

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:

  • Side-by-side: This approach involves setting up a new parallel environment and moving the databases along with the associated instance level objects like logins, jobs etc. to the new environment.
  • In-place upgrade: With this approach, the SQL Server setup program upgrades the existing SQL Server installation by replacing the existing SQL Server bits with the SQL Server 2016 bits and then upgrades each of the system and user databases. For environments running SQL Server failover cluster instances or Always On Availability Groups, an in-place upgrade is combined with a rolling upgrade to minimize downtime.

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.

Upgrading a Replication Topology with a Remote Distributor

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

Upgrading a Replication Topology with Publisher acting as the Distributor

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

Upgrading a Replication Topology with Subscriber acting as the Distributor

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

Side-by-side Upgrade of the Distributor without re-initialization

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.

Steps for side-by-side migration of the distributor to Windows Server 2012 R2

  • Setup a new failover cluster or standalone instance running the same major release, edition and version as your distributor on Windows Server 2012 R2/2016 with a different windows cluster and SQL Server FCI name or standalone host name. You will need to keep the directory structure same as the old distributor to ensure that the replication agents executables, replication folders and database file paths are found at the same path on the new environment. This will reduce any post migration/upgrade steps required.
  • Make sure that the current synchronization is complete and post that shut down all the replication agents
  • Shut down the current SQL Server failover cluster instance or standalone instance running as the distributor. If this is a standalone instance of SQL Server, you will need to shutdown the Windows Server hosting  the SQL Server instance to ensure that there is no conflict while renaming the server.
  • Remove the DNS entries for the old (current distributor instance) environment and the AD entries for the computer object for the SQL Server FCI
  • If this is a SQL Server Failover Cluster instance, rename the new SQL Server Failover Cluster instance name with the old virtual server name. If this is a standalone SQL Server instance, then rename the new standalone host with the old hostname.
  • Copy the database files from the previous instance using SAN redirection or storage copy or file copy
  • Bring the new SQL Server instance online
  • Restart all the replication agents and verify if the agents are running successfully
  • Validate if replication is working as expected

In-place upgrade to SQL Server 2016

  • Run in-place upgrade for SQL Server 2016 on the new cluster
  • If required, rebuild old nodes and add to the cluster to re-use existing hardware
  • Validate if replication is working fine

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.

Summary

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.

Resources

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

Advertisements

Change Tracking Cleanup


Cross post from SQL Server Tiger Blog.

Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications which was introduced in SQL Server 2008. We recently had a number of customers ask us about how Change Tracking Cleanup works and how they can troubleshoot further if the cleanup is not working as expected. In the first part of this blog post, I will explain how Change Tracking cleanup works and what “information” is cleaned up by the automatic cleanup task. I will also touch upon what enhancements were shipped in SQL Server 2014 and above to help cleanup more efficiently.

Change Tracking cleanup is invoked automatically every 30 minutes. The default retention period is 2 days. An example of setting the automatic cleanup for Change Tracking information is shown below.

ALTER DATABASE <DBNAME> SET CHANGE_TRACKING = ON  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Automatic Cleanup

Each table that is enabled for Change Tracking has an internal table (a.k.a. side table with the naming convention: change_tracking_<#>) which is used by Change Tracking functions to determine the change version and the rows that have changed since a particular version. Every time the automatic cleanup thread wakes up, it scans all the user databases on the SQL Server instance to identify the change tracking enabled databases. Based on the retention period setting of the database, each side table is purged of its expired records. The automatic cleanup removes rows from the on-disk tables based on the retention period defined for the database.

Change tracking information is stored for all tables (enabled for Change Tracking) in a database in an in-memory rowstore (syscommittable). This in-memory rowstore is flushed every checkpoint to the on-disk table (syscommittab). Rows from the syscommittab internal table are removed during every checkpoint.

Manual Cleanup

In SQL Server 2014 Service Pack 2 and above, we provided a new Stored Procedure, sp_flush_CT_internal_table_on_demand, to assist with Change Tracking cleanup. KB3173157 has more details. This stored procedure accepts a table name as parameter and will attempt to cleanup records from the corresponding change tracking internal table.  During the course of the deletion, it will print some verbose in the output window about the progress of deletion.

In case you want to automate the cleanup for all tables, you can use a while loop to execute this stored procedure against all the tables or tables that receive a high number of changes to prevent automatic cleanup from lagging in cleaning up records from the Change Tracking internal tables. A sample manual cleanup T-SQL script is available on the tigertoolbox GitHub repo: ChangeTrackingCleanup.sql (see screenshot below).

In the above section, I talked about how automatic and manual cleanup happens in SQL Server. In this post, we will explore a bit more in depth on how the cleanup actually works with the help of some metadata from an actual Change Tracking implementation.

ChangeTracking auto cleanup is a background thread which wakes up at a fixed frequency and purges expired records (records beyond retention period) from the change tracking side tables. There are two cleanup versions that this thread maintains over the course of the cleanup action – invalid cleanup version and hardened cleanup version. When the thread wakes up, it determines the invalid cleanup version. The invalid cleanup version is the change tracking version which marks the point till which the auto cleanup task will perform the cleanup for the side tables. The autocleanup thread traverses through the tables that are enabled for change tracking and calls an internal stored procedure in a while loop, deleting 5000 rows in a single call within the while loop. The loop is terminated only when all the expired records in the side table are removed. This delete query uses the syscommittab table (an in-memory rowstore ) to identify the transaction IDs that have a commit timestemp less than the invalid cleanup version. This process is repeated until the cleanup is done with all change tracking side tables for that particular database. Once this is done with the final change tracking side table, it updates the hardened cleanup version to the invalid cleanup version.

Every time a checkpoint is run, an internal procedure is called that uses the hardened cleanup version and deletes a minimum of 10k records from sys.syscommittab table after they are flushed to the disk-based side tables. As you can see, both cleanup (in-memory rowstore and disk based side tables) are inter-dependent and having an issue with one of these might affect the other cleanup, eventually leading to unnecessary records in sys.syscommittab and delays in CHANGETABLE functions. See screenshot below of an extended event session tracing the checkpoint of a database which shows operations on the sys.syscommittab table.

clip_image001

Below is the output of calling the stored procedure for manual cleanup stored procedure, “sp_flush_CT_internal_table_on_demand”. I had inserted 50K rows and random updates to three tables t2, t3 and t4. The change data was cleaned up by the automatic cleanup post the retention period. Post the cleanup, I inserted another 50K rows into the table t2. After that I executed the manual cleanup procedure which did not have any cleanup to perform as the change data was within the retention period.

 

Cleanup Watermark = 103016

Internal Change Tracking table name : change_tracking_885578193

Total rows deleted: 0.

— Query to fetch cleanup version for a change tracking table

select

object_name (object_id) as table_name,

is_track_columns_updated_on,

min_valid_version,

begin_version,

cleanup_version

from sys.change_tracking_tables

The screenshot of the SSMS output grid that you see below is from the query above.

clip_image002

A new extended event, “change_tracking_cleanup”, was added to track change tracking automatic cleanup activities. The T-SQL script used to fetch the information below can be found on our tigertoolbox github repository.

As you can see from the screenshot below, the cleanup task shows you when the cleanup started and completed. Additionally, you get granular details like when the retention timestamp was updated which is an easy way of co-relating the invalid cleanup version to a timestamp value (see UpdateRetention and UpdateInvalidCleanup steps below). The side table object IDs shown below have line items reflecting the number of rows cleaned up and the start and end of the change tracking cleanup. One aspect to keep in mind is that the update retention timestamp is reflected in UTC and you will need to do the necessary conversion to get the time aligned with the server’s local timezone.

clip_image003

To summarize, we suggest the following steps when troubleshooting change tracking cleanup issues:

  1. Ensure that auto cleanup is working properly using the Extended Event “change_tracking_cleanup”

  2. If automatic cleanup is running slowly, then you can execute the stored procedure “sp_flush_CT_internal_table_on_demand”. In SQL Server 2014 Service Pack 2 and above, we provided a new Stored Procedure, sp_flush_CT_internal_table_on_demand, to assist with Change Tracking cleanup. KB3173157 has more details.

Attaching a CDC enabled database on SQL Server 2016


When you detach a database with Change Data Capture enabled on SQL Server 2014 and below and attach it to a SQL Server 2016 instance, you could run into the error mentioned below while execute Change Data Capture (CDC) related procedures.

Error:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 639 [Batch Start Line 0]

Could not update the metadata that indicates table [<schema name>].[<object name>] is enabled for Change Data Capture. The failure occurred when executing the command ‘insert into [cdc].[captured_columns]’. The error returned was 213: ‘Column name or number of supplied values does not match table definition.’. Use the action and error to determine the cause of the failure and resubmit the request.

This is due to the fact that there is a metadata change in SQL Server 2016 associated with Change Data Capture which does not happen till you manually execute sys.sp_cdc_vupgrade against the newly attached database on the SQL Server 2016 instance.

The “Attach a Database” online documentation has also been updated to reflect this information.