SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Installing Service Pack 1 for SQL Server 2000

By Brian Knight,

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://www.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://www.sqlservercentral.com/columnists/bknight/sp1sql2k.asp.

Total article views: 4793 | Views in the last 30 days: -1
Related Articles

Error: Installing SQL Server Cluster Instance

Error: Installing SQL Server Cluster Instance


Need to Install 2nd Instance of SQL Server 2005 on a clustered server

Need to Install 2nd Instance of SQL Server 2005 on a clustered server


Installing reporting services on SQL server default instance

Installing reporting services on SQL server default instance


Install Reporting Services in Cluster

Multiple Instance Cluster


Steps to install Service Pack for SQL Server 2005 – Multiple Instances

Steps to install Service Pack for SQL Server 2005 on multiple instances in one go :- Step 1 : First...