Replication

Question

Replication

Admingod

SSCertifiable

Points: 5997
More actions
May 6, 2020 at 7:23 pm

#3749475

I am thinking to configure Always on or replication from two databases to 1 single database for reporting and consolidating the dbs . Current prod is on sql 2016 and planning to configure the reporting server with SQL 2017 and configure Always on between SQL 2016 primary and SQL 2017 secondary. My understanding is lower to higher version can be configured for Always on or replication. Do you see any issues with this setup?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Admingod SSCertifiable Points: 5997 More actions · Answer 1

I understand that it won't be possible to configure Always on from 2 database to 1 database. Yes it could be done with replication. But Primary as sql 2016 and secondary as SQL 2017 would that be a problem?

Sue_H SSC Guru Points: 90860 More actions · Answer 2

Admingod wrote:

I understand that it won't be possible to configure Always on from 2 database to 1 database. Yes it could be done with replication. But Primary as sql 2016 and secondary as SQL 2017 would that be a problem?

Replication has publishers and subscribers instead of primaries and secondaries. And the version of the distributor makes a difference in terms of what is supported. The matrix of what is supported with the different versions is located in this document:

Replication Backward Compatibility

Sue

Admingod SSCertifiable Points: 5997 More actions · Answer 3

Thanks Sue for confirming from the replication side.What if i have to configure Always on 1 to 1 database ,would setting up between SQL 2016 as primary and SQL 2017 as secondary (if needed with backward compatibility mode) would that work?

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Mostly no but it can be yes at times. It's only supported as an upgrade process. Higher level replicas cannot be read and if you fail over to a higher version replica, the lower version stops synchronizing so it's not something you'd want to architect. You really want to go over documentation before things like this. It's all in there -

Prerequisites, Restrictions, and Recommendations for Always On availability groups

Upgrading Always On Availability Group Replica Instances

Sue