Recently I had to create an upgrade plan for a company with 50+ SQL Servers with replication, both transactional and merge, spider-webbed between most of them. In the past I’ve upgraded SQL Server with the replication feature and it was always seemless, but this time the plan was for a large grouping of servers so I re-read the documentation.
It turns out that there are some very real limitations when upgrading publishers and subscribers but they are flexible enough that I believe any DBA who is aware of them could create a successful plan.
What could go wrong?
First, the bad news. If you don’t follow the below requirements set by Microsoft, when you upgrade one of your instances you will be greeted with this error message in your replication monitor.
The SQL Server version of the Publisher server needs to be upgraded to ‘Microsoft SQL Server 2012’. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200956)
Get help: http://help/MSSQL_REPL-2147200956
This completely halts your data transfer and in a scenario where you can’t down-grade your instance you might be strong armed into upgrading your subscriber or publisher.
Rules to follow
SQL Server does not require that you stop activity or modify replication at all in order to upgrade. It is designed to be seamless as long as you follow the below rules.
Reference: MSDN – Upgrade Replicated Databases.
- 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 of the Publisher version. For example: a SQL Server 2005 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012; and a SQL Server 2012 Publisher can have Subscribers running SQL Server 2005, SQL Server 2008 (including SQL Server 2008 R2), or SQL Server 2012.
- A Subscriber to a merge publication can be any version less than or equal to the Publisher version.
When the upgrade goes wrong
It is not common, and in fact I’ve never experienced it, but there are times when your upgrade does not properly upgrade replication. If that happens consider running these system stored procedures before pulling your hair out or possibly even before calling up Microsoft and spending money on support.
- sp_vupgrade_mergetables – undocumented but mentioned in this post.
- sp_vupgrade_mergeobjects – undocumented but mentioned in this post.
These stored procedures are run during the version upgrade and might catch objects missed by a failed upgrade or one that was interrupted part way through.