What is Transactional Replication Supported Version Matrix? – Interview Question of the Week #274

Question: Which SQL Server versions can participate in transactional replication together? Check the Publisher, Distributor and Subscriber as separate roles in Microsoft’s replication compatibility matrix.

This arose during a Comprehensive Database Performance Health Check. The client wanted another instance to handle reads with near-real-time data. Their available licensing ruled out the Always On configuration we had considered, and the delay of their backup/restore approach wasn’t suitable. We chose to investigate transactional replication.

Before building the topology, we checked version compatibility. Replication is not simply a copy of a database to any newer or older server.

The role-specific rules

• The Distributor must be the same or a newer version than the Publisher.
• For transactional replication, the Subscriber is generally within two SQL Server release versions of the Publisher, subject to the published matrix.
• The Publisher and Distributor must be the same product: SQL Server with SQL Server, or Azure SQL Managed Instance with Azure SQL Managed Instance.

Count SQL Server releases, not calendar years. Merge replication has different rules. Product, publication type, edition and supported features also matter, so the short version-distance rule is not a substitute for the matrix.

The original SQL Server 2012 example

In the original project, the Publisher was SQL Server 2012. The historical matrix allowed 2012 or later supported Distributor combinations, and listed 2008, 2008 R2, 2012, 2014 and 2016 Subscribers for that Publisher. The two 2008 releases make release-count shorthand particularly easy to misread.

That is a historical compatibility example, not a recommendation to deploy those old versions now. Before an upgrade or a new topology, check the exact versions, servicing requirements and lifecycle status. Test the planned publication and application workload as well as the initial synchronization.

For an upgrade, the Distributor is the first role to plan, not an afterthought.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.