Suppported SQL Server versions in Replication Topology
Chris Skorlinski, Microsoft SQL Server Escalation Services
Customer planning for migration to SQL Server 2016 asked for clarification on running mixed version of SQL Servers in their Replication Topology. I searched for “mixed version”, but was unable to find our posted topic. My colleague George Mason tracked it down under “upgrade”. I’m posting key paragraph along with quick reference examples.
Upgrade Replicated Databases
https://msdn.microsoft.com/en-us/library/ms143699.aspx
SQL Server 2016 supports upgrading replicated databases from previous versions of SQL Server; it is not required to stop activity at other nodes while a node is being upgraded. Ensure that you adhere to the rules regarding which versions are supported in a topology:
- 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 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.
Key point Publisher is within 2 builds of the Subscriber and the Distributor is same or higher build then Publisher.
These are supported:
- Publisher (2008/R2) -> Distributor (2016) -> Subscriber (2012)
- Publisher (2014) -> Distributor (2016) -> Subscriber (2008/R2)
- Publisher (2016) -> Distributor (2016) -> Subscriber (2012)
Not supported:
- Publisher (2016) -> Distributor (2016) -> Subscriber (2008/R2)
- Publisher (2008/R2) -> Distributor (2016) -> Subscriber (2016)
In chart above SQL Server 2008/R2 means either SQL Server 2008 or SQL Server 2008R2. For all versions, ensure you’re running on most recent SQL Server service pack to take advantage of software fixes and enhancements. Check out Microsoft Release Services blog for updates on service packs and cumulative updates.
you’re writing about upgrading database in a replication setup.
does apply the same restriction – eg. 2 builds – also for normal transaction replication without upgrading the database.
e.g.
publisher 2008/r2 -> distributor(2008/r2) -> subscriber 2016
the subscription will be done without initialisation/snapshot but with identical data on 2008/r2 and 2016.
would the following topology supported with transactional replication:
publisher (2014) -> distributor (2014) -> subscriber (2008/r2)
publisher (2014) -> distributor (2014) -> subscriber (20016)
both replication chain are within 2 builds.
I believe you missed an important bullet point from the reference material:
– A Subscriber to a merge publication can be any version less than or equal to the Publisher version.
My understanding of this is that a 2016 publisher can have 2008/R2 subscribers for merge replication. Please could you confirm this?
Is this supported?
Publisher (2008/R2) -> Distributor (2014) -> Subscriber (2016)
This combination is not supported as Subscriber is not within 2 builds of the Publisher.
NOT SUPPRTED: Publisher (2008/R2) -> Distributor (2014) -> Subscriber (2016)
When setting Replication, can you have Distributor on SQL Server 2005, Publisher of SQL Server 2008?
Rule of thumb, have the Distributor the highest version in your topology: “A Distributor can be any version as long as it is greater than or equal to the Publisher version”
This means you can’t have a SQL 2005 Distributor with a SQL 2008 Publisher.
This combo is supported right?
Publisher ( 2012 std lic) -> Distributor( Same server as publisher 2012 std lic) -> Subscriber ( 2016 std lic)
A 2012 Publisher\Distributor supports 2016 Subscriber since the 2016 is within 2 builds (2012, 2014, 2016).
Does anyone know why MS puts this limit on replication? AWS has no such limit.
Our developers add logic to Replication stored procedures to handle unique variations of each build of SQL Server. To reduce complexity of stored procedures it was decided to only include logic for 2 versions.
Other data replication technologies based on different solutions such as CDC or transaction log readers don’t have those requirements. You can always add those on top of your SQL Server to move data throughout your environment.