Blog

It’s finally time– you’ve been given the thumbs up to upgrade to SQL Server 2012. You’ve selected your SQL Server Edition and worked out your licensing agreement. You’ve sized new hardware in production and sketched out your migration plan. You’re ready to install developer edition in the test environment. You grab your installation media and…

Suddenly you realize you’re not quite ready. You need to make sure that all testing goes against the same version of SQL Server you’re going to go live with. But which version of SQL Server do you choose?

What’s the SQL Server Version?

I turned my SQL Server Version up to 11 … 11.0.3000.0, to be exact.

The SQL Server “version” is often called the “patch level” of SQL Server. When a new SQL Server version is released you get the “RTM” version of the product. Over time, lots of fixes are released for each version: Service Packs, Cumulative Updates, and hotfixes.

You can find your current SQL Server version by going into your Server Properties in Management studio, or running the TSQL command:

SELECT @@VERSION;

To decode your version number, check out this big list of SQL Server Builds.

Identify SQL Server Service Packs

First, check if a service pack is avaiable for your version of SQL Server, and which one is the latest. SQL Server 2012 Service Pack 1 was released in November 2012.

Service Packs contain important fixes for a product and are tested even more rigorously than incremental releases like cumulative updates, so they’re pretty important to install. But like everything else, they may contain something that has unexpected side effects– so you need to test your application with the service pack before you change it in production.

Sometimes new features get snuck into Service Packs these days — check out what features were added in SQL Server 2012 SP1.

If you forgot to install the service pack at the beginning of testing, you may wonder if it’s worth it to go back and install the service pack and re-test. If you have any of these doubts, read the list of bugs the Service Pack fixes– but note that you’ll also have to read the release notes for all the cumulative update packages it includes as well.

Cumulative Updates

Upgrading ain’t easy, but sometimes it’s REALLY worth it
[Photo credit: essarah]

You’ve found your service pack, so you’re good, right? Not so fast.

You also need to consider the Cumulative Updates that Microsoft releases for SQL Server. These updates are more frequent than service packs, and technically Microsoft recommends that you only install them if you are impacted in an issue that the Cumulative Update corrects. However, there are many people who prefer to test and go live on their chosen version of SQL Server + Latest Service Pack + Latest Cumulative Update– because these updates do fix some doozies.

My favorite resources for cumulative updates is the SQL Server Release Services Blog. I subscribe to this by RSS, and anytime I need to do research on cumulative updates, I head to their blog and use their posts to find all the related links for documentation.

For SQL Server 2012, you’ll find that Cumulative Update 1 has been released post SP1, so you can consider the version SQL Server 2012 SP1 CU1. This CU1 has some pretty critical sounding fixes in it, including out of memory errors, incorrect results on parallel queries, and databases offline if an index rebuild is performed when the transaction log is full. That sounds pretty compelling, right?

Don’t get too comfortable, there’s even more to consider.

Critical Hotfixes – And a New Way to Find ‘Em!

Microsoft works hard to release fixes regularly– hence those Cumulative Updates. But they still need to release out of band hotfixes sometimes for big issues, and it can take little time for those critical issues to be handled in a cumulative update.

This is the trickiest part of selecting a version, really, because you need to be able to find if there are critical hotfixes that might impact you, and what to do about them. There typically hasn’t been an easy way to find out about these. Even for people like us who work on and talk about SQL Server every single day, we’ve been pretty dependent on word of mouth in the community to find out about things.

Good news, Microsoft has recently created some new RSS feeds for KB articles that you can use to research and stay informed of new hotfixes for Windows and SQL Server! (We’re talking about SQL Server versions in this post, but guess what? The same stuff applies to Windows version. File that one under ‘job security’.)

Make sure to research your choices well, because you can make the wrong choice.

Hard Choices: When Service Packs and Critical Updates Collide

Here’s an example of a sticky situation.

SQL Server 2012 SP1 has a nasty little bug that only happens sometimes. The symptoms are that after the service pack is installed, processes named msiexec.exe keep running and use up lots and lots of CPU– up to 100%. This persists after rebooting the instance.

This doesn’t happen every time that you install the service pack. But when it happens, it can be very tricky to resolve it– for evidence of that, just read the notes in the Microsoft Connect item on the issue.

Microsoft responded to the problem and released a hotfix for the issue– KB2793634. That’s great, but if you read carefully and check that Connect item, you’ll find that this hotfix should only be installed if you experience the issue and have NOT installed Cumulative Update 1 for Service Pack 1. A hotfix is planned to be released later that is valid for that situation.

So, at this point, you have multiple options:

  • If you’re really compelled by the fixes in SQL Server 2012 Service Pack 1 + Cumulative Update 1, you could install them all everywhere and gamble that you won’t get hit by the msiexec.exe bug in production. If you even consider this one, you need to be prepared with the steps you’re going to take to monitor and make sure you know if the issue is occurring, and how you’re going to respond if it happens.
  • If you’d like to go the safer route, you may choose to install only SQL Server 2012 + Service Pack 1. You still want to know the steps you’re going to take to monitor for the msiexec.exe bug and how you’re going to respond if it happens, but you have more options. In this case you’ll be able to use the published hotfix if needed. (By the way, it’s not always straightforward– read the user reports in the Connect item.)
  • If you’re scared off by this entirely, you may choose to install SQL Server 2012 RTM version and skip all updates. That may seem like the safest option, but research all the fixes that were made in the cumulative updates and service packs– you could burn yourself with this choice as well.

