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 MSSQL\Data 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 Services\Data 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 Msolap\Install subdirectory or in the Msolap\Install 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.

End of Article


You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Too late your advice :( After i install SP2 for SQL7 in my production server, NT refuses to start, with a "STOP c000021a: the winlogon process ...". After I try every trick I know, with the same results, I have to reinstall NT. Since then, no more trouble.

julio moreyra- August 11, 2000

After installing Service Pack 2 on the SQL Server 7.0 (both parts - database components and OLAP Services) I cannot connect to the server using OLAP manager installed on client computer. The error message is "Cannot retrieve the repository information from the server (name)". I replace the existing repository file msmdrep.mdb with the one from backup and got the same result. I could not find any information about this error message anywhere, so please, help me to resolve this problem.

Irene Medvedev- August 18, 2000

I would also backup the \MSSQL7\BINN subdirectory, in the event the Service Pack upgrade goes bad. A good DBA can never have too many backups.

Shea Walker- August 24, 2000