SQL Server Reference Guide

All software has mistakes, called “bugs.” Ever wonder why programming mistakes are called "bugs?" The legend is that because the original mainframe computers had vacuum tubes for programming, they glowed brightly and attracted insects. Bugs sometimes got electrocuted and fell between connections, causing the system to short out and shut down. The system literally had "bugs”. I’m not sure if this is accurate, but it does make a good story.

Substituting one set of code (the one with the bugs in it) for another is called “patching” (another old computer term). It’s a process that isn’t without risk — there’s a possibility that you’ll actually introduce new bugs, or even cause something that was working to stop working. And of course you need to think about any of the work-arounds that you put into place because of the original bug in the first place. I’ll explain the process you should follow after I explain the various options Microsoft has for patching your SQL Server Instances.

Multiple Ways to Patch the System

If you've read many of the tutorials on this site, you can see there are a lot of areas in SQL Server that could house errors, or be optimized to work faster. Microsoft has multiple ways to implement these software fixes. One is called a Service Pack, and the other is called a Hotfix. A newer model uses a Cumulative Update.

What's the difference between a Service Pack and a Hotfix? Well, the official documentation states that Service Packs normally have three properties:

• Provide security fixes
• Correct software errors
• Enhance performance

Normally, Service Packs don’t add new functionality or change the interface dramatically. Service Packs are bundled into a programmed delivery method, and are cumulative. That means that you can install Service Pack three without applying Service Pack two, or even one. Service Packs are for general use — pretty much everyone should install a Service Pack, with the caveats that I'll tell you about in a moment. These Service Packs are free, and open to anyone with any edition of SQL Server. Although some vendors charge for incremental Service Packs, Microsoft doesn’t.

A Hotfix is usually a specific security or software flaw that is addressed in code. There may or may not be a packaged delivery method — some Hotfixes just come with instructions of how and where to copy the patch. Most of them, however, require that you create a Service Request, and then the support technician will work with you to deliver the Hotfix using a download code and location. Depending on your support contract (or lack of one) there may or may not have to pay for the Service Request time. The patch is free.

Hotfixes are normally not for everyone — Microsoft states that you should only apply the patch if you're having the specific problem it addresses. Even then, some Hotfixes are only available from a Microsoft support representative. This happened to me recently. I once had an error I couldn't correct on a SQL Server system, and I got a specific correction from a Microsoft Product Support person. They then gave me a location to download the Hotfix. It came with several ominous warnings about not putting on other systems and so forth. Some Hotfixes come this way, and others are available on the web, but all of them correct a specific problem.

At the beginning of development on SQL Server version 2008 Microsoft introduced an “Incremental Servicing Model” which has been termed a “Cumulative Update” (CU). A CU is kind of a mix between a Service Pack and a Hotfix. The difference between a CU and a Service Pack is that you shouldn’t install a CU unless you need it. The difference between a CU and a Hotfix is that you don’t have to make a support call to get it – but in some cases you do have to click on a link, fill out some info and then get an e-mail with the download link.

You can think of the difference between a Service Pack and a Hotfix/Cumulative Update the same way as a vitamin tablet and a bottle of insulin. Most everyone can and should take a vitamin, but it’s a bad idea to take insulin without having a medical condition that requires it.

Keep in mind that you apply a patch per Instance — if you have multiple Instances on a single server, you can (and sometimes may want to) patch them at different levels. There are, however, certain components (like the SQL Browser, SSMS and other parts of the system) that are shared when you have multiple Instances. Those will move to the higher level.

How to Determine What Patches You Already Have

So now that you know the difference in a Service Pack, a Hotfix and Cumulative Update, how do you check the Service Pack level on your Instance? Or are you at “RTM” — Released to Manufacturing (no patches at all)?

To see the version number in a graphical tool in SQL Server 2000 and lower, open Enterprise Manager and click on the server’s name. On the right hand side of the screen (if you have everything set to the defaults) you’ll see the version number listed.

To see the version number in SQL Server 2005 and higher, open SQL Server Management Studio and connect to the server. The version number is included on the same line as the name of the server.

