SQL Server 7.0 Service Pack 2

After a three-month public beta version, Microsoft released SQL Server 7.0 Service Pack 2 (SP2), which includes more than 170 known fixes to SQL Server and 20 fixes to OLAP Services. SP2 includes a Microsoft Data Access Components (MDAC) service pack upgrade and makes structural changes to SQL Server and Data Transformation Service (DTS). Let me guide you through some of the fixes and installation details you’ll need to make this process go smoothly.

The service pack solves many serious security and miscellaneous problems in SQL Server, including 15 access violation problems that could freeze your server. It includes some minor fixes in SQL Distributed Management Objects (DMO) to repair some memory usage issues. Although Microsoft didn’t update the English Query feature in SP2, everything this feature depends on has been updated. With so many changes in one service pack, your application will need intense regression testing.

General Information

You can break SP2 installation into two independent installations that you can run separately or together. For Intel-based machines, the database service pack file is Sql70sp2i.exe and the OLAP Services file is Sql70olapsp2i.exe. The database service pack will upgrade SQL Server to version 7.00.842. To determine your current version number, type SELECT @@VERSION in Query Analyzer. You should have one of the following SQL Server versions:

You can apply the same database service pack version to all editions of SQL Server 7.0. It affects almost every facet of SQL Server, fixing bugs in the core storage and relational data engine, and in tools such as Enterprise Manager and osql.

Finding your current OLAP Services version is a little more complicated. In OLAP Manager, click on the server you want to query. On the OLAP Manager Help menu, click About Microsoft SQL Server OLAP Services. The following list shows the OLAP Services version numbers.

SP2 also includes upgrades that you might not expect, and you should test them thoroughly. By installing the OLAP Services service pack, you’ll also install the OLAP Add-in Kit 1 and DTS Task Kit 1. These two OLAP features offer a new DTS task for processing an OLAP cube and some added features such as backups for OLAP. (In previous versions, you could add these two features manually.) When you install the database portion of the service pack, you’ll also install a new version of MDAC (MDAC 2.1 SP2).

Before Installation

Before beginning the installation, make sure that you have 193MB of hard drive space for the database upgrade and 187MB for the OLAP Services upgrade. When you extracting SP2, don’t extract the files to a directory that contains spaces or you’ll receive an error when you try to install. (This is also true for SQL Server 6.5 service packs.) Because of some MDAC changes, you’ll have to reboot the server unless you’re already operating on MDAC 2.1 SP2.

Before executing setup, you need to stop the following services:

In addition, close any open applications, including the control panel and SQL Server Service Manager. (If you leave the Services window open in control panel after stopping the services, you’ll receive an error message that the service is locked.) If you’re installing the OLAP Services SP2 on an OLAP system and you have moved your repository to SQL Server, stop all services listed previously except MSSQLServer.

Before installation, back up all user databases and the Master and msdb databases. Save any DTS packages as .dts files. Although DTS packages are covered with your msdb backup, saving them as .dts files adds an extra layer of protection. In addition, make sure that your master and msdb databases are set to automatically grow or have at least 500KB of free space.

If you’re installing OLAP Services SP2, back up your OLAP Services databases by stopping the service, then backing up your OLAP ServicesData directory. If you have migrated the repository to SQL Server, back up the database where it is stored. If you have not migrated the repository, back up the Msmdrep.mdb file (located in the OLAP ServicesBin directory). No matter which service pack you install, always back up your registry.

Installing any SQL Server service pack in a clustered environment is challenging. You’ll essentially have to break the cluster, install the service pack on the nodes, and then re-establish the cluster. You can do this using the Failover Cluster Wizard, and this process can be more daunting than the actual SP2 installation. Be sure to study the clustering white paper "How to Install SQL Server 7.0 Enterprise Edition on Microsoft Cluster Server" before installing SP2 in a clustered environment. SQL Server 2000 has vastly simplified this process.

Installation

For Intel-based machines, the SP2 setup files are located in the X86setup directory. Double-click setupsql.exe to begin. After prompting you for the sa password and intervening one other time, the installation will complete without further input from you. Toward the end of the process, a series of scripts will execute to upgrade your system catalogs. These scripts must all complete successfully, because these stored procedures issue many object drop and create statement procedures on your system catalog. If the upgrade is interrupted, your system might become unstable.

Install the service pack on every client workstation running any client utility such as Query Analyzer or Enterprise Manager. The MDAC upgrade is automated in the SQL Server database upgrade. If you only want to install the latest MDAC version, it’s much simpler to download the MDAC 2.1 SP2 files individually. This individual MDAC file takes up less space than SP2 and is simpler to upgrade. If you choose this process, you need to restart your machine.