Update (Feb 16, 2013) – Potential Software Registry Bloat from SQL Server 2012 Sp1

There’s more you should know about the potential problems introduced by SQL Server 2012 SP1. If you hit the issue with the msiexec.exe installer process running post-installation (which does not happen all the time), you may experience growth of your software registry hive over time.

Symptoms include server level instability — and yes, those three words are terrifying! Learn more about how to diagnose if you have the issue and repair it from Remus Rusanu.

There’s Often No “Perfect” Choice

Many factors go into a version selection– what your vendor supports, the release/change frequency your organization allows, your business’ tolerance of risk, and the amount of testing and validation you can do are all critical factors. Not to mention your upgrade surface and whether your high availability options could be impacted by the change! (If you’re like me, one of the last things you ever want to risk is getting stuck doctoring up a Windows Registry in a critical downtime situation.)

But no matter how perfect your change process, you’ll always need to keep current on fixes as they are released AND do research to find the bugs which may impact your choice. Things change fast!

Don’t have time to do all the research, or want some expert advice? Drop us a line– we often help clients figure out the right path to take their environment to the next level.

↑ Back to top
  1. How can I “downgrade” SQL Developer Edition so it will only support standard-edition features?

    • Michal – unfortunately, you can’t. You could use Policy-Based Management to proactively stop users from creating Enterprise-Edition-specific features like certain kinds of indexes, but that’s about it.

  2. Kendra, you didn’t mention CU5 as a viable option… Any reason for this we should be aware of, please?

    • Oh, yes– you could go with RTM + any of the cumulative updates, including CU5 at this point.

      You just have the risk there of issues potentially introduced by cumulative updates, which technically are recommended only by Microsoft if they’re fixing a specific issues. (Service packs are more generally recommended by them since they receive more testing– but we just know about one specific issue this service pack introduces one issue that’s impacted a lot of people.)

      I don’t know for sure that the MSIEXEC issue is specific to the service pack and couldn’t happen with CU5. It’s certainly possible, I just haven’t found any confirmation so far.

      So a complicated decision all around. But yes, cumulative updates 1-5 are all options.

  3. I installed the KB2793634 on a test Windows 2012 with SQL 2012 SP1 Hyper-V VM and it helped out a lot. After installing SP1 I noticed a performance deterioration, so I gave the VM more RAM but it was still slow. The msiexec process just kept running multiple instances of itself and cranking the CPU. In my case, having a poor man’s VM helped me notice the performance problems immediately.

    I’m considering waiting for SQL 2012 SP2 before putting it into production; especially after reading this.

  4. Luckily, SQL Server 2012 SP1 CU2 is now available, which contains the fix from KB2793634.
    http://support.microsoft.com/kb/2790947

    • Wim – something interesting about SP1 CU2 is that it makes the above discussion even more complex! SP1 CU2 introduces new features and new syntax for backups. So now, it’s possible that you can have two SQL Server 2012 instances – one where backups work, and one where they don’t! Funny.

    • Just added an update to the post. Anyone who had the issue might have had a side effect of bloating of the software registry hive– and I don’t believe any of the fixes that correct the msiexec.exe installer issue itself ALSO correct the registry hive.

      There is a way to check your software registry hive size and confirm if you have been impacted. I have updated this in the blog post.

      • Hi, as an effect of the SP1 bug, one of our early SQL 2012 systems needed the VM rebuilt and SQL reinstalled due to registry bloat. It manifested itself initally as an error loading user profiles. MS support tried a number of things to remedy the situation before suggesting a complete rebuild (there was one other option left to bring back the old software hive from the regback folder but it was over a month old). It took around 1 month for the bug to kill our cluster. MS recommended CU3 should be applied following installation (I believe the bug was fixed initially in CU2).

  5. Nice discussion of the subject, Kendra.

    Another good way to find out abou new Cumulative Updates is put a reminder in your favorite calendar program for seven-eight weeks after the last one for that SQL Server version was released. Microsoft is not super reliable about immediately announcing and publicizing CU releases on the Release Services blog.

    I also like to periodically check their KB articles that list all the CU builds for each RTM and SP branch, such as the example below:

    The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released
    http://support.microsoft.com/kb/2772858

    They use the same naming standard for the titles of those “build list” KB articles, so they are easy to find.

  6. The SP1 bug is quite unpleasant. Thought I was losing my mind until I found that Connect article (and that was before they released the hotfix). I had to fix it on one server, if I recall, by uninstalling the management tools and reinstalling them from media (basically having RTM level management tools and SP1 level everything else).

  7. Nic Neufeld:

    Has that fix been reliable for you so far? Uninstalling Management Tools and reinstalling them from the RTM media?

    • James,

      Not answering for Nic, but I have heard from one other person anecdotally that this resolved their issue, and there is are a couple of related comments on the Connect item. I can’t confirm that it always works, or if it is a permanent fix, however. It would not resolve the registry bloat issue if that has occurred– you would need to take separate steps to address that.

  8. Personally, I wouldn’t upgrade that black Ferrari :)

  9. Do you know if the msiexec issue only occurs if you install SP1 after the RTM install, or can it also happen if you run the install that includes both RTM and SP1? We are seeing the issue on an install done with the former but not the latter.

    • Tom – if you’re not seeing the issue, I wouldn’t worry about it. If you’re getting ready to do the install, just plan on doing the cumulative update immediately afterwards.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php