7 Mistakes You Can’t Afford To Make When Upgrading to SQL 2012

It’s new, it’s shiny, and it smells like frozen yogurt.

No, it’s not the blender you got for Christmas. It’s SQL 2012 (formerly known as codename ‘Denali’). And it’s here. And you want to use it, you know you do.

As much as you would like to believe that migrating to SQL 2012 is as easy as doing a backup and a restore, the reality is that you need to take some extra steps before letting your end users run amok in SQL 2012 goodness. The last thing you want is for one of them to get in there and report back things like:

“It’s slower than SQL 2008”, or

“When will your team be able to support SQL 2012?”

Nobody wants that to happen to you. Here are the seven mistakes you can’t afford to make.

1. Failing to use the SQL 2012 Upgrade Advisor

The SQL 2012 Upgrade Advisor (UA) is just that: an advisor. It doesn’t fix everything, it merely advises you on what actions you should take when upgrading to SQL 2012. The actions the UA recommends will come in two forms: those actions to be done prior to a migration, and those actions to be completed post-migration. The UA is really good at finding what I call the “stub-your-big-toe” things that need fixing prior to a migration. But it is not foolproof, it will not identify every last detail. You will need to play the role of an actual DBA when migrating to a new version.

2. Not reviewing the “breaking changes” section in the Books Online

Did you know that Microsoft publishes a list of breaking changes for each version of SQL Server? Well, you do now. You should review them to the point that they are familiar to you. You don’t have to memorize them all, just be familiar with them so that if something odd happens you can think to yourself “…hey, is this odd behavior listed in the breaking changes section of the BOL”? I would like to believe that the UA will alert you to many of these breaking changes but the truth is the UA is not as dynamic as the BOL. That means the BOL may have an entry or two that doesn’t make it into the UA checklist, and that is why you should review this section.

3. Skipping the “behavioral changes” section in the Books Online

Similar to the breaking changes, the behavioral changes are changes that could still affect you in an adverse way. They are definitely worth reviewing, and they are also things that the UA is likely to never report back to you about because they aren’t things that *will* break, but merely things that *could* break.

4. Forgetting to execute DBCC CHECKDB WITH DATA_PURITY

One of your 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 prior to SQL 2005 (and you *know* they are still out there), this step is rather important to take. For databases created in SQL 2005 and later, the DATA_PURITY check is supposed to be done automatically with a regular CHECKDB.

But what about a database that was created in SQL 2000, migrated (poorly) to a SQL 2008 instance, and left in the SQL 2000 (80) backward compatibility mode? What about that little feller? 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 is getting done.

5. Ignoring the execute DBCC UPDATEUSAGE command

While not as critical as the DATA_PURITY command noted previously, this one 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. And much like the DATA_PURITY check, this command is also recommended for databases that were created prior to SQL 2005. For databases created in SQL 2005 and later, you should only run this command if you feel you are getting inaccurate results from sp_spaceused, and you should note that for very large tables this command could take a long time to execute.

6. Not 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';

Bottom line here: don’t forget to update the statistics.

7. Failing to refresh your views using sp_refreshview

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. The point here is that your view may not be contained in just your database. In what could be the most dramatic twist of all, 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.

So, if you are migrating a database in your environment to a new server then it would be a good idea to refresh your views using sp_refreshview. Most of the time it won’t do anything for you, much like any movie with Stephen Baldwin. But there is that one chance where it will dramatically improve performance and your customer will be happy as a result. It’s like flossing: it doesn’t take much effort, and the end result is usually worth that little effort.

And with the Holiday Season upon us, I leave you with a Christmas Bonus: an eighth mistake you can’t afford to make:

BONUS: Forgetting to take backups

You’re a DBA. Backups are in your DNA. You should have taken one prior to the start of any migration, and you had better take one right before you turn that database over to your end users. Also, you should save any output from the seven items listed above, as it could prove helpful should something go awry later.

There you have it, the mistakes you can’t afford to make when upgrading to SQL 2012. If you haven’t started building up your SQL 2012 migration or upgrade checklist yet, now is the time, and get these seven items included. They will save you pain, I promise.