SQL Server Reference Guide

Service Packs

Last updated May 16, 2003.

All software has mistakes in it, called “bugs.” Ever wonder why programming mistakes are called "bugs?" The original mainframe computers had vacuum tubes for programming, and the legend is that when these glowed brightly they attracted insects. These bugs sometimes got electrocuted and fell between connections, causing the system to short out and shut down. The system literally had "bugs."

What are Service Packs?

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 implemented two ways to implement these software fixes. One is called a Service Pack, and the other is called a Hotfix.

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. They are for general use — pretty much everyone should install the Service Pack, with a caveat that I'll tell you about in a moment.

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. 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.

You can think of the difference between a Service Pack and a Hotfix 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.

What Service Packs Do I Have Installed?

So now that you know the difference in a Service Pack and a Hotfix, how do you check the Service Pack level on your server?

To see the version number in a graphical tool in SQL Server 2000, 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, 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) or SQL Server Management Studio (SQL Server 2005), and connect to your server. Run the following command:

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

If you’re using SQL Server version 7, you need a different command:

SELECT @@VERSION
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. To determine the Hotfixes on your server, download the program called HFNetChk from Microsoft. There's a commercial version of this tool by the way that will keep your servers up to date, but this is the free one Microsoft provides.

There are now 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 Do I Install a Service Pack or Hotfix?

You shouldn’t download the latest Service Pack or Hotfix 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 Service Pack 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 CD (such as Analysis Services or English Query), then you should immediately re-apply the Service Pack. Failure to do so may render that feature unusable.

How Do I Install the Service Pack?

Do your homework by reading up on the latest information, 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 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.

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.

Download the Service Packs from Microsoft at http://www.microsoft.com/sql. When you get the package, you can run it to expand the files it contains. Double-click the “setup.exe” fileto start the install of the Service Pack.

Once the Service Pack 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 Service Packs or Hotfixes blindly, you should keep informed regarding the Service Packs and Hotfixes 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 this article.

Online Resources

You can find out about Service Packs and Hotfixes for SQL 2005 here.

For SQL Server 2000, check here.