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:
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
[Photo credit: essarah]
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.
39 Comments. Leave new
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.
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.
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.
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.
Brent – Indeed, I noticed this as well. This CU has such a SP 1.5-feeling about it, with so many fixes AND new functionality. Has this ever been done before?
Has anyone tested the Azure backup syntax against Amazon S3 yet?
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).
Ouch! That sounds painful. Thanks for sharing the story.
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.
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).
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.
Personally, I wouldn’t upgrade that black Ferrari 🙂
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.
I’m currently on SQL 2012 SP1+CU3 , if I install SP2 + CU1 kb2976982 could I still install any of the CU4-CU11 for SP1 or are those included in SP2!
Thanks
Hey there,
“Microsoft SQL Server 2012 service packs are cumulative updates and upgrade all editions and service levels of SQL Server 2012 to SP2. This service pack contains up to and including SQL Server 2012 Service Pack1 Cumulative Update 9 (CU9). ”
This little bit of magic is published in the Details section of the Service Pack itself– you can always see which CUs it includes.
For SP2, at this point you should be downloading “Microsoft SQL Server 2012 SP2 Update”, which fixes the data corruption issues in KB2969896.
But of course it still does not have anything past the CU cutoff listed.
Hey Kendra,
Thanks for the reply but I’m a bit confused now … you say everything through SP1 CU9! What about these two fellas CU10 kb2954099 and CU11 kb2975396 … are they considered the step kids? 🙂
Let me know
You know how some people say you can have it all? Yeah, that’s totally not true! At least, not all at once.
Think about it this way: you’re Microsoft. You need to ship a service pack, and you’ve got to test it. You’ve also got to keep shipping cumulative updates. So at a certain point you branch off (that happened here after CU9) and go down your Service Pack branch.
As time goes on, they release more CUs that effectively “catch up”. As of this moment SP2 CU1 is out, which is effectively CU10. http://blogs.msdn.com/b/sqlreleaseservices/archive/2014/07/23/cumulative-update-1-for-sql-server-2012-sp2.aspx
Do you want to apply that? Well, it’s 18 days old. You probably want to test the heck out of it outside of production if you do want to use it– and like anything else, read what fixes it includes to see if it’s worth it to you.
This Microsoft you speak of and their “catching up” games never gets old doesn’t it. Thanks for explaining the process now I fully understand how this works.
ps. Looking forward to SQL 2016
If I’m doing a new SQL 2012 install, what’s the best version to install in terms of trying to be the most up-do-date, yet with few or no pitfalls (a-la SP1 cpu bug)? In other words, from what you’ve seen, is SP2 stable, with no pitfalls? Is there enough experience with SP2 CU3 to say that it’s solid? If not, how about CU2? Thanks for your insight!
I see this hotfix for SP2, so I gather SP2 shouldn’t be the destination, but at least SP2 CU1?
2969896 FIX: Data loss in clustered index occurs when you run online build index in SQL Server 2012 (Hotfix for SQL2012 SP2)
If you’re using Enterprise Edition and do parallel online index rebuilds, yep. I would also review the fixes in the CUs after CU1 and see if they contain items that might impact you. I always go here to find the articles easily: http://blogs.msdn.com/b/sqlreleaseservices/
I feel your pain.
I don’t know of any major outstanding bugs with the very latest service packs + CUs at this time (for 2012 it’s SP2+CU3 right now), but you still need to test the version you pick thoroughly for your application.
(Edit: added a plus to try to say SP+ latest CU)
We are currently running our Database Server on a SQL Server 2012 RTM version:
Current Version:
Microsoft SQL Server 2012 – 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)
We need to install the latest Service Pack for SQL Server 2012, which is SP3
I had the following doubts based on this subject:
1. Will installation of SQL Server 2012 SP3 affect any of our production databases?
2. Do we need to restore the databases, after installation of SP3?
3. Are there any particular prerequisites we need to follow before the installation of SP3?
4. Do we need to perform a database backup and backup of the data files before the installation of SP3?
5. Does SP3 has any known bugs? Should any Cumulative Update be followed after SP3?
6. Can I directly jump on to install SP3, instead of installing SP1 or SP2?
Please advise. Appreciate your help on this. Thanks.
Nikhil – as I asked you to do in email, please post random questions over at http://dba.stackexchange.com, not in unrelated blog post comments.
Hi Brent
I am unable to select instance name in feature selection page while doing SQL Server 2008R2 service pack 3 installation ..Please advise.
Kamesh – for unrelated questions, head over to http://dba.stackexchange.com.
hi, we are on MS sql server (Microsoft SQL Server 2012 (SP1) – 11.0.3393.0 (X64) and wanted to check if we can directly upgrade to SP3 latest patch .
Thank you
Jonu – yes.
thx brent, would you point me to some guide on how to patch them
I have given an rtm version of sql server 2014. Do i need to apply sp1 or I can straight move to SQL Server 2014 SP2 CU8 without applying the old sps or cus.
SQLNovice – for Q&A, head on over to https://dba.stackexchange.com.
Is it best practice to install SQL cumulative updates separately from regular Windows updates?
Jay – for Q&A, hit a Q&A site like https://dba.stackexchange.com.