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 http://qa.sqlservercentral.com/columnists/bknight/sp1sql2k.asp.
Before you can install the service pack, go to http://www.microsoft.com/sql/downloads/2000/sp1.asp
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
versions:
·
8.0.194 Base Analysis
Services
·
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: http://qa.sqlservercentral.com/columnists/bknight/sp1sql2k.asp.
