MORE ABOUT ME

26 Mar Upgrading To SQL 2012: Ten Things You Don’t Want To Miss

SQL-Server-2012Many novice administrators make the common mistake of believing that the upgrade process for a database server is as easy as pressing a few buttons. While you can certainly click “Next, Next, Finish” and consider your task to be complete the truth is that the upgrade process is often considerably more complex. A proper upgrade process involves detailed research, planning, and execution.

Failing to prepare a proper upgrade process for your database server is likely to result in your end users seeing diminished performance after the upgrade is complete. Since your goal is to increase performance and stability as a result of the upgrade process you can understand that your users are likely to be upset if things were to get worse!

It can be a daunting task to put together everything you need in a pre-upgrade checklist. I’ve compiled the top ten items that you need to include in any checklist you put together for migrating your database server to SQL Server 2012. Including these ten items is likely to help you avoid 95% of any potential upgrade issues.

Please note that these steps are specific for an upgrade to the database. They do not include anything regarding the upgrading or testing of an application that is going to be accessing the upgraded database. You will want to remember to test your application and not just assume it will work perfectly even after the database has been upgraded. I would also advise that you perform these steps in a non-production environment because that I often find that common sense isn’t so common after all.

1. Using 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. 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. Reviewing 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. Executing 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. Executing 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. 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 after an upgrade. Failure to do so could result in your queries running slowly as you start your testing and may end up wasting your time while you try to troubleshoot the possible bottlenecks. So, take care of the stats now, and you don’t have to worry about it later.

7. Refreshing 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.

8. Taking 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. (bonus item – make sure your backups are good!)

9. Upgrading your hardware

SQL Server 2012 does not support AWE. That means if you still have a 32-bit operating system and are currently using AWE in order to see beyond 4GB of RAM, if you upgrade to SQL 2012 you will be limited to *only* that 4GB of physical RAM.

It’s time for you to move to a server and operating system that was released within the past ten years. If you are expecting to have more than 4GB of RAM then you need to use the 64-bit version of SQL 2012.

10. Knowing the right upgrade path

For those folks running SQL 2000 instances you are not going to be able to upgrade directly to SQL 2012 without first upgrading to an intermediary version. You have two options to choose from when going from SQL 2000. The first option is to do an upgrade in place from SQL 2000 to SQL 2005, 2008, or 2008 R2. The second option is to do a backup (or even detach) your SQL 2000 database and restore/attach to an instance running SQL 2005, 2008, or 2008 R2. At that point you will be able to complete the upgrade to SQL 2012.

Conclusion

Upgrades are a necessary part of any development lifecycle. The chances of having a successful upgrade increases along with the amount of planning and preparation you invest in building a proper upgrade process. If you are planning to upgrade to SQL 2012 you can use this post as a guide to help put together your checklist.

If you haven’t started building up your SQL 2012 migration or upgrade checklist yet, now is the time, and get these ten items included. They will save you pain, I promise.

5 Pingbacks/Trackbacks

  • Pingback: Upgrading To SQL 2012: Ten Things You Don’t Want To Miss - SQL Server Blog - SQL Server - Telligent()

  • http://www.straightpathsql.com Mike Walsh

    Great post, Tom. If you can do a migration instead of an in place it is the safer approach, too :-) I blabbered on about that in an answer on dba.stackexchange recently – http://dba.stackexchange.com/questions/29328/are-sql-server-in-place-upgrades-as-ill-advised-as-they-used-to-be

    Nice post again! Some things that often get overlooked in there, too.

    • ThomasLaRock

      Mike,

      Thanks for reading. I always prefer to migrate my data to newer hardware/software as opposed to the in-place upgrade. That’s not always an option for everyone, however.

      Nice answer to that question, too. I just gave you another upvote there.

      • http://www.straightpathsql.com Mike Walsh

        Great point Tom. I always have the big discussion with clients who want to do in-place. Sometimes I can convince them, sometimes I can’t – but I try :-) Either way – the tips in here are invaluable. Going to add this post as an edit to my answer here eventually. And thanks for the upvote – I won’t admit if I was just pandering for votes or trying to be helpful. :-)

  • http://twitter.com/cesarlemus Toño

    Thank you very much Thomas, ¿is it recommended using this list into a migration process too or only into an upgrade process?

    • ThomasLaRock

      This list is meant to complement either strategy. Ultimately your data will go from an earlier engine to the latest version, and these items are all things you should consider regardless of how you got your data into the latest version.

      • http://twitter.com/cesarlemus Toño

        Thank you, I’ll consider this list because in a few month I have to magrate from SQL Server 2005 to SQL server 2012.
        !! Great post !!

  • http://twitter.com/thesqlpro Ayman El-Ghazali
    • ThomasLaRock

      Yes, yes we could.

  • Pingback: Something for the Weekend - SQL Server Links 29/03/13 • John Sansom()

  • MindQ

    Thomaala Rockss.is known to be a reputed consulting firm for SQL SERVER related

    Courses with top talented database professionals and software engineers

    coming from some of the most established and most successful software developmentand

    technology consulting firms around the globe for providing a tailored-made solutions to

    their clients at the very best of their knowledge and liable time.

    http://www.sqlservermasters.com/

  • spdesigns

    Great post! Very helpful as I’m preparing for migration from 2005 to 2012

  • hellomahesh

    SQL Server 2012 Upgrade Advisor doesn’t report deprecated features. Any other workaround tool apart SSDT compilation?

    • ThomasLaRock

      I think SSDT is meant to perform that function. The only other thing I can think of is to run a trace against a workload and try to capture any warnings for deprecated features.

      • hellomahesh

        The problem with SSDT is I have tens of DBs on the server and I have to create and import each of them into DB projects and compile. It is a cumbersome task for me..

  • Jason Pociask

    Why does everyone want to dump on Stephen Baldwin? He’s a nice guy, I’ve met him.

    • ThomasLaRock

      I’m sure he’s a nice guy, and there’s probably a better option I could have used for that analogy.

      • Jason Pociask

        No problem, keep up the good work!

  • Pingback: and continues with upgrade paths… | Byte Size BI()

  • Pingback: Upgrading to SQL Server 2014: A Dozen Things to Check - SQL Server - SQL Server - Toad World()

  • Pingback: Upgrading to SQL Server 2014: A Dozen Things to Check - SQLRockstar - Thomas LaRock()