January 21, 2019 at 6:42 am
Hi,
We have an old SQL Server 2008 cluster that still runs on Windows Server 2008 R2. We want to move to a SQL Server 2016 cluster running on either Windows Server 2012 R2 or 2016.
For now, we will not migrate to Always On because I think the process is a little bit more complex and we cannot afford an extended time of downtime. We will start using Always On for newer installations.
The 2 most important things to take into account while migrating the databases are:
- Failover to 2016 should be made as fast as possible, with the lowest period of downtime possible
- At any time, we do not want to lose high-availability
I have been thinking of the following steps. High-level:
1. Create a new (physical or virtual) SQL Server cluster (Cluster_B)
OS : Windows Server 2012 R2 / 2016
SQL : SQL Server 2016
2. Set up Database Mirroring between the existing SQL Server cluster (Cluster_A) and the new Cluster_B
3. The databases will be synced between Cluster_A and Cluster_B
4. (During maintenance window) Failover the active databases to Cluster_B
5. (During maintenance window) Shut down Cluster_A
6. (During maintenance window) Make the required network changes
This can either be a DNS change (CNAME e.g.), but it is also possible to rename Cluster_B to Cluster_A
Only Step 4, 5 and 6 should introduce some downtime.
Would this be the best or most realistic approach? Or am I missing something?
January 22, 2019 at 12:29 pm
What's the number of databases and their size?
January 22, 2019 at 2:01 pm
Personally would pick Windows 2016 given the choice of OS’s. Windows 2012 is already out of mainstream support.
On top of Alex’s question, how many apps are you talking about with the number of databases?
It is just a single SQL instance cluster?
What’s your maintenance window?
Are the databases in simple or full recovery model?
AG’s are out of the question anyway being the source is 2008 as you have put. You still have log shipping if the databases are in full recovery mode. Database mirroring may just be a bit over the top depending what you really want. I am guessing you don’t want to alter the application connection strings to support mirroring and you don’t want to setup a mirrored SQL instance... are you doing more work then required?
I’ve seen some fancy things with disk snapshots but had issues with the disk signatures when moving disks between clusters, though that example was multi instance.
CNAME’s can be interesting as well, again you would be looking at potentially altering application connection strings depending how you wanted to plan for now and the future. Word of warning is that you will need to register the SPN’s in the CNAME manually, so would be best to fix the ports being used by the SQL cluster.
January 24, 2019 at 3:45 am
Fozzie - Tuesday, January 22, 2019 2:01 PMPersonally would pick Windows 2016 given the choice of OS’s. Windows 2012 is already out of mainstream support.On top of Alex’s question, how many apps are you talking about with the number of databases?It is just a single SQL instance cluster?What’s your maintenance window?Are the databases in simple or full recovery model?AG’s are out of the question anyway being the source is 2008 as you have put. You still have log shipping if the databases are in full recovery mode. Database mirroring may just be a bit over the top depending what you really want. I am guessing you don’t want to alter the application connection strings to support mirroring and you don’t want to setup a mirrored SQL instance... are you doing more work then required?I’ve seen some fancy things with disk snapshots but had issues with the disk signatures when moving disks between clusters, though that example was multi instance.CNAME’s can be interesting as well, again you would be looking at potentially altering application connection strings depending how you wanted to plan for now and the future. Word of warning is that you will need to register the SPN’s in the CNAME manually, so would be best to fix the ports being used by the SQL cluster.
I recommend Windows Server 2016, as you say.
We are talking about 5 clusters to move, I do not yet have numbers on the amount of databases. I believe most clusters are on just 1 default instance.
The risk of reassigning disks is too high, we want the old cluster to be available until after the move also, so that we can fallback immediately in case of issues.
Log shipping is not supported in combination with Clustering, Database Mirroring is the only one that supports the above scenario:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb500117(v=sql.105)
January 24, 2019 at 3:55 am
gert.lievens - Thursday, January 24, 2019 3:45 AMFozzie - Tuesday, January 22, 2019 2:01 PM......
Log shipping is not supported in combination with Clustering, Database Mirroring is the only one that supports the above scenario:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/bb500117(v=sql.105)
Had another look and seems that it does not literally state that Log Shipping is not possible with Clustering.
I still prefer Database Mirroring as I found the failover part to be easier to maintain and I have more experience with DBM.
January 24, 2019 at 8:02 am
Log shipping is available with a standard failover cluster, one can even roll your own if you really wanted - all it’s doing is applying the transaction logs in no recovery or with standby dependant on how you set it up. But your databases would need to be in full recovery to begin with, which I am guessing is this case given the database mirroring aspects.
However database mirroring willl be a one way affair. Forget what I said about a witness, you won’t need or want one. You would be running in high safety mode.
Once you flip over, I don’t think you will be able to flip back. The compatibility of the database will be internally upgraded even though it will have a compatibility level of 100. So if you needed to back out you would be looking at restoring the databases on the old server from backups.
If you have dev servers to play with, maybe try and put the mirroring idea to the test. Always nice to have somewhere to try out different ideas and test out opinions from online.
January 24, 2019 at 8:06 am
Also on the disk front.. I wasn’t talking about moving redials. More of snapping them and presenting as copies. Something that is available in the Pure SAN system we use at work.
January 31, 2019 at 3:40 am
Fozzie - Thursday, January 24, 2019 8:02 AMLog shipping is available with a standard failover cluster, one can even roll your own if you really wanted - all it’s doing is applying the transaction logs in no recovery or with standby dependant on how you set it up. But your databases would need to be in full recovery to begin with, which I am guessing is this case given the database mirroring aspects.However database mirroring willl be a one way affair. Forget what I said about a witness, you won’t need or want one. You would be running in high safety mode.Once you flip over, I don’t think you will be able to flip back. The compatibility of the database will be internally upgraded even though it will have a compatibility level of 100. So if you needed to back out you would be looking at restoring the databases on the old server from backups.https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/upgrading-mirrored-instances?view=sql-server-2014If you have dev servers to play with, maybe try and put the mirroring idea to the test. Always nice to have somewhere to try out different ideas and test out opinions from online.
Thanks, this was really useful, especially about the internal upgrade which I did not take into account. Log Shipping is also something we can absolutely try out in our lab environment.
Got full admin access on the SQL server cluster instances, there are about 5 clusters still to do. Some instances have up to 50 databases and the database sizes range from a couple of GB's to over 300 GB for others. Also, apparently most databases are still in Simple recovery model.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply