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?
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:
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 UpdatesYou’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.