Should You Apply SQL Server 2000 SP2?

Looking for a last-minute holiday gift for that special SQL Server professional in your life? Perhaps SQL Server 2000 Service Pack 2 (SP2) is just the thing. Microsoft released SP2 on November 30; the service pack includes several fixes that might be relevant to your environment. You'll find language-specific downloads for SP2 on Microsoft's Web site.

After you navigate to the language-specific version of the service pack you need, you'll also find a complete list of SP2 fixes and the always-important readme.txt file. SQL Server 2000 SP2 contains three parts that you can apply independently: the Database Components, the Analysis Services Components, and the service pack for Microsoft SQL Server Desktop Engine (MSDE) users.

The Database Components part includes updates for

The Analysis Services Components part includes updates for

In addition, Desktop Engine (MSDE) users should carefully review the installation instructions in the readme.txt file; SP2 installations vary based upon which version of MSDE you install on the machine. The following Microsoft article contains information that will help you determine the correct way to install SP2 for SQL Server 2000 Desktop Engine.

When you apply service packs to a server running multiple instances of SQL Server, keep the following information in mind. You must apply the service pack to each instance of SQL Server to update the database engine. However, SQL Server 2000 uses a single image of tools and Microsoft Data Access Components (MDAC) for all instances running on the machine. Applying the service pack to a single instance will update the tools and certain pieces of the shared-client connectivity.

Should you immediately upgrade to SP2? It depends. I'm aggressive about rolling out service packs in my development environments. After all, knowing what each of your application-development teams is doing is difficult; it's almost impossible to know for sure whether a particular service-pack fix is relevant. I've seen many cases where the application of a new service pack fixes a previously unidentified performance problem. However, you should roll out service packs in a production environment with care, and the age-old advice "if it ain't broke, don't fix it" applies more often than not. And letting the service pack age in the public domain is always a good idea. New service packs inevitably introduce a few gotchas here and there, and it's nice to let someone else find them first. A quick review of the SQL Server newsgroups at msnews.microsoft.com could uncover service-pack problems that are relevant to your SQL Server implementation.

Whether it's today, next month, or 6 months from now, you'll eventually apply the latest service pack. But be sure to read the entire readme.txt file before you do so. For example, the readme.txt file for SQL Server 2000 SP2 explains that you can't simply uninstall the service pack. If you need to uninstall the service pack from an instance, you'll have to perform a complete SQL Server rebuild of that instance. Never apply a service pack to a production system unless you're prepared to restore the entire system from backup or to explain to your boss's boss why the company's mission-critical application suddenly stopped working.

SP2 already has an addendum to its initial readme.txt file that corrects a few errors. The most notable problem is that the original readme.txt file incorrectly lists the value of @@version for SP2 as 8.00.532. The correct value of @@version for SP2 is 8.00.534. This addendum is also available from the main download page (see URL above).

Because of the holidays, we won't publish SQL Server Magazine UPDATE next week. But have a wonderful holiday, and I'll be back in the new year!