Installing Service Pack 2

Change happens faster in the computer industry than in any other, and the SQL Server sector is no exception. No sooner had I upgraded to Windows 2000 than Microsoft released Service Pack 2 (SP2) for SQL Server 7.0 and OLAP Services. Here are seven steps to ensure that installing SP2 goes smoothly. (Before you begin, read the readme.txt file; it always contains important information.)

1. Back Up the SQL Server Databases

You should always do a full system backup before you install any system upgrade. Although a service pack doesn't change user databases, it often changes system databases such as master and Microsoft database (msdb). To perform the backup, use your usual backup program, or at least archive the MSSQLData folder.

2. Back Up the OLAP Services Databases

As a precaution, back up your OLAP Services databases. Again, use your usual backup method, or at least archive the OLAP ServicesData folder.

3. Enable Autogrow for Master and Msdb

The SP2 installation instructions say that you need a minimum of 500KB of free space in the master and msdb databases. To ensure that you have the necessary room, right-click the msdb and master databases in Enterprise Manager, select Properties from the menu, and select Automatically grow file.

4. Stop the SQL Server Services and Other Database Applications

Ensure that active applications don't have a lock on the SQL Server executable files or other database connectivity files, such as your ODBC drivers. Before the installation, use the SQL Server Service Manager to stop the MSSQLServer, MSSQLServerOLAPService, SQLServerAgent, and MSDTC services. Also be sure to stop the MTS, MSMQ, and COMTI services, and end ODBC/OLE DB applications.

5. Install SQL Server SP2 Components

Install SP2 for SQL Server by running the setup.bat file. You'll find the file in the service pack CD-ROM's root directory or in the root directory to which you expanded the service pack files from the sql70sp2i.exe archive. To run the installation, you must use an account that has sysadmin authority.

6. Install OLAP Services SP2 Components

To install SP2 for OLAP Services, run the setup.bat file, which is in the service pack CD-ROM's MsolapInstall subdirectory or in the MsolapInstall subdirectory to which you expanded the service pack files from the sql70olapsp2i.exe archive. Use an account that has sysadmin authority.

7. Restart the SQL Server and OLAP Services

After applying SP2, restart all the services and applications that you ended before you applied the service pack.