If you upgrade a server participating in replication, as with every SQL Server service pack you need to install the Distributor first. From the distributor, install the Publisher, then the Subscribers in any order. You can upgrade one piece at a time without affecting the rest of your topology. You won't be able to fully access some new replication features until all involved servers have been upgraded.

Major Changes

DTS buffs might be disappointed by this upgrade. Any SP1 or release to manufacturing (RTM) client won’t be able to open packages saved on a server or workstation that uses SP2. SP2 workstations and servers can see SP1 packages, but after they save the package, SP1 machines can no longer view it. This problem stems from the fact that Microsoft introduced significant structural changes in the way DTS stores the files incorporated in SP2.

SP2 provides two new options for starting SQL Server. The new -g switch specifies (as an integer) how many megabytes of memory SQL Server leaves available to SQL Server outside processes. This memory is used for extended stored procedures and creating selected objects in SQL Server. Don’t set this option unless you have 2GB of physical memory for Desktop and Standard edition or 3GB for Enterprise Edition. To set this switch to 256MB, use the following syntax:

-g 256 

Another change in SP2 is an added database option called pending upgrade. This option was established for SQL Server 2000 compatibility; don’t use it in SQL Server 7.0. When this option is set to true, users can’t create indexes or statistics in the target database. If they try, they’ll receive error 1931:

Create index/Create statistics is disallowed when the database has pending upgrade enabled.

The upgrade also adds several merge replication enhancements, including additional system stored procedures, to improve speed and optimization.

Rolling Back

With any luck, you won't need this section of the article. If you need to roll back to a previous service pack or base SQL Server, there is no easy way to downgrade. The least complicated method is to back up all your databases, reinstall SQL Server, and then reapply Service Pack 1. You can also run sp_detachdb to detach your databases before the downgrade and sp_attachdb to attach them after the reinstall. Then you’ll need to recreate logins and tasks. If you previously set up replication, you’ll have to redo that, too. Don’t restore your SP2 master or msdb database, because the data will have changed with the upgrade to SP2.

Known Issues

SP2 will inevitably have bugs, but most will be nuisances rather than major showstoppers. Some, such as the changes to DTS, aren’t really bugs at all.

One of the most alarming bugs that occurs with both the SP1 and SP2 installations is that the sa password is written to a log in clear text. This log file, sqlsp.log, which is stored in the system’s temporary directory, has permissions assigned to it that let any system user view the log. The bug only occurs if you are in Mixed Mode and type the password during the upgrade. If you are in NT authentication mode, this bug doesn’t apply. You can either delete the file after the upgrade or install the patch.

A noticeable bug generates error 8630 when a user tries to update a table with a compound key. A table with a compound primary key and foreign key on those columns will generate the following error.

Server: Msg 8630, Level 17, State 38
Internal Query Processor Error: The query processor encountered an unexpected error during execution.

You can solve this problem by changing the table’s definition. You can change the index for the primary key to a clustered index, drop the foreign key on the table, or designate another column as the primary key.

Another known issue is within the tools, including Query Analyzer. When you issue a large query that displays the results in a grid format, queries might not be saved properly into a comma-separated file. If you display the results in text format, the problem won’t occur.

The last known bug concerns the Maintenance Wizard (sqlmaint.exe). Without SP2, the Attempt to repair any minor problems option (-CKDBrepair at the command line) didn’t work, because the Maintenance Wizard didn’t place databases in single-user mode. If you install SP2, the wizard will now place the databases in single-user mode and not take them out of that status. The only solution is to avoid selecting this option.

Staying Informed

Microsoft is currently investigating other potential bugs, including one that deals with merge replications that use rows or joins. Microsoft had not confirmed these bugs when this article went to press. Check the Microsoft support sites for up-to-the minute bug announcements. Also, remember that whenever Microsoft releases a new service pack, the newsgroups are flooded with messages about bugs, and only a fraction are confirmed.

You can learn about new service pack bugs or hotfixes by reading biweekly database news flashes that report confirmed bugs. If you’re an MCP, you can view more frequently updated reports at http://partnering.one.microsoft.com/mcp/newflash.htm. You can also find an updated list of bugs by searching for the keyword kbSQLServ700sp2bug on Microsoft’s support site.

But be careful before installing any of these hotfixes. Regression-test thoroughly before you send a hotfixed system into production. Do not install any posted hotfix unless you're experiencing a showstopping or production problem. You’ll get more reliable results by waiting for the next service pack release.

Finally, you can receive frequent security alerts by subscribing to Microsoft's security bulletin service.

With so many additions and changes to SQL Server and OLAP Services in this service pack, you’ll take a major risk if you simply roll it out to your production services. Move slowly and test extensively. Surf the Web and newsgroups constantly for any new bugs or problems this service pack might have introduced. To download SP2 and view a complete list of fixes, visit the Microsoft site.