Installing Service Pack 1 for SQL Server 2000


Estimated total down time: <10 minutes

Estimated total down time if SQL Server is on a cluster:

<20 minutes

Like most SQL Server service packs, the latest service pack

by Microsoft for SQL Server 2000 is simple to install. Don’t let this deceive

you though. A step mishap can leave your system in an inconsistent state and

cause major problems when users begin to hit it again. This article will provide

you a step-by-step guide to installing Service Pack 1 for SQL Server 2000. I

will also cover how to install the service pack in a clustered environment,

which adds a few extra twists to the installation.


This article gives you a step-by-step guide on how to

install the service pack but does not cover the actual service pack in much

detail. For more information on what the service pack includes, see

Before you can install the service pack, go to

to download the appropriate files. The service pack comes in three flavors: SQL

Server 2000, Desktop Engine, and Analysis Services. The sql2ksp1.exe

file is what most people will use to update SQL Server and it will work on any

edition of SQL Server except the Desktop Engine.  

After downloading the file, double-click on the file. You

will then be prompted for which directory you’d like to extract the files to.

You will need a total of about 110MB of available space for the file to extract.

Make sure you extract the files to a directory that has no spaces in it. You may

receive an error otherwise when you try to install the service pack. As the file

is being delivered to the directory, your server’s performance will slow for a

brief few minutes.

 Before you start the installation, backup your

databases. Backups should include every database on the system. If you're

installing the Analysis Services service pack, backup all the cubes as well as

your registry. Proper backups are very important because rolling back is NOT

easy!  Also before you begin the

setup, make sure all services related to SQL Server are stopped. This would

include the Microsoft Component Services, Microsoft Message Queuing, and

Microsoft COM Transaction Integrator services if you have these on your machine.

Also make sure that your control panel is closed. Do not stop the MSSQLServer

service if you’re installing the service pack on a clustered environment. This

will be done by the service pack.

 You’re now ready! You will need to make sure that

you’ve prepared roughly 10 minutes for the server to go down plus the amount

of time it takes for your server to reboot. In most cases, a reboot will be

required and some servers can take 20 minutes to perform this. If you’re in a

clustered environment, this entire process takes much longer. You will have to

reboot all nodes in the cluster after installing the service pack. On average,

it takes me 20 minutes to install it on a Active/Active cluster. If you’re

running an Active/Active cluster, keep in mind that this reboot process will

take down both nodes of your cluster. The service pack will also stop shared

services like MSSEARCH and MSDTC.

Before you

move forward, you may want to confirm what service pack you’re on now.

You’ll also want to do this after you finish the install to make sure the

system catalog has been upgraded. You can open Query Analyzer and execute the

command SELECT @@VERSION to determine this. Below is a list of versions:


8.00.194 - Base SQL

Server 2000


8.00.384 - Service

Pack 1 for SQL Server

To determine the version of

Analysis Services you're running, simply open Analysis Manager and select About

Analysis Services under the Help menu. Here is a list of Analysis Manager's



8.0.194 Base Analysis



8.0.382 Analysis

Services SP1

 With that done, you’re ready to begin the install.

Installation of the service pack should be easy. If you are performing multiple

installs, I recommend creating an unattended installation script, which allows

you to install it rapidly and without interaction. Since that is a more advanced

topic, that will not be covered by this article. You can run the setup file

called setupsql.exe in the \x86\setup folder.

 The first thing you’ll be asked is what type of

server you’d like to install the service pack on. If you have the SQL Server

installed in a clustered environment, then the service pack will automatically

detect this and prompt you for the virtual server name. Type the exact virtual

server name in this case. You can find this in Cluster Administrator or in

Enterprise Manager. If you’re installing this on a regular non-clustered

server, you must only select Local Computer and click Next.

 You then proceed to the license agreement, where you

must only click Yes to proceed. You will then be asked what instance you’d

like to install the service pack on. This is where instances come in handy. For

example, if you’d like to install the service pack on a 2nd

instance to test your application against it, while leaving the default instance

on its base level. This is also where instances can be dangerous. Even though

you upgrade your second instance to the service pack 2 level, your client tools

and client-communication items like OLE DB will be upgraded. Even though you

only install the service pack on one instance, all instances in SQL Server share

the same communication layers and tools. 

The next step is where you will be prompted for a valid SQL

sysadmin account. If you don’t know the SA password, you can choose to use

Windows Authentication. If you are using Windows Authentication, make sure that

you’re signed in with a user that has sysadmin rights to the SQL Server. After

clicking Next, this will be validated after a long pause. If you’re running

SQL Server in a cluster, you will also be asked for a valid user in the

Administrators group in Windows 2000 or NT. This is so the service pack

installation can copy the files to both nodes in the cluster.

 You will receive one more confirmation screen before

the service pack installs. After clicking Next here, the service pack will begin

to copy files If any of the programs are started, like the SQL Server Service

Manager, you will be prompted to close them. The first item that installs is

MDAC. This process upgrades MDAC to 2.6.1, which contains 20 bug fixes.

 Your SQL Server and related services like SQL Server

Agent and MSSEARCH will then be stopped as files are being copied. If you’re

installing the service pack on an Active/Active cluster, then the shared tools

like MSDTC will be stopped, affecting both instances. This process in a cluster

takes my servers on average 10 minutes to complete. You will not be able to see

the status of copying files or what it is installing during this process. If

you’re installing it in a non-clustered environment, you will be able to see

the status of the install. 

The last step of the installation is a confirmation screen

that asks you to create a backup of the master and msdb database. I prefer to

backup all the databases on a service pack installation. That way I have a

before snapshot of the database and an after-service pack snapshot. You may also

be asked to restart the server. Even if you’re not asked to restart the server

or client, always do so. If you are in a clustered environment, take the SQL

Server resources offline and restart all the nodes in the cluster. You must

restart all the nodes since the tools for both nodes have been updated.

 You’ve hopefully now completed the installation! As

you can tell there are not too many steps to the installation. It is always a

good idea to backup your databases before the installation. If you need more

information on rolling back the installation or more in-depth coverage of the

service pack, see my other article at: