Transactional Replication Mixed Version Matrix or Supported Replication Backward Compatibility

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

Rules for the Transactional Replication are :

–> 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 (11.x) Publisher can have SQL Server 2014 (12.x) and SQL Server 2016 (13.x) Subscribers; and a SQL Server 2016 (13.x) Publisher can have SQL Server 2014 (12.x) and SQL Server 2012 (11.x) Subscribers.

>> A Subscriber to a merge publication can be all versions equal to or lower than the Publisher version which are supported as per the versions life cycle support cycle.

In another way which is Simple and in short we can say

>> Publisher version <= Distributor version

>> Subscriber (two versions) => Publisher or Subscriber (two versions) <= Publisher

In Short and Simple T-replication supported VERSIONS

Coming to our Environment the Client is asking to Setup Hight Availability between SQL 2012 with Read\Write operation and SQL 2016 with Read Operations

Initially we are thinking 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( As the Readonly mode is not supported because of two mixed Versions ).

After carefully thinking we decided to go with Replication .So, the setup will looks like :

Our client was using SQL Server 2012 as 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

Checkout the blog to get complete Matrix over Replication https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-replicated-databases?view=sql-server-ver15#transactional-replication-matrix