January 28, 2015 at 5:40 am
Hello All,
I am planning to migrate the AlwaysON 2012 (2TB) database to SQL2014.
Option 1: Normal Backup/ Restore
Option 2: Migrating the SAN/VMware disks
Since, DBAs prefer backup/restore. Let me know your experience/opinion.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 28, 2015 at 7:43 am
Simple, setup a new replica whioch is SQL Server 2014 and introduce it into your AlwaysOn group. Add the secondary database and let it synchronise.
Once synchronised you may then fail the group over to the new primary and will be at SQL server 2014, note you wont be able to fail back and will ned to remove the sql 2012 replicas from the group (or upgrade them)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2015 at 10:08 am
Perry Whittle (1/28/2015)
Simple, setup a new replica whioch is SQL Server 2014 and introduce it into your AlwaysOn group. Add the secondary database and let it synchronise.Once synchronised you may then fail the group over to the new primary and will be at SQL server 2014, note you wont be able to fail back and will ned to remove the sql 2012 replicas from the group (or upgrade them)
Hi Perry Whittle,
Let me explain more clearly, I have a serverA/B SQL2012 and serverC/D SQL2014.
Do you mean, I need to add serverC/D into new AG group. I am not getting your point. Can you explain a bit.
Thanks for you taking your time.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 28, 2015 at 10:13 am
are you using availability groups?
if you're not then disregard my previous post and i'll explain it differently to match your scenario
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2015 at 10:21 am
Perry Whittle (1/28/2015)
are you using availability groups?if you're not then disregard my previous post and i'll explain it differently to match your scenario
Yes, I have AG group in SQL 2012 which has two replicas primary and secondary.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 28, 2015 at 10:27 am
muthukkumaran Kaliyamoorthy (1/28/2015)
Yes, I have AG group in SQL 2012 which has two replicas primary and secondary.
So if i have it correct, you have the following
ServerA and ServerB with a SQL2012 instance on each server and an AlwaysOn availability group across the 2 instances.
ServerC and ServerD with a SQL2014 instance on each.
Is this correct so far?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2015 at 10:32 am
Perry Whittle (1/28/2015)
muthukkumaran Kaliyamoorthy (1/28/2015)
Yes, I have AG group in SQL 2012 which has two replicas primary and secondary.So if i have it correct, you have the following
ServerA and ServerB with a SQL2012 instance on each server and an AlwaysOn availability group across the 2 instances.
ServerC and ServerD with a SQL2014 instance on each.
Is this correct so far?
Yes, correct. ServerC and ServerD is the new one just installed SQL2014, nothing has configured it.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 28, 2015 at 10:36 am
muthukkumaran Kaliyamoorthy (1/28/2015)
Perry Whittle (1/28/2015)
muthukkumaran Kaliyamoorthy (1/28/2015)
Yes, I have AG group in SQL 2012 which has two replicas primary and secondary.So if i have it correct, you have the following
ServerA and ServerB with a SQL2012 instance on each server and an AlwaysOn availability group across the 2 instances.
ServerC and ServerD with a SQL2014 instance on each.
Is this correct so far?
Yes, correct. ServerC and ServerD is the new one just installed SQL2014, nothing has configured it.
Ok and you want the large database from the AlwaysOn availability group on instances A and B
to be moved across to an availability group on ServerC and ServerD, correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2015 at 10:54 am
Yes, Correct. Server A & B old - SQL 2012. And need to move it to server C & D new - SQL2014.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 28, 2015 at 11:17 am
muthukkumaran Kaliyamoorthy (1/28/2015)
Yes, Correct. Server A & B old - SQL 2012. And need to move it to server C & D new - SQL2014.
So, you have two options
The first option involves joining ServerC and ServerD to the same Windows Server Failover Cluster as ServerA and ServerB.
You then restore a backup of the large database onto C and D and then join the instances to the same Availability group as A and B. Once the database has synchronised you failover the group to either C or D and the database is at SQL Server 2014.
To clean up you remove A and B from the AlwaysOn group and then if necessary remove the servers from the Windows Server Failover Cluster.
The second option involves setting up C and D in a new Windows Server Failover Cluster (which it sounds like you may have already done) and creating a cross cluster migration. There is a document provided by Microsoft on how to achieve this, you can find it at this link.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2015 at 2:15 pm
Perry's solution is a good one, but there are a few variables that need to be addressed for it to work:
Is the database you're migrating the only one in the AG?
Does the 2012 server instance host other AG's that need to remain active during the migration?
Is the database you are moving set up with a DNS listener?
January 28, 2015 at 2:21 pm
it's not mentioned the nodes are on separate physical sites so option 1 is very straight forward, its just extending the cluster and the AO group.
Option 2 however does have variables to consider, for instance you need to delete any listener that is assigned to the source group before migrating. The doc I have linked has a whole host of variables and issues to consider, not an easy task by any means.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 28, 2015 at 9:52 pm
Perry Whittle (1/28/2015)
muthukkumaran Kaliyamoorthy (1/28/2015)
Yes, Correct. Server A & B old - SQL 2012. And need to move it to server C & D new - SQL2014.So, you have two options
The first option involves joining ServerC and ServerD to the same Windows Server Failover Cluster as ServerA and ServerB.
You then restore a backup of the large database onto C and D and then join the instances to the same Availability group as A and B. Once the database has synchronised you failover the group to either C or D and the database is at SQL Server 2014.
To clean up you remove A and B from the AlwaysOn group and then if necessary remove the servers from the Windows Server Failover Cluster.
The second option involves setting up C and D in a new Windows Server Failover Cluster (which it sounds like you may have already done) and creating a cross cluster migration. There is a document provided by Microsoft on how to achieve this, you can find it at this link.
Thanks Perry for your input and assistance. I have not heard cross cluster AG migration.
I am going with option 2. Since it's already a GEO cluster 4 node in USA (6 SQL instances)+ 2 node in China (2 SQL instances).
My plan is to migrate the 2 named instance in USA from WSFC 2008+ SQL2012 to WSFC SQL2014.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 28, 2015 at 10:53 pm
Ozzmodiar (1/28/2015)
Perry's solution is a good one, but there are a few variables that need to be addressed for it to work:Is the database you're migrating the only one in the AG?
Does the 2012 server instance host other AG's that need to remain active during the migration?
Is the database you are moving set up with a DNS listener?
Yes, it has only one AG. But it's a 6 node WSFC. Node 3 & 4 has 2 pair SQL instances Default+ named. Plan is to migrate the named instances pair.
Yes, I need the same Listener and planning to create a DNS entry (Forward look up zone).
Thank you both of you. But, I am still thinking backup/restore will be a easiest one than cross cluster migration, Detach/attach & SAN migration.
Let me know your thought on this and other options.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 29, 2015 at 4:35 am
muthukkumaran Kaliyamoorthy (1/28/2015)
Since it's already a GEO cluster 4 node in USA (6 SQL instances)+ 2 node in China (2 SQL instances).
All 6 nodes part of the same WSFC?
muthukkumaran Kaliyamoorthy (1/28/2015)
My plan is to migrate the 2 named instance in USA from WSFC 2008+ SQL2012 to WSFC SQL2014.
If all nodes are part of the same WSFC i would use option 1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply