Migrating Production SQL Servers (Availability Group) from one data center to another - questions

Question

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

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

and async replica in Azure

azure server 3: SQL Server 2012 SP4 on Windows Server 2012 R2

We have a new data center (dc2) where we need to migrate production SQL availability group to

The end goal is to have two SQL Server 2017 machines 
(server 4 and server 5)  in new data center (dc2) on Windows Servers 2016, combined in AG

After successful migration to dc2 (server 4, server 1 and server 2 in dc1 are going to be abandoned, we do not need to keep server 1 and 2 as replicas in AG after production db is switched to be server 4 and 5

We have below questions:

1. if deploy server 4 and server 5 at dc2 as SQL Servers 2017, and add them to existing AG as secondaries, will they be kept synchronized with primary (server 1) without any problem, despite difference in SQL Server and Windows versions ?

2. will adding server 4 and server 5 to AG and then manual failover to server 4 to be new primary (when we are ready to switch applications to use server 4 / listener new IP as production) work fine ? And then server 4 and server 5 will be synchronizing, and server 1,2,and 3 will break and stop syncing ? Is my understanding correct ?

3. are there any better options of migrating production from dc1 to dc2 with short downtime?

4. is it OK to deploy server 4 and server 5 as virtual machines in dc2 ? Or it is better to buy dedicated physical machines ? We want a potential automatic failover from server 4 to server 5 to work perfectly in future. Can having SQL Servers virtualized introduce potential problems with automatic failover function?

Answer 1

(you have lot of sub questions as part of main question .. will get straight to answering your questions ... )

if deploy server 4 and server 5 at dc2 as SQL Servers 2017, and add them to existing AG as secondaries, will they be kept synchronized with primary (server 1) without any problem, despite difference in SQL Server and Windows versions ?

Yes, they will ... BUT you will have limit on number of replicas - synchronous with automatic failover :

SQL Server 2012 AG Limits: 1 primary replica, 4 secondary replicas, 2 of the replicas can be synchronous.

So practically, if you were to add additional replica in your current topology, you would be able to add only 1 server (2 you have in DC1, 1 cloud and 1 in DC2 (either server 4 or 5) - this will make 4 replicas which is max).

You could then pre-stage server 5 in DC2 to be logshipped. SO when you cutover to server 4, you can easily join server 5 dbs to AOAG without taking full & log backup of your databases.

will adding server 4 and server 5 to AG and then manual failover to server 4 to be new primary (when we are ready to switch applications to use server 4 / listener new IP as production) work fine ? And then server 4 and server 5 will be synchronizing, and server 1,2,and 3 will break and stop syncing ? Is my understanding correct ?

See my answer 1. Once you failover to 2017, you wont be able to switch back to 2012. Remember that it will have the same old IP if listener. You can technically add 2nd listener (as client access point) with an OR dependency of your AG on both the listeners. This way once you decom you old listener, your AG will not go offline.

are there any better options of migrating production from dc1 to dc2 with short downtime?

You should explore setting mirroring between DC1 and DC2 leaving the AlwaysON bit separate. This way, you just cutover from DC1 to DC2 and point your application to the new listener in DC2. This will be more staright forward and easy to rollback since both DC1 and DC2 are decoupled and failover and failback (point to old DC1) will be more manageable.

This is an upgrade as well (2012 --> 2017). I have written very detailed steps in my answer here.

is it OK to deploy server 4 and server 5 as virtual machines in dc2 ? Or it is better to buy dedicated physical machines ? We want a potential automatic failover from server 4 to server 5 to work perfectly in future. Can having SQL Servers virtualized introduce potential problems with automatic failover function?

I would highly suggest to be a 1-1 match. If old servers are physical, go with physical with same or more resources (CPU, RAM and Disk (match mountpoints, folder layout, etc)). Physical vs Virtual is a whole different debate with many facets and it would be unfit in the current scope of the question.

Note: If you still have questions, I would highly suggest hire a professional as you might run into issues if you do not do a dry-run end-to-end migration test.