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.

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.