Skip to main content
deleted 3 characters in body
Source Link
Kin Shah
  • 62.4k
  • 6
  • 122
  • 240

(you have lot of subquestions queuessub 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.

(you have lot of subquestions queues 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.

(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.

Source Link
Kin Shah
  • 62.4k
  • 6
  • 122
  • 240

(you have lot of subquestions queues 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.