To determine the Service Pack level installed on your SQL Server in a command window or query, open Query Analyzer (SQL Server 2000 and lower) or SQL Server Management Studio (SQL Server 2005 and higher), and connect to your server. Run the following command:

SELECT SERVERPROPERTY('productversion')
, SERVERPROPERTY ('productlevel')
, SERVERPROPERTY ('edition')
GO

The first line of what you'll see shows the version number of SQL Server software installed on your server. The last three digits (called the build number) of the version number are used to determine the Service Pack installed on your SQL Server.

Microsoft has posted a list of the version numbers that they keep up to date. You can find that reference here.

Your number may not be listed here at all. That's OK, since you might have a Hotfix installed on top of the Service Pack. It's a bit more difficult to determine the Hotfix number, since each one will be different, and many may be installed.

There are also stored procedures you can use to show you the build number on your server, such as:

EXEC sp_server_info 
GO
and 
EXEC master..xp_msver
GO

These procedures show more information than just the service pack numbers.

When to Install a Patch

You shouldn’t download the latest patch (even a Service Pack) and install it the day it releases. The first thing you should do when you find that the latest one is out is read up on the fixes it provides. There may be interactions you may not be aware of with other parts of your server. You should be especially careful regarding the programs written against your databases, since they may contain workarounds for the bugs in the current release. The best thing to do is to check with the author of the program to make sure they know you're putting that Patch on.

There is a situation where you can blindly apply a Service Pack. If your server is currently at a Service Pack level and you install a new feature from the original DVD (such as Analysis Services or Reporting Services), then you should probably re-apply the Service Pack. Failure to do so may render that feature unusable. Once again, read the documentation to make sure.

How to Apply a Patch

The short version: do your homework by reading up on the latest information, create a plan, and install and test the Service Pack to a test server. Run your primary applications to ensure that they work, and get a sign-off on them from the business community and development teams. Take several backups. Then apply the patch (according to your plan) on the production server. Run more tests, be ready to fall back if you have to.

The longer version: always read the documentation before you apply a patch, whether it is a SP, Hotfix or CU. I can’t emphasize this enough, and in fact it’s the most common mistake I see in shops. While it is possible to back-out a patch, it can be really painful, and even be destructive. I don’t recommend it. Read the docs — they will tell you what features will change. Check those features with your application vendor, or your application developers. Meet with them, talk with them, make sure everyone understands what is changing.

Next, create a plan of attack. Plan the date, plan the recovery, explain to everyone (even the business, especially the business) what you plan to do and what will happen if things go well or if they don’t. Make sure you include the testing part of what you’ll do, and the time the tests will take until the system is available again.

Take a full server backup from the Operating System level with the SQL Server services turned off. This will guarantee you can return to a previous state if something goes wrong during the upgrade. Remember, you’re replacing binary files on the Instance, so just having your normal database backups isn’t enough.

Next, turn the SQL Server services back on and run your normal maintenance, including taking a backup of all your databases. This includes the master database as well as your entire user database collection. This provides a quicker fallback than the full system backup.

Now follow your plan and apply the patch, per the instructions. Once again I can’t emphasize how important it is to read the documentation before you create your plan. It may very well have steps that will change how, when or what you need to do.

Next, run your plan against the test server, and run the tests for the application. Make sure everything is as you expect it, and by now your backups will be complete on your production server. Apply the patch there, and run your production tests. Stay sharp and have those backups and backout strategy handy just in case things don’t go as you expect.

Once the patching is complete, make sure you check the server with any applications written against it before you let everyone back in. You want to make sure everything is OK before you allow users to put production data into your system.

And that's it! While you shouldn't apply patches blindly, you should keep informed regarding the patches that are available. It has been estimated that less than half of the systems in production today have the proper Service Packs and Hotfixes installed. This makes the systems less stable and prone to security breaches.

InformIT Articles and Sample Chapters

Roberta Bragg covers automated methods for applying Service Packs and Hotfixes in her article Maintaining Security by Implementing, Managing, and Troubleshooting Service Packs and Security Updates.

Books and eBooks

If you have multiple servers, you need to check out Operations Manager. It can help you solve these issues far better than doing it by hand. Check out System Center Operations Manager 2007 Unleashed for more.

Online Resources

The Microsoft site for finding the latest patches is here.