Sql-server – Migrating a cluster from SQL Server 2005 to SQL Server 2016

clusteringmigrationreplicationsql server

I am in the process of migrating a SQL Server Cluster. I would like to hear considerations for a successful migration.

The specs of production cluster are:

  1. Windows Server 2003 SP2 32bit.
  2. SQL Server 2005 Enterprise edition – 9.00.3152.00 (Build 3790: Service Pack 2)
  3. Disk space is about 1.5TB including all databases. (IBM v7000)

The specs of destination cluster are:

  1. Windows Server 2016 Standard 64 Bits
  2. SQL Server 2016 Standard 64 Bits
  3. Same disk as old cluster, but faster (IBM v9000 Flash system)

Special Considerations:

  • Cluster Name must be the same to avoid changes in applications, linked servers and other connections. I mean, we have to migrate with a different cluster name, then shut down old cluster and rename the new one using the old name (I hope this to be clear)

At this time what I have done and tested is the following:

  1. Create and configure a Windows Failover Cluster using Windows Server 2016 (with a different name)
  2. Configure Active Directory to allow cluster to create objects
  3. Restoring a database from MSSQL 2005 to MSSQL 2016, run some queries and explore objects.

Also:

  • I have split the drives following the best practices.
  • We are cleaning "super users" and giving just the right permissions to users.
  • Setting and testing compatibility mode to 130
  • Backup strategy was defined about 1 week ago to ensure data availability

Things to do or consider:

  • Download all SSIS packages to new project and test them against the new cluster. Once the cluster name changes, change SSIS to original name
  • Migrate all Jobs and make sure they run successfully
  • Test application that uses the migrated database
  • Create ODBC connections for Linked Servers pointing to SQL Server 2000 Databases (yes, we still use MSSQL 2000)
  • Backup Active Directory to have a rollback point in case something fail regarding cluster names (we have to delete old object and create a new one)
  • Configure SQL Replication.

Best Answer

This is one of those questions that I love to see. There are a lot of things to add to do to add to migration and upgrade successes and it warms the hear to see the question.

You've already covered a lot of things to consider and I'll add a few thoughts but I am looking forward to seeing other answers with more ideas from others.

I'm not including things you've already included in the great list you have started and knowing me I'll be editing and adding to this answer as I go.

**One disagreement I may have with your approach: Regarding the cluster network name, why not make a new cluster name and just use CNAME to point connections to the old name to the new instance? This lets you make a new name, avoid messing with conflict potential, makes go live night smoother and gives you a clearer and easier rollback. I would do a CNAME approach and change the CNAME once you are sure the old can be taken offline. I may be alone here so I welcome comments supporting that idea or disagreeing.

I see a few areas of focus when it comes to migrating. If you make an "outline" of the phases and outline of the areas to focus on comes to mind: Planning, Pre-Migration Steps, Day of Migration, Post Migration. I'll cover some of those sections here and I am hoping others join in with ideas.

Planning Thoughts

Since you are starting with new hardware. This is a great chance to make sure you are considering all of the best practices that may have been overlooked before. Things like:

  1. Split out your drives. At the very least I'd love to see separate drives for (Your Mileage May Vary here also, really depends on performance profile of your setup):

    • Operating System (C drive)
    • SQL Server binaries and maybe system DBs excluding TempDB
    • User Data Files
    • User Log Files
    • TempDB data and Log Files
    • Local backup and/or misc drive for file imports, staging of data, etc

  2. When formatting your drives for Data files and TempDB specifically, consider doing the 64KB allocation unit size. (Source)

  3. Now is your chance to maybe clean up some of those security holes and flaws that could exist. Look to build with best practices and least access.

Before Migration

Lots of things to do before the migration. Some that come to mind immediately are:

  1. Run the upgrade advisor. SQL Server 2016 made some improvements there and it is a separate download. Look to see what issues may exist.
  2. Do a more thorough test of your DB to see what breaks and what works. Do a test migration and put into 2016 (130) compatibility mode. And do thorough testing. If you don't have a test approach you can look at something like profiling activity and replaying to see what breaks. Test. Test. Test.
  3. Grab the SQL Server and Windows Server 2016 documents about clustering and failover clustering. Look to the checklists and prerequisites carefully.
  4. Ensure you have good transaction log management - why migrate an excessively large LDF file due to poor management.
  5. Make sure you are doing maintenance properly on your databases - especially make sure you are running regular DBCC CHECKDBs - this is a good thing to do before migration or really as part of final migration, but you don't want the first time you do one to be on migration night and find surprises.
  6. Determine success criteria and have a rollback plan (doing a migration to new is great, you can simply rollback any CNAME changes, connection strings and point back to old) in case of issues. And know what issues constitute a rollback and what issues you'll "stay and play" with post migration. Get buy in for this list.
  7. Have a really good backup strategy. Know you can restore. Know where your backup files are, etc.
  8. Look to all server objects. You covered a lot. But don't forget credentials, proxy accounts, alerts, maintenance jobs, SP_Configure settings, etc. A GREAT tool for you to look at that can actually help you remember things is the PowerShell DBATools scripts. These scripts can help you migrate all of these items - including SQL authenticated logins with the correct SIDs for your databases, but they also can help you remember what to migrate when you look through all of the modules. You can look into the scripts here.

Immediately After Migration

Lots more here. And a category of "On migration week" could be added and maybe someone will or I will edit and add later.

Some things here that come to mind straight away:

  1. Have a smoke test / check to verify all came over well.
  2. Change compatibility mode to SQL Server 2016 on all databases if testing proved that out to work. I see this getting missed a lot.
  3. Make sure maintenance and backups are in place. Run a post migration DBCC CHECKDB on the databases.
  4. Make sure your jobs are running.
  5. Configure monitoring and alerting so you don't miss alerts about this server
  6. Once you are satisfied with a limited test, turn the old off and make a CNAME change.
Related Question