Availability Group of SQL Servers 2012, 2012 and 2016 possible?

Question

We have two SQL servers combined into Availability Group in primary data center (dc1):

dc1 server 1: SQL Server 2012 SP3 on Windows 2016
dc1 server 2: SQL Server 2012 SP3 on Windows 2012 R2

We are going to open new data center (dc2) which is going eventually to be new primary data center In dc2 there is going to be third SQL server

dc2 server 3: SQL Server 2016 (or 2012) on Windows 2016

We would like to have SQL Server 2016 on server 3, but here are my questions:

1) --> What are potential problems if we add SQL Server 2016 as a third node to existing Availability Group that has two SQL Servers 2012?

2) --> Will AG be working properly or ? If you have any practical experience of doing so, please share

3) --> Do we really need to deploy server 3 as SQL Server 2012 (instead of 2016) in order for AG to be working ?

4) --> What are best options (aside of AG) to keep SQL servers 1, 2, and 3 in sync if we chose to deploy server 3 as SQL Server 2016 ? is that transactional replication

Thanks in advance!

Answer 1

1) --> What are potential problems if we add SQL Server 2016 as a third node to existing Availability Group that has two SQL Servers 2012?

Once you failover(or failover happens) to 2016, you wont be able to sync other replicas. You should use this when you want to do upgrade with minimal downtime.

Another issue is about the new query optimizer introduced in sql server 2014 and up. I have seen some queries perform badly on the newer version. So putting 2016 as new node introduces extra risk.

2) --> Will AG be working properly or ? If you have any practical experience of doing so, please share

Above answer covers this question. A failover to 2016 will break replica sync status. Once you upgrade to higher version, the boot page gets upgraded which prevents tlog restoration of higher version to lower version.

3) --> Do we really need to deploy server 3 as SQL Server 2012 (instead of 2016) in order for AG to be working ?

Yes - if you are planning to keep all the 3 replicas in sync.

No - if you are planning to upgrade with minimal downtime.

Make sure to sync sql agent jobs, logins, etc since they are not automatically synced.

4) --> What are best options (aside of AG) to keep SQL servers 1, 2, and 3 in sync if we chose to deploy server 3 as SQL Server 2016 ? is that transactional replication

Replication is the only option available with some additional things to consider

• Replication will be hard to maintain when using with AGs.
• Depending on how you implement sync status in AGs, you will need to enable trace flag 1448
• All the tables should have PKs defined if they are needed for replication.
• You wont be able to truncate tables once you publish articles in replication.

My advise is to use sql server 2012. You can do an upgrade later by properly testing your application on sql server 2016.

Answer 2

Don't mix your versions, the databases on 2016 will be upgraded and fail-back to a 2012 won't work.

You should and go this route only if you wish to upgrade all 3 nodes to SQL 2016, eventually having everything on 2016, and no 2012/2016 mix.