SQL Server – Upgrade from SQL Server 2012 to 2016

Do you have an MSDN License? Or could you start a 30-day free trial with Azure? If so, you can easily set up a VM with SQL Server 2016 Standard edition installed:

This will help ensure no surprises with this or other Enterprise features.

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.