Applying SQL Server Service Packs

Problem
As with most applications there are hot fixes, updates and/or service packs that are often released. Some of these updates are automatic while others require you to take action and apply the update. SQL Server is one of those applications that requires you to take action versus having the updates automatically applied. This is a good thing, but it requires the DBA to take some type of action. In addition, applying the service packs to the database engine is pretty much understood, but did you also know that these updates also need to be applied on the clients as well?

Solution
As mentioned already, the SQL Server service packs are usually applied to the server and you can easily tell what version of the software you are running by either issuing a T-SQL command or by using the GUI such as the following.

SQL Server Management Studio

This is shown in the the object explorer.

SQL Server Enterprise Manager

This is shown if you right click on a server and select properties.

T-SQL Command

Or you can execute SELECT @@VERSION to get this info.

To tell what version you have installed refer to this tip: How to tell what SQL Server version you are running.

Client Tools

In addition, the client tools also need to be updated as well to ensure you have the latest release and also to ensure that all features still work properly. There are some cases where older versions of the tools will not work properly, so you will need to make sure you also update the client tools.

The install process works the same way as the server updates where you apply the service pack, but you only need to update the client tools which should be the only option when installing the service pack update.

The following shows you how you can determine what version of the client tools you have installed.

SQL Server 2005

From within management studio, select Help -> About and you will get a screen like the following. From this screen you can see what version of the Management Studio is installed as well as other components of SQL Server.

SQL Server 2000

With SQL Server 2000 it is not as easy. If you select Help -> About Microsoft SQL Enterprise Manger you will get the following screen. From this screen you can not tell what service pack updates have been installed only that is it is version 8.0, so this is not very helpful.

Another way to find out is to browse to the "C:\Program Files\Microsoft SQL Server\80\Tools\Binn" folder or whichever folder you have the SQL Server tools installed. You could then right click on one of the applications and select Properties and then look at the Version tab. Here we can see that the version of SQL Profiler is 2000.80.2039.0 which is the same as the database engine listed above.

Enterprise Manager is an MMC snap in, so you can not see the actual version of the file by using the same technique. When you try to look at the Properties for file "SQL Server Enterprise Manager.MSC" there is no version tab. There are other applications in this folder that you can look at which should show you the same version number, so you can pretty much be sure this is the version that has been installed.

Next Steps

• Next time you apply your service packs to your servers, don't forget about the client tools on your desktops
• Check out your existing client tools installations to make sure you have the latest versions installed or at least the equivalent of what is installed on your servers.
• Refer to this tip How to tell what SQL Server version you are running to find out what the version number corresponds to.