On the Replication Backward Compatibility, the supported version for Transactional replication and the rules for the supported versions are rather straightforward and clearly described.
The Transactional Replication Rules are as follows:
–> Any version that is more than or equal to the Publisher version can be a Distributor (in many circumstances, the Distributor and Publisher are the same instance).
–> A Publisher can be any version that is less than or equal to that of the Distributor.
–> The type of publication determines the subscriber version:
>> The Subscriber of a transactional publication might be any of the two variants of the Publisher. A SQL Server 2012 (11.x) Publisher, for example, can have subscribers from SQL Server 2014 (12.x) and SQL Server 2016 (13.x), while a SQL Server 2016 (13.x) Publisher can have subscribers from SQL Server 2014 (12.x) and SQL Server 2012 (11.x).
>> All versions equal to or lower than the Publisher version that are supported according to the versions life cycle support cycle can be Subscribers to a merging publishing..
We can also put it another way, which is simple and brief.
When it comes to our environment, the client wants to set up high availability between SQL 2012 with Read/Write operations and SQL 2016 with Read operations.
Initially, we considered configuring AlwaysOn, but due to licencing concerns with the version that the client owned, this was not a viable option. Furthermore, because the data was required in near-real-time, we were unable to use typical backup/restore or log shipping methods ( As the Readonly mode is not supported because of two mixed Versions ).
We chose to go with Replication after much deliberation. As a result, the setup will be as follows:
As a Publisher, our client was using SQL Server 2012. We can utilise SQL Server 2012, 2014, 2016, 2017, or 2019 as a Distributor, and SQL Server 2008/R2, 2012, 2014, or 2016 as a Subscriber.
To learn more about Matrix over Replication, visit the blog.