How to Obtain and Install SQL Server Service Packs and Cumulative Updates

If you are in charge of one or more SQL Server instances, whether they are on a laptop, or a large Production data center, you need to be aware of how SQL Server updates and fixes are released, and how you can obtain them.

First, a few definitions:

RTM means Release to Manufacturing. It is the original, released build version of the product, i.e. what you get on the DVD or when you download the iso file from MSDN.

A Hotfix is designed to fix a single issue, usually after a case has been escalated through Microsoft CSS to the SQL Server Product Team.

A Cumulative Update is a cumulative package of hotfixes (usually 20-40) that also includes all previous cumulative updates for that Service Pack. Cumulative Updates are not fully regression tested. Cumulative Updates are released every eight weeks.

A Service Pack is a much larger collection of hotfixes that have been fully regression tested. Service Packs are only released every 12-18 months.

When you first install a shiny new copy of SQL Server 2008 R2 (or 2005 or 2008) from the DVD that came with the box, what you have is the RTM build of that version of SQL Server. Depending on how long ago that major version of SQL Server was released, that build that you just installed could be years old.

I often run into instances of SQL Server that are still running the RTM build of that version of SQL Server, which in my opinion is both lazy and irresponsible.  If you don’t do something about it, you could be on an “Unsupported Service Pack”, which means that you will only get limited troubleshooting support from Microsoft if you ever need to open a support case with Microsoft CSS. You are also more likely to run into issues that were fixed after the product went RTM.

Hotfixes don’t get added to SQL Server on a whim, they are only added after working with CSS and the Product Team, and convincing them that the issue that is prompting the hotfix is important enough to actually fix and release. Cumulative Updates are a collection of hotfixes over the previous eight weeks. When you look at the list of fixes for each Cumulative Update, you can determine whether they seem to address any issues that you have been experiencing in your environment. Then you can decide whether to go through the pain of getting them tested and installed.

You can download Service Packs directly from Microsoft without making any special requests. Service Packs are cumulative, so you can go directly from RTM to SP4 without installing any intervening Service Packs. If you have Microsoft Update installed on your machine, it will offer up SQL Server Service Packs as updates, but I prefer to obtain and install them myself.

Some organizations have a policy of only testing and deploying Service Packs (which Microsoft sometimes calls Public Cumulative Updates (PCU)), deciding to ignore any regular Cumulative Updates that are released between Service Packs. They argue that only Service Packs are fully regression tested by Microsoft, and that they don’t have the resources to test and certify Cumulative Updates with their applications and environments. They also argue that third party software vendors have not certified these Cumulative Updates for their products, which is another reason to skip them.

Personally, I am against this approach. As I said before, you should look at the fix list for each Cumulative Update when it is released, and make the determination whether you need to install that CU. Generally speaking, I am biased towards trying to stay current on my Service Packs and CUs.

At any rate, how do you find out about these Cumulative Updates? One way is to read my blog or follow me on Twitter, since I am pretty good about finding them, and announcing them to the world. Fellow SQL MVP Aaron Bertrand (blog|twitter) also does a good job of spreading the word. You can also check the Microsoft SQL Server Release Services blog, and the SQL Server Solution Center.

Once you know that a CU for your version and Service Pack of SQL Server has been released, you need to find the Knowledge Base (KB) article that lists the fixes and has the link where you can “View and request hotfix downloads”.  Here is the KB for SQL Server 2008 R2 RTM CU5. In the top left portion of the KB page, you will see the “View and request hotfix downloads” link.

Once you follow that link, you need to click on the “Show hotfixes for all platforms and languages” link in the center of the page. Otherwise, you will only see the hotfixes for the platform (x86, x64, or ia64) that you are running on the computer where your browser is running, which is probably your laptop or workstation. After you choose the correct packages (which can be confusing), provide a valid e-mail address, and fill in the CAPTCHA information, you will get an e-mail with a link to download a zip file with the Cumulative Update setup program. That zip file is password protected, so you will need the password that is included in the e-mail to unzip it.

I always immediately unzip the file and save it in a directory structure like you see below (with further subdirectories for x86 and x64), because the password expires after seven days.

image

Finally, after jumping through these small hoops, you are ready to install it on a test instance (which could be a virtual machine) just to make sure that the setup program works, and does not seem to break SQL Server. After this initial smoke test, you can do additional installs and further, more involved testing before you deploy it to your Production environment.

When it is time to deploy a CU in Production, you are looking at an outage of some duration, no matter what you do. At a minimum, the CU setup program will stop and start the SQL Server Service, and it could be stopped for several minutes while the CU installation is running. Sometimes, the CU setup program will want a reboot after it finishes, depending on what SQL Server components you are running. If you don’t have any high availability solution (such as database mirroring or fail-over clustering) in place, this outage could last anywhere from 5 to 20 minutes, which is kind of bad…

If you do have database mirroring or fail-over clustering, you can do a “rolling upgrade” where you upgrade the Witness, then the Mirror, and then the Principal (for mirroring) or each node in turn (with fail-over clustering), where you can install a CU with a couple of sub minute outages.  Using database mirroring, my outages are typically 10-12 seconds each for this type of maintenance.

This entry was posted in SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and tagged , . Bookmark the permalink.

11 Responses to How to Obtain and Install SQL Server Service Packs and Cumulative Updates

  1. Great post Glenn! I just added Microsoft SQL Server Release Service blog to my RSS feed. I didn’t know they had a blog.

  2. Pingback: @GlennAlanBerry posts How to Obtain and Install SQL Server Service Packs and Cumulative Updates | Glenn Berry’s SQL Server Performance | sqlmashup

  3. Pingback: SQL Server Blogs and news for the week 14/01/11 | John Sansom - SQL Server DBA in the UK

  4. Cliff says:

    Hi there, would just like to confirm the following, can I just skip straight to CU7 for SQL 2008 R2 Express and it will include fixes from CU1 – CU6?

    Or do I have to download and install from CU1 thru CU6?

    Thanks for your help

    • Glenn Berry says:

      You can just skip to the latest CU for a branch (whether it is the RTM or SP1 branch) and you will get all the previous CU fixes for that branch. If you are on the RTM branch, you would have to install SP1 first, then you could install the latest CU for SP1.

  5. Pingback: How to Obtain and Install SQL Server Service Packs and Cumulative Updates « Obtained's Blog

  6. hennie7863 says:

    I was wondering whether CU will be released for older RTM or Service packs? For instance SP2 is out but MS is still releasing CUs for SP1 (as it seems to me). Is that correct? In that case, why is that?

    • Glenn Berry says:

      Microsoft only releases Cumulative Updates for “supported” Service Packs for each major release. At some point in time, they “retire” the RTM branch, then they “retire” the SP1 branch, etc. Once a branch is retired, there are no more Cumulative Updates for that branch.

  7. john says:

    I am having difficulty with a CU install. I was running 2008 R2 SP1 and applied CU3. Now I try to install CU5 and cannot select an instance. I think the setup files are somehow out of sync. Do you have any suggestions on how to correct the issue? Also, is it safe to uninstall CU3, and if I do does that cleanly get me back to the prior release?

  8. jrkancharla says:

    Excellent piece of information. I am the lead developer for over 30 sql server instances but unforunately we have a lousy dba support group that does not looks into maintenance tasks at all. I myself sometimes need to look into available update options and provide the software package and have them install it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s