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

Question: What is the Transactional Replication Supported Version Matrix?

Answer: Most of the projects I do are about Comprehensive Database Performance Health Check. When we start a new project we have limited data and our workflow is not intense. However, I often like to get involved with projects which are not related to performance tuning when I have enjoyed working with them earlier.

Recently, while working on the health check project, one of the realizations which we had with our client was that we need to have another instance of our server where we can forward our read requests. Now the ideal solution was to configure AlwaysOn but that was not something would work out due to licensing issues of the version which the client had available. Additionally, the need for the data was near real-time hence we can’t do the traditional backup/restore or log shipping.

After carefully thinking we decided to opt for the replication. When we started to work with the replication the very first question raised was about the supported version for the transactional replication.

The rules for the supported versions are pretty simple and clearly written on the Replication Backward Compatibility.

The rules for transactional replications are:

• A Distributor can be any version as long as it is greater than or equal to the Publisher version
• A Publisher can be any version as long as it less than or equal to the Distributor version
• A Subscriber to a transactional publication can be any version within two versions of the Publisher version

In short:

• Publisher version <= Distributor version
• Subscriber (two versions) => Publisher or Subscriber (two versions) <= Publisher

For example, our client was using SQL Server 2012 for a publisher. We can use either SQL Server 2012, 2014, 2016, 2017 or 2019 as a distributor and SQL Server 2008/R2, 2012, 2014, or 2016 as a subscriber. You can see the complete matrix over the Transactional Replication Matrix.

Reference: Pinal Dave (http://blog.SQLAuthority.com)