Upgrading to SQL Server 2016, Part Three

If you’ve been following this series, you know that in part one, I addressed reasons for upgrading to Microsoft SQL Server 2016, as well as some pre-upgrade tasks to be mindful of, and in part two, I focused on tasks that should take place during the upgrade. Now, in part three—the final installment—we’ll cover the post-upgrade tasks.

After the Upgrade

After your upgrade to SQL Server 2016 is complete, you’ll need to perform a series of tasks to verify that the database is ready to be handed over to end-users for further testing. Let’s explore these in-depth:

Create Backups

That’s right—create backups. Right now. Before you do anything else. After all, you’re a DBA and backups should be in your DNA. You should have taken one prior to the start of any upgrade or migration, and you had better take one right now, and again before you turn the database over to end-users. Also, you should save any output from the items listed here, as doing so could prove helpful should something go awry later. And don’t forget to make sure your backups are good!

DBCC CHECKDB

Another post-migration or upgrade tasks should be to run the following statement:

DBCC CHECKDB WITH DATA_PURITY;

This statement will check your data for values that are no longer valid for the column datatype. For databases created in SQL Server 2005 and later, the DATA_PURITY check is supposed to be done automatically with a regular CHECKDB, but that’s not the case for databases created prior to SQL Server 2005 (and you know they’re still out there), so this step becomes even more important.

But what about a database that was created in SQL Server 2000, migrated (poorly) to a SQL Server 2008 instance, and left in the SQL Server 2000 (80) backward compatibility mode? Do you want to assume that the DATA_PURITY check has been getting done? Here’s a thought: just go run it yourself anyway. That way you know it’s getting done.

Also worth noting here is that column integrity checks are not performed when the PHYSICAL_ONLY option is used.

DBCC UPDATEUSAGE

While not as critical as the DATA_PURITY command, there’s another one that still has a place in any migration or upgrade process:

DBCC UPDATEUSAGE(db_name);

This command will help fix any page count inaccuracies that are resulting in the sp_spaceused stored procedure returning wrong results. The best time to run this command is now, but be aware that it can take some time to run depending on table or database size. Ideally, you would run this on a regular basis for one of the following reasons:

Updating Statistics

This one is not to be skipped and is simply a must for any migration or upgrade checklist:

USE db_name; GO EXEC sp_updatestats;

This command will update the statistics for all the tables in your database. It issues the UPDATE STATISTICS command, which warrants mentioning because you may want to use that command with the FULLSCAN option. I’m the type of person that would rather be safe than sorry and therefore would end up running something like this:

USE db_name; GO EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

The bottom line is don’t forget to update the statistics after an upgrade. Failure to do so could result in your queries running slowly as you start your testing and you may end up wasting your time while you try to troubleshoot the possible bottlenecks. With SQL Server 2016, there is also a new Cardinality Estimator (CE). Since the query optimizer relies on accurate statistics for plan estimation purposes, you will want your statistics as accurate as possible before you begin any testing. Take care of the statistics now and you won’t have to worry about them later.

Refresh View Definitions

Believe it or not, every now and then someone will build a view that spans into another database on the same instance. And, in what may be a complete surprise to many, sometimes these views will go across a linked server as well. In what could be the most dramatic plot twist of all time, sometimes these views are created using a SELECT * syntax.

I know, I know—what are the odds that you could have such code in your shop? But it happens. And when you have bad code on top of views that go to other databases (or views of views of views of whatever else some sadistic person built), you are going to want to use sp_refreshview to refresh those views.

Check Compatibility Levels

If you’ve done SQL Server upgrades previously, then you have likely noticed the compatibility level does not get set to the newest version after the migration is complete. You need to manually set the compatibility level yourself. With SQL Server 2016, this becomes more important than in previous versions due to the new CE.

I recommend you update every database on the SQL Server 2016 instance to compatibility mode 130 and then test, test, and test. (This assumes that you have baselined performance for your critical queries prior to the migration so you can verify if the new CE is working for or against you.)

Verify Counts of Objects

Remember the counts of objects such as tables and stored procedures that you took before? Now is when you want to review those counts. Make sure you have the same number of objects you started with prior to the upgrade and migration. Remember the SQL Server upgrade motto: no table left behind!

Check Configurations

As part of the pre-upgrade tasks, you should have collected details on the in-house and third-party vendor applications using the database server. You also should have collected information about the specific configurations applied to the server OS, database instance, and the database itself. Now is the time for you to review those details and make sure the configurations have been applied to the new server.

In Closing

Upgrades are a necessary part of any development lifecycle. The chances of having a successful upgrade increase proportionally with the amount of planning and preparation you invest in building a proper upgrade process. Whether you’re already planning your upgrade to SQL Server 2016 or have yet to do so (by the way, now is the time), you can use this series as a guide to help put together a proper checklist of pre-upgrade, during upgrade, and post-upgrade tasks. Doing so will save you a lot of blood, sweat, and tears. I promise.