Service Pack 4 for Microsoft SQL Server 2000 (64-bit)

March 24, 2005

© Copyright Microsoft Corporation, 2004. All rights reserved.

 
The SQL Server documentation team cannot answer technical support questions, but welcomes your suggestions and comments about this readme documentation. You can quickly and directly send e-mail feedback using the link below. Please send your feedback in English.

To submit written feedback about this document, click here:  Submit feedback.
 

Contents

1.0 Introduction

    1.1 System Requirements

    1.2 Before You  Upgrade to SP4

    1.3 Identify the Current Version of SQL Server 2000

    1.4 Additional Information About SP4

2.0 Where to Find and Download SQL Server 2000 SP4 (64-bit)

    2.1 Choosing the Correct Language

    2.2 Downloading SQL Server 2000 SP4 (64-bit)

    2.3 Extracting the Database Components SP4 Files

    2.4 Download and Extraction Phase Guidelines

    2.5 SP4 Setup Documentation

3.0 Service Pack Installation

    3.1 Prepare for SP4 Installation

    3.2 Install SQL Server 2000 SP4 (64-bit)

    3.3 Restart Services and Applications

    3.4 Install on a Failover Cluster

    3.5 Install on Replicated Servers

    3.6 Apply SP4 to Read-Only Databases or Filegroups in a Replication Topology

    3.7 Uninstall SP4

    3.8 Reapply SP4

4.0 Additional Installation Information

    4.1 Unattended Installations

5.0 Documentation Notes

    5.1 Database Enhancements

    5.2 Analysis Services Enhancements

    5.3 Replication Enhancements

    5.4 Error Reporting

    5.5 Serviceability Enhancements

[Top]

1.0 Introduction

This Readme file describes how to use Microsoft® SQL Server™ 2000 Service Pack 4 (SP4) to upgrade existing instances of SQL Server 2000 (64-bit).

The general process for installing SP4 is as follows:

1. Determine whether you can use SP4 and if so, which part of parts of SP4 you need to  install. Make sure to review all parts of Section 1.0 of this Readme before you download and install SP4.
   
   
2. Download and extract the service pack installation files. Section 2.0 describes how to obtain the SP4 installation files.
   
   
3. Prepare an instance for upgrade to SP4. Section 3.1 details the preparatory steps to take before you install SP4.
   
   
4. Install SP4. Section 3.2 details options for running SP4 setup.
   
   
5. Restart Services and Applications

SQL Server 2000 SP4 has four parts:

• SQL Server 2000 SP4 (64-bit) includes all updates for SQL Server 2000 (64-bit).

  Note   This is the first service pack for SQL Server 2000 (64-bit).

• Database Components SP4 updates an instance of the database engine for SQL Server 2000 (32-bit) Enterprise Edition, Standard Edition, Developer Edition, Enterprise Evaluation Edition, or Personal Edition.
  
  
• Analysis Services SP4 updates the various parts of SQL Server 2000 (32-bit) Analysis Services in a SQL Server 2000 installation.
  
  
• MSDE 2000 SP4 updates instances of SQL Server 2000 MSDE 2000 and MSDE 2000 Release A.

This service pack cannot be used to upgrade any 32-bit SQL Server 2000 components. To upgrade 32-bit components of SQL Server 2000, you must obtain the other parts of SP4, which upgrade SQL Server 2000 Database Components, SQL Server 2000 Analysis Services and SQL Server 2000 Desktop Engine. Separate Readme files describe how to install these 32-bit components of SP4. The 32-bit components and the Readme files are available at this Microsoft Web site.

[Top]

1.1 System Requirements

This section describes changes to system requirements and system related issues that affect the installation of SQL Server 2000 SP4 (64-bit). General information on system requirements for SQL Server 2000 (64-bit) can be found at this Microsoft Web site.

Installation Issues on Supported Systems

SP4 fails to install on an instance of SQL Server 2000 (64-bit) Database Components if the Devices: Unsigned driver installation behavior local security policy for Windows Server 2003 has been set to Do not allow installation.

• If you use the Do not allow installation setting, you must change it to Silently succeed before you install SP4. If necessary, you can return the policy to its previous setting after the installation is complete.

Note   Do not allow installation is not the default setting for these security policies.

To set security policies

1. In Control Panel, double-click Administrative Tools.
   
   
2. Double-click Local Security Policy.
   
   
3. Expand Local Policies.
   
   
4. Select Security Options.
   
   
5. Ensure that the Devices: Unsigned driver installation behavior option in the right pane is set to Silently Succeed before you install SP4:

Application Requirements

If your instance of SQL Server 2000 (64-bit) is being used by an application, before you upgrade to SP4, ask the provider of the application whether any SQL Server 2000 upgrade considerations apply to that application.

[Top]

1.2 Before You Upgrade to SP4

You can apply SP4 to one or more instances of SQL Server 2000 (64-bit).

[Top]

1.2.1 Determine How to Remove Database Components SP4

Before you use SP4 to upgrade an existing instance of SQL Server 2000, it is advisable to plan how to return the instance to its previous state, in case that becomes necessary later. When SQL Server 2000 SP4 is installed, it makes changes to the system tables for maintenance purposes. It also upgrades user and distribution databases that are members of a replication topology. Because of the nature of these changes, SP4 cannot be removed easily. To revert to the build that you were running before you installed SP4, you must first uninstall the instance of the SQL Server 2000 (64-bit) database engine and then reinstall that instance. Second, if you applied any hotfixes, you must reapply the hotfixes to the instance that is being reverted.

Important   To restore your system safely to its pre-SP4 state, you must have made backups of the master, model, and msdb databases immediately before you installed SP4. For more information, see Section 3.1 Prepare for SP4 Installation.

For more information, see Section 3.2 Install SQL Server 2000 SP4 (64-bit).

[Top]

1.2.2 Determine How to Remove Analysis Services SP4

Before you use Analysis Services SP4 to upgrade an existing instance of Analysis Services, it is advisable to plan how to return the instance to its previous state, in case that becomes necessary later. To revert to the build that you were running before you installed Analysis Services SP4, you must first uninstall the instance of Analysis Services SP4 and then reinstall that instance of Analysis Services. Second, if you ran a previous SQL Server 2000 service pack or applied any hotfixes, you must reapply the service pack and hotfixes to the instance that is being reverted.

To prepare for a later return to pre-SP4 SQL Server Analysis Services, you must back up the registry key HK_LOCAL_MACHINE\Software\Microsoft\OLAP Server and all of its subkeys before you install Analysis Services SP4. When you later uninstall Analysis Services SP4, you must delete this registry key and restore the pre-SP4 version from the backup.

For more information, see Section 3.7 Uninstall SP4.

[Top]

1.2.3 Upgrade an Instance in a Replication or Log Shipping Topology

SQL Server 2000 SP4 Setup upgrades user databases that are members of a replication topology. This upgrade factor can affect backup-and-restore functionality of replicated user databases. Before you install SQL Server 2000 (64-bit) SP4, ensure that replication databases and filegroups are writable and that the user account that is running Setup has permission to access the databases.

For more information about applying SP4 to databases that are included in replication topologies, see Section 3.5 Install on Replicated Servers.

If SP4 Setup detects user databases or filegroups that are not writable, it does the following:

• Applies the SP4 replication updates to all writable user databases.
  
  
• Writes a list of the non-writable databases to the Setup log, which is located at \WINDOWS\Hotfix\hotfix.log.
  
  
• Displays the following warning message:

  Setup has detected one or more databases and filegroups which are not writable.

You can ignore this warning unless some of the databases listed in the Setup log are members of a replication topology. If any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply SP4 Setup to that instance of SQL Server 2000 (64-bit).

Note   This message does not affect unattended installations. For more information about unattended installations, see Section 4.1 Unattended Installations.

For more information about making a database writable, see Section 3.6 Apply SP4 to Read-Only Databases or Filegroups. For more information about reapplying SP4, see Section 3.8 Reapply SP4.

You need not remove log shipping before you upgrade to SP4. However, if the database log is shipped to a database that is a replication Publisher, you must do the following:

1. Take the database offline before you apply SP4.
   
   
2. Apply SP4 to the instance.
   
   
3. Bring the database back online.
   
   
4. Run the following script:

   USE master
   GO
   EXEC sp_vpupgrade_replication
   GO

If you apply SP4 without having taken offline all non-writable databases that ship logs to publication databases, you receive this error:

Error Running Script sp_vpupgrade_replication (1)

If you receive this error, follow the preceding procedure.

Note   During installation, Setup makes no distinction between read-only databases and databases that are offline or in a suspect state. If a replication database or filegroup is in any of these conditions during setup and is involved in a replication topology, you must reapply the service pack after making the database writable.

[Top]

1.3 Identify the Current Version of SQL Server 2000

Before running Setup, identify the versions of the instances of SQL Server 2000 (64-bit) that is being upgraded. The method for determining which version of SQL Server 2000 (64-bit) is installed depends on whether you have a database engine or Analysis Services installation.

To identify the installed version of SQL Server 2000 (64-bit) Database Components:

1. Execute one of the following queries against an instance of the database engine by using isql, osql, or Query Analyzer:
   • SELECT SERVERPROPERTY('ProductLevel')
     
     
   • SELECT @@VERSION
     
     
   • SELECT SERVERPROPERTY('ProductVersion')
2. Find out your version based on the following table.

   SQL Server 2000 Version and Level	@@VERSION	Product Level
   Database Components (64-bit) Original Release	8.00.760	RTM
   Database Components (64-bit) SP4	8.00.2039	SP4

   
   

   Note   Your product version may be different than these values if you applied a hotfix after installing the product or after installing a previous service pack. For example, @@VERSION returns a value of 8.00.818 after you apply the security fix MS03-031 to SQL Server 2000 (64-bit).

[Top]

1.4 Additional Information About SP4

A list of the fixes contained in this service pack will be provided in Microsoft Knowledge Base articles 888799 and 888800. Each fix listed in these articles has a link to a Knowledge Base article describing the problem addressed by the fix. Follow the links to the individual Knowledge Base articles to see information about each fix.

Any information relevant to SQL Server 2000 Service Pack 4 that was not available in time to be included in this Readme file will be published in Microsoft Knowledge Base article 884525.

The Knowledge Base articles mentioned in this Readme are available in the Microsoft Support Knowledge Base.

1. Under Advanced Search, in the For text box, type the number of the article you want.
   
   
2. From the Select a Microsoft Product drop-down list, select SQL Server 2000.
   
   
3. Under Search Type, select Article ID.
   
   
4. Click the Run the search right-arrow button.

Hotfixes

All publicly issued SQL Server 2000 (64-bit) security bulletins have been addressed in SP4.

If you received a SQL Server 2000 hotfix after December 2, 2004, that hotfix is unlikely to be included in SP4. Contact your primary support provider about obtaining the same hotfix for SQL Server 2000 SP4.

SQL Server 2000 SP4 includes serviceability enhancements that enable you to uninstall future hotfixes. For more information, see Section 5.5 Serviceability Enhancements.

SQL Server CE and SQL Mobile Server Tools Updates

Users of Microsoft SQL Server 2000 Windows® CE Edition (SQL Server CE) and SQL Server 2005 Mobile Edition (SQL Mobile) who have upgraded or plan to upgrade SQL Server 2000 database and publisher servers to SP4 should also update the server replication components on Microsoft Internet Information Services (IIS) servers. Updated server tools installers are available for SQL Server CE and for SQL Mobile.

Note   Even if you updated your server replication components after you upgraded to SQL Server 2000 SP3 or SP3a, you must install the latest SP4-specific updates to server tools components.

OPENXML Update

SQL Server 2000 SP4 (64-bit) now uses the same internal version of the MSXML technology that is used by 32-bit versions of SQL Server 2000 SP4. This change removes the dependency that SQL Server 2000 (64-bit) had on the version of MSXML 3.0 installed by the operating system.

[Top]

2.0 Where to Find and Download SQL Server 2000 SP4 (64-bit)

Before you prepare to download and extract SQL Server 2000 SP4, read "Downloading and Extraction Phase Guidelines," later in this section.

SQL Server 2000 SP4 is distributed in the following ways:

• On a SQL Server 2000 Service Pack 4 CD-ROM that contains service packs for the following components:
  • SQL Server 2000 SP4 (64-bit)
    
    
  • Database Components SP4
    
    
  • Analysis Services SP4
    
    
  • MSDE 2000 SP4

  If you have an SP4 CD, you can upgrade an instance of SQL Server 2000 (64-bit) to SP4 by using the self-extracting file SQL2000-KB884525-SP4-ia64-LLL.exe from the CD.

• In four self-extracting installation package files that can be downloaded from the Internet at this Microsoft Web site:
  • SQL2000-KB884525-SP4-ia64-LLL.exe (SQL Server 2000 SP4 (64-bit))
    
    
  • SQL2000-KB884525-SP4-x86-LLL.exe (Database Components SP4)
    
    
  • SQL2000.AS-KB884525-SP4-x86-LLL.exe (Analysis Services SP4)
    
    
  • SQL2000.MSDE-KB884525-SP4-x86-LLL.exe (Desktop Engine (MSDE 2000) SP4)

  Note   LLL represents a designator that varies by language.

After you have acquired SQL2000-KB884525-SP4-ia64-LLL.exe from either the download site or SP4 CD, you can then run it to extract the SQL Server 2000 SP4 (64-bit) files onto your computer. SQL2000-KB884525-SP4-ia64-LLL.exe creates a set of folders and files on your hard disk that you can use to install SQL Server 2000 SP4 (64-bit).

[Top]

2.1 Choosing the Correct Language

SQL Server 2000 service packs are language-specific. To upgrade an instance of SQL Server 2000 (64-bit), you must obtain the service pack that has the same language as your instance. You get the service pack either on a SQL Server 2000 SP4 CD or by downloading the SP4 files. For example, if you upgrade an instance of SQL Server 2000 (64-bit) that uses Japanese, you must download the Japanese version of SP4.

If you are unsure of the language of an instance of SQL Server 2000 (64-bit):

• Click Start, then click Run.
  
  
• In the Open box, type Regedit, then click OK.
  
  
• Locate and select one of the following keys:
  • For a default instance of SQL Server 2000, locate and select this subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion

  • For a named instance of SQL Server 2000, locate and select this subkey:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\CurrentVersion

    where InstanceName is the name of the instance.

• In the right list pane, read the value of the Language entry. Compare that value to those in the following table to determine the language for your instance of SQL Server 2000:

  Language Registry value (in hexadecimal)	Language Registry value (in decimal)	Language of That Instance
  0x00000407	1031	German
  0x00000409	1033	English
  0x0000040c	1036	French
  0x00000411	1041	Japanese

[Top]

2.2 Downloading SQL Server 2000 SP4 (64-bit)

To download the self-extracting installation package for SQL Server 2000 SP4 (64-bit):

• Go to this Microsoft Web site.
  
  
• From the Full Download drop-down list in the upper-right portion of this page, select the language that matches the language for your instance of SQL Server 2000, and click Go.
  
  
• On the Microsoft Download Center page that corresponds to the language chosen, select the file SQL2000-KB884525-SP4-ia64-LLL.exe, where LLL varies by language.

[Top]

2.3 Extracting the Database Components SP4 Files

You must first extract the installation files from SQL2000-KB884525-SP4-ia64-LLL.exe before you can install Database Components SP4 (64-bit). You can execute SQL2000-KB884525-SP4-ia64-LLL.exe directly from the SP4 CD, or from the folder into which you either downloaded it or copied it from the CD.

To extract the Database Components SP4 (64-bit) files:

• Open Windows Explorer and navigate to the folder that contains SQL2000-KB884525-SP4-ia64-LLL.exe.
  
  
• Double-click SQL2000-KB884525-SP4-ia64-LLL.exe to extract the Database Components SP4 (64-bit) files. When asked, specify the folder into which you want the SP4 files to be extracted.

[Top]

2.4 Download and Extraction Phase Guidelines

When you download and extract SP4 installation files from the Internet, use the following guidelines:

• When running SQL2000-KB884525-SP4-ia64-LLL.exe to place the SP4 files into a folder, make sure you have free space that is approximately three times the size of the executable file in order to extract and store the service pack installation files. You must have space enough to store the self-extracting file and the extracted service pack files, and temporary working space for the self-extraction program.
  
  
• When you download SQL2000-KB884525-SP4-ia64-LLL.exe to your local or shared directory, and run the file, SQL2000-KB884525-SP4-ia64-LLL.exe prompts you for the name of the folder into which you want the service pack files placed.

  Note   When you extract the service pack to a network shared directory, the path to the folder that you specify is relative to the folder in which you ran SQL2000-KB884525-SP4-ia64-LLL.exe.

• You can rename or move the folder after extracting the components. However, make sure that the folder path name does not contain blank spaces.
  
  
• You can download all four SP4 self-extracting files and extract them into the same target folder to create a location from which you can install each part of SQL Server 2000 SP4. The four self-extraction files will not overwrite each other or interfere with each other.

[Top]

2.5 SP4 Setup Documentation

SP4 installation files contain updated setup documentation that you can access by clicking Help during SP4 setup. This documentation does not update the version of SQL Server 2000 (64-bit) Books Online that is already installed on your computer. To access the updated SQL Server 2000 (64-bit) SP4 setup documentation, run the Setupsql.chm file. Setupsql.chm is located in the root folder on the SP4 CD-ROM, the local folder, or the network share that contains the extracted service pack files.

Note   Updated Books Online documentation for SQL Server 2000 (32-bit) is available as a set of downloadable files at this Microsoft Web site.

[Top]

3.0 Service Pack Installation

To install SQL Server 2000 SP4 (64-bit), follow the instructions in this section. Review the material in Section 1.0 Introduction before installing SP4. The steps to install SP4 are:

1. Prepare for SP4 Installation
   
   
2. Install SQL Server 2000 SP4 (64-bit)
   
   
3. Restart Services and Applications

[Top]

3.1 Prepare for SP4 Installation

You must do the following before you install SP4:

1. Back Up Your SQL Server Databases
   
   
2. Back Up Your Analysis Services Databases, Repository, and Registry Settings
   
   
3. Verify that the System Databases Have Enough Free Space
   
   
4. Verify that the Service Accounts Are Not Disabled
   
   
5. Stop Services and Applications Before You Run SP4 Setup

[Top]

3.1.1 Back Up Your SQL Server Databases

Before you install SP4, back up the master, msdb, and model databases. Installation of SP4 modifies the master, msdb, and model databases, making them incompatible with pre-SP4 versions of SQL Server. Backups of these databases are required if you decide to reinstall SQL Server 2000 (64-bit) without SP4.

It is also advisable to back up your user databases, although SP4 performs updates only on user databases that are members of replication topologies.

[Top]

3.1.2 Back Up Your Analysis Services Databases, Repository, and Registry Settings

Before you install SP4 to upgrade an instance of Analysis Services, back up your Analysis Services databases by making a copy of the Microsoft Analysis Services\Data folder, which is installed by default under the C:\Program Files folder. Back up the database that contains the repository before installing SP4, which by default is located at C:\Program Files\Microsoft SQL Server\MSSQL$instancename\Data\OLAPRepository.mdf, where $InstanceName specifies the instance name for a named instance of SQL Server 2000 (omitted for the default instance). Also, save your Analysis Server registry entries.

To back up registry settings on a computer running on Windows 2000:

1. Run Regedit.exe.
   
   
2. Locate the subkey HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server.
   
   
3. Select the Export Registry File item on the Registry menu.
   
   
4. Specify a name and location for the exported registry file.

To back up registry settings on a computer running on Windows XP or Windows 2003 Server:

1. Run Regedit.exe.
   
   
2. Locate the subkey HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\OLAP Server.
   
   
3. Select the Export item on the File menu.
   
   
4. Specify a name and location for the exported registry file.

If you have migrated your Analysis Services repository to SQL Server, back up the database that contains the repository before you install SP4.

For more information, see Section 3.7 Uninstall SP4.

[Top]

3.1.3 Verify that the System Databases Have Enough Free Space

If the autogrow option is not selected for the master and msdb databases, the databases must have at least 500 kilobytes (KB) of free space. To verify that they have this much space, run the sp_spaceused system stored procedure for the master or msdb database. If the unallocated space in either database is less than 500 KB, increase the size of the database. For more information, see "Expanding a Database" in SQL Server Books Online.

If the autogrow option is selected for the master and msdb databases, and there is sufficient room on the drives, you can skip the preceding space-verification step.

To verify that the autogrow option is selected in SQL Server 2000, open SQL Server Enterprise Manager on a 32-bit computer, connect to the 64-bit instance of SQL Server to upgrade, right-click the icon for the database, and then click Properties. Verify that the Automatically grow file check box is selected.

[Top]

3.1.4 Verify that the Service Accounts Are Not Disabled

Setup may fail if the service startup type for the MSSQLServer and MSSQLServerOLAPService services are set to Disabled.

To verify and enable service accounts

1. In Control Panel, select Administrative Tools, then Services.
   
   
2. Locate the MSSQLServer service in the list and note the value for Startup Type.
   
   
3. If the value for Startup Type is Disabled, double-click the service name and change Startup Type to either Manual or Automatic.
   
   
4. Repeat steps 2 and 3 for the MSSQLServerOLAPService service.
   
   
5. Click OK.

[Top]

3.1.5 Stop Services and Applications Before You Run SP4 Setup

You should stop all applications and services that make connections to all instances of SQL Server being upgraded, including Control Panel, Add and Remove Programs, SQL Server 2000 Reporting Services, SQL Server 2000 Notification Services,  before installing SP4.

You can apply SP4 without first shutting down applications and services. Setup will stop the MSSQLServer, SQLServerAgent, and MSSQLServerOLAPService services. If Setup determines that other applications and services must be stopped, a warning dialog box will be displayed that lists the applications and services that must be stopped. After you stop these applications and services, click Try Again. If you cannot stop an application or service, click Continue. Setup will continue, but you may need to restart your computer after Setup is complete. If you click Cancel, Setup will be cancelled.

Setup may stop SQL Server Service Manager. If this occurs you may need to restart SQL Service Manager after Setup is complete.

Note   Setup cannot always determine which applications and services must be stopped. You can reduce the likelihood that you will need to restart your computer after you install SQL Server SP (64-bit). To reduce this likelihood, you should stop all applications and services that make connections to SQL Server (64-bit), including Control Panel, before installing SP4.

You cannot stop the services in a clustered environment. For more information, see Section 3.4 Install on a Failover Cluster.

[Top]

3.2 Install SQL Server 2000 SP4 (64-bit)

To install SQL Server 2000 SP4 (64-bit), run Setup.exe from one of the following locations:

• The folder on the local computer that contains the extracted service pack files from SQL2000-KB884525-SP4-ia64-LLL.exe, where LLL varies by language.
  
  
• The folder on the network share that contains the extracted service pack files from SQL2000-KB884525-SP4-ia64-LLL.exe.

This starts the setup installation process.

SP4 (64-bit) supports unattended installation. For more information, see Section 4.1 Unattended Installations

Installation Process

Depending on the options selected, the following installation dialog boxes are displayed during setup:

• Welcome — Provides an overview of Setup.
  
  
• EULA — You must accept the end-user license agreement (EULA).
  
  
• Instance to Update — Allows you to select SQL Server 2000 (64-bit) instances to update.
  
  
• Authentication Mode — You must choose the authentication mode that Setup uses when connecting to SQL Server. When possible, select Windows Authentication. If you select SQL Server Authentication, you must supply the password for the sa account. To use different authentication modes or to specify different sa passwords for individual SQL Server 2000 (64-bit) instances, click the Advanced option button, select an instance from the list, and then click Edit to select the authentication mode for that instance.
  
  
• Error Reporting — If error reporting is disabled for at least one instance of SQL Server 2000 (64-bit), the error reporting dialogue is displayed. To enable error reporting, select the check box Enable SQL Server error reporting. This enables error reporting for all instances. For more information, see Section 5.4.1 Error Reporting Enabled for All Instances.
  
  
• Ready to Install — Setup has all the information it needs to begin the installation.
  
  
• Installation Progress — Reports ongoing status for each instance being updated.
  
  
• Installation Complete — Reports the final status of each updated instance. This page includes a link to the log file (HotFix.log).

[Top]

3.3 Restart Services and Applications

When Setup completes, it may prompt you to restart the computer. Section 3.1.3 Stop Services and Applications Before You Run SP4 Setup provides guidelines on when a restart is required. After the system restarts (or after Setup completes without requesting a restart), use the Services application in Control Panel to make sure that any services you stopped before applying the service pack are now running. This includes services such as DTC and the Microsoft Search services or their instance-specific equivalents.

Restart the applications you closed before running the service pack Setup program.

It is prudent to also back up the upgraded master, msdb, and model databases at this time.

[Top]

3.4 Install on a Failover Cluster

The following information applies only to SQL Server 2000 (64-bit) components that are part of a failover cluster.

To install the service pack on a failover cluster

1. If any resources have been added with dependencies on SQL Server resources, those dependencies must either be removed or taken offline before you install SP4. If you do not remove the dependencies, the installation of SP4 will take those resources offline.

   Note   When a clustered resource is taken offline, all dependent resources are also taken offline by the cluster service.

2. Run the service pack from the node (active node) that owns the group containing the virtual server that you plan to upgrade. This installs the service pack files on all nodes in the failover cluster. You cannot install SP4 on any other nodes (passive nodes) in the cluster.
   
   
3. In the Instance to Update dialog box, select the virtual server that you plan to upgrade.
   
   
4. Setup prompts you for login credentials used to connect to other nodes in the cluster.
   
   
5. Keep all nodes of the cluster online during setup. This ensures that the upgrade is applied to each node of the cluster.
   
   
6. If you removed dependencies or took resources offline in Step 1, restore the dependencies or bring the resources online.

Note   Setup might require restarting of the failover cluster nodes. This restart replaces the files that were in use during setup with the updated files.

For additional information about how to install SP4 on a failover cluster, see Knowledge Base article 811168. For an example of how to install SP4 in unattended mode with remote authentication for failover clusters, see 4.1 Unattended Installations.

If you need to rebuild a node in the failover cluster, perform the following steps

1. Rebuild the node in the failover cluster. For more information about rebuilding a node, see "How to recover from failover cluster failure in Scenario 1" in SQL Server 2000 Books Online (64-bit).
   
   
2. Run the original SQL Server 2000 (64-bit) Setup program to restore the node to the failover cluster.
   
   
3. Run SP4 Setup on the node you have added.

[Top]

3.5 Install on Replicated Servers

The following information applies only to existing instances of SQL Server 2000 that are part of a replication topology:

• Before you upgrade to SP4, ensure that the Windows account on which the SQL Server service runs is a member of the sysadmin fixed server role. You must verify this because replication distribution databases are upgraded under the context of the SQL Server service account. After upgrading to SP4, you should remove the Windows account from the sysadmin role.
  
  
• You must upgrade the Distributor before you upgrade the Publisher

  Note   In many cases, especially in merge replication, the Distributor and Publisher are on the same server and are upgraded at the same time.

• When you need to upgrade an instance of SQL Server 2000 in replication topologies based on transactional replication with read-only Subscribers, you can upgrade the Subscriber before or after the Publisher and Distributor.
  
  
• When you need to upgrade an instance of SQL Server 2000 in replication topologies that are based on merge replication or transactional replication with updating Subscribers, you must upgrade the Subscriber after the Publisher and Distributor.
  
  
• If you use merge replication and the Distributor is located on another computer or database instance (a remote Distributor), after applying SP4 you must generate a new snapshot.

[Top]

Installing SP4 on a Server that Acts as a Publisher and a Subscriber

You might need to quiesce the system (stop all updates) and upgrade all servers simultaneously in the following cases.

• For replication topologies based on merge replication
  
  
• For replication topologies based on transactional replication, which updates Subscribers, among which are one or more servers that act as both a Subscriber and a Publisher (or Distributor).

Example 1: Topology That Requires Simultaneous Upgrades

The following table includes servers that both publish and subscribe to publications that allow updates at the Subscriber. As noted earlier, you must follow the upgrade order Distributor, Publisher, Subscriber for topologies that allow updates at the Subscriber. This order requires you to upgrade Server A first for the merge publication and Server B first for the transactional publication with updating Subscribers. In this case, you must quiesce the system and upgrade the servers simultaneously.

Example 2: Topology That Allows Sequential Upgrades

In this example, you can upgrade Server A first because the read-only transactional publication allows a Subscriber to be upgraded before the Publisher/Distributor.

[Top]

3.6 Apply SP4 to Read-Only Databases or Filegroups in a Replication Topology

The following information applies only to SQL Server 2000 (64-bit) instances that are part of a replication topology.

When non-writable databases or filegroups exist, Setup displays the following message:

Setup has detected one or more databases and filegroups which are not writable.

In general, you can ignore this warning and setup will continue. However, if any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply SP4 Setup to that instance of SQL Server 2000.

Note   This message does not affect unattended installations. For more information about unattended installations, see Section 4.1 Unattended Installations.

During installation, Setup makes no distinction between non-writable databases and databases that are offline or in a suspect state. If a database or filegroup in a replication topology is non-writable, you must reapply the service pack to upgrade this database. For more information about bringing a database online, see the topic "Attaching and Detaching a Database" in SQL Server Books Online. For more information about diagnosing suspect databases, see the topic "Server and Database Troubleshooting" in SQL Server Books Online.

To apply SP4 to a read-only database

1. Make the read-only database writable by using the ALTER DATABASE statement, as follows:

   ALTER DATABASE database SET READ_WRITE

2. Repeat Step 1 for all read-only databases.
   
   
3. Apply (or reapply) the service pack.
   
   
4. If required, make the database read-only again by using ALTER DATABASE, as follows:

   ALTER DATABASE database SET READ_ONLY

To apply SP4 to a read-only filegroup

1. Make the read-only filegroup writable using ALTER DATABASE, as follows:

   ALTER DATABASE Database 
   MODIFY FILEGROUP filegroup_name READWRITE 

2. Repeat Step 1 for all read-only filegroups.
   
   
3. Apply (or reapply) the service pack.
   
   
4. Make the filegroup read-only again using ALTER DATABASE, as follows:

   ALTER DATABASE Database 
   MODIFY FILEGROUP filegroup_name READONLY

For more information about ALTER DATABASE, see the ALTER DATABASE reference topic in SQL Server Books Online. For more information about reapplying SP4, see Section 3.8 Reapply SP4.

[Top]

3.7 Uninstall SP4

To remove Database Components SP4, follow the instructions in this section.

To be able to return to pre-SP4 versions of SQL Server 2000 components, you must back up the master, msdb, and model databases before you install SP4. For more information, see Section 3.1 Prepare for SP4 Installation.

If any of the databases are involved in replication, you must disable publishing.

To disable publishing:

1. In SQL Server Enterprise Manager, expand a SQL Server group, expand a server, right-click the Replication folder, and then click Configure Publishing, Subscribers, and Distribution.
   
   
2. Click the Publication Databases tab.
   
   
3. Clear the check box for each database that is involved in replication. This allows the databases to be detached.

To revert to the pre-SP4 version of SQL Server

1. Detach all user databases. For more information, see "How to attach and detach a database (Enterprise Manager)" in SQL Server 2000 Books Online.
   
   
2. Uninstall SQL Server. In Control Panel, double-click Add/Remove Programs, and then select the instance of SQL Server that you want to uninstall.
   
   
3. Install SQL Server 2000 from the CD-ROM or the location from which you originally installed SQL Server.
   
   
4. Apply any service packs and QFE fixes that were installed prior to SP4.
   
   
5. Restore the databases master, msdb, and model from the last backup that was created before applying SP4. This automatically attaches any user databases that were attached at the time the backup was created, assuming that the location of the data files has not changed.
   
   
6. Attach any user databases that were created after the last backup of the master database.
   
   
7. Configure replication if necessary.

   Warning  When you revert to the pre-SP4 version of SQL Server 2000, all changes made to the databases master, msdb, and model since applying SP4 are lost.

[Top]

3.8 Reapplying SP4

The following information applies to all components.

In the following cases, you must reapply SP4:

• If new SQL Server 2000 (64-bit) components are added after SP4 is applied.
  
  
• If databases or filegroups that are part of a replication topology and were read-only during the initial application of SP4 are made writable.

To reapply SP4, follow the steps in Section 3.0 Service Pack Installation.

[Top]

4.0 Additional Installation Information

This section documents additional service pack installation considerations that apply only in special cases.

4.1 Unattended Installations

SP4 can be applied in unattended mode to one or more instances of SQL Server 2000 (64-bit). Unattended setup is run from the command prompt by using command line parameters to specify specific setup options. The /quiet parameter suppresses the Setup dialog boxes and allows unattended installation. The following procedures show command prompt syntax to install the SP4 package in typical unattended mode scenarios. Modify and test these examples to meet the needs of your organization.

To install SP4 in unattended mode for all qualifying instances of SQL Server 2000 (64-bit)

1. Open the command prompt window and navigate to the location of the SP4 setup files.
   
   
2. Type the following at the command prompt and press ENTER:

   setup.exe /quiet /allinstances

To install SP4 in unattended mode for the default instance of SQL Server 2000 (64-bit)

1. Open the command prompt window and navigate to the location of the SP4 setup files.
   
   
2. Type the following at the command prompt and press ENTER:

   setup.exe /quiet /instancename=MSSQLServer

To install SP4 in unattended mode for a specified SQL Server 2000 (64-bit) instance

1. Open the command prompt window and navigate to the location of the SP4 setup files.
   
   
2. Type the following at the command prompt and press ENTER:

   setup.exe /quiet /instancename=<instance_name>

   Note   <instance_name> specifies the target instance.

To install SP4 in unattended mode for all qualifying instances of SQL Server 2000 (64-bit) on a failover cluster

1. Open the command prompt window and navigate to the location of the SP4 setup files.
   
   
2. Type the following at the command prompt and press ENTER:

   setup.exe /quiet /allinstances /user=<user> /password=<password>

   user is the login used to connect to all nodes of the cluster, and password is the password for the specified login.

   Security Note  If you must run an unattended clustered setup; supply the login credentials at runtime. If you must store this password in a script file, secure the file to prevent unauthorized access.

   To install SP4 in unattended mode using SQL Server Authentication

1. Open the command prompt window and navigate to the location of the SP4 setup files.
   
   
2. Type the following at the command prompt and press ENTER:

   setup.exe /quiet /allinstances /sapwd=<sa_password>

   sa_password is the password for the sa account.

   Security Note  If you must run an unattended setup using SQL Server Authentication; supply the password for the sa login at runtime. If you must store this password in a script file, secure the file to prevent unauthorized access. You should never use a blank sa password.

To enumerate all SQL Server 2000 (64-bit) instances without installing SP4

1. Open the command prompt window and navigate to the location of the SP4 setup files.
   
   
2. Type the following at the command prompt and press Enter:

   setup.exe /reportonly

Unattended Installation Considerations

The following considerations relate to unattended installations:

• Run unattended installations using Windows Authentication whenever possible.
  
  
• During an unattended installation, if Setup detects any non-writable databases or filegroups, that information is written to the Setup log and the unattended installation continues. If any of the non-writable databases listed in the Setup log are members of a replication topology, you must make those databases writable and reapply SP4 Setup to that instance of SQL Server 2000 (64-bit). For more information about making a database writable, see Section 3.6 Apply SP4 to Read-Only Databases or Filegroups. For more information about reapplying SP4, see Section 3.8 Reapply SP4.

[Top]

5.0 Documentation Notes

This section covers issues that can occur after applying Database Components SP4 and new features that are available when you run SP4. These issues apply when running the service pack to upgrade from any earlier version of SQL Server 2000. This section does not describe all of the fixes provided in SP4. For a complete list of these fixes, see Microsoft Knowledge Base articles 888799 and 888800.

Any information relevant to SQL Server 2000 Service Pack 4 that was not available in time to be included in this Readme file will be published in Microsoft Knowledge Base article 884525.

[Top]

5.1 Database Enhancements

The following enhancements apply to instances of SQL Server 2000 on which Database Components SP4 is installed.

[Top]

5.1.1 Change to Maximum Network Packet Size

Introduced in SP4

In SP4, the maximum value for the network packet size option (set using sp_configure) is 32767. This is slightly less than half the previous maximum of 65536. During upgrade, existing values larger than 32767 will automatically be adjusted to 32767. If a script attempts to use sp_configure to set a value larger than 32767 but less than or equal to 65536, the value will also be set to 32767. Setting the network packet size to a value larger than 65536 results in an error.

[Top]

5.1.2 Optimization of Queries with Large IN Lists or Many OR Clauses

Introduced in SP4

SP4 includes a change in the behavior of the SQL Server optimizer that affects queries containing predicates with large IN lists or many OR clauses. More specifically, this change (introduced in SQL Server 2000 hotfix 789) affects queries that contain (or that can be rewritten using an equivalent expression that contains) the following:

• More than 10,000 elements in the IN list
  
  
• Two IN lists, each containing over 100 elements
  
  
• Over 10,000 disjuncts in the OR clause
  
  
• A combination of OR clauses and IN lists such that their equivalent expression will contain more than 10,000 disjuncts

When such queries are run on systems that have very large memory and a low degree of parallelism, a query plan with inferior performance may be chosen by the optimizer. To override the change in optimizer behavior, trace flag 9060 is provided in this service pack. By default, trace flag 9060 is OFF. When the trace flag is ON, SP3 behavior prior to hotfix 789 is enabled. If error 701 (insufficient system memory) is encountered when the trace flag is ON, consider rewriting the queries using temporary tables or table variables for the values in the IN lists. For numerical ranges, use BETWEEN clauses or greater than (>) or less than (<) operators. For information on using trace flags, see "Trace Flags" in SQL Server Books Online.

[Top]

5.1.3 Future Network Protocol Support

Introduced in SP4

Banyan VINES, Multiprotocol, AppleTalk, and NWLink IPX/SPX network protocols are supported in SP4. However, these protocols will not be supported in SQL Server 2005 and later releases. Please plan accordingly.

[Top]

5.2 Analysis Services Enhancements

This section discusses enhancements to SQL Server 2000 Analysis Services that are included with SP4.

[Top]

5.2.1 New Registry Entries

Introduced in SP4

Seven new registry entries have been introduced in SP4 to provide additional control over security, meta data, and memory management. For more information about using these new registry entries, see the whitepaper, "Registry Entries for Microsoft SQL Server 2000 Analysis Services," at this Microsoft Web site.

[Top]

5.3 Replication Enhancements

This section discusses enhancements to SQL Server 2000 replication that are included with SP4.

[Top]

5.3.1 Change to Security Designations of Replication ActiveX Controls

Introduced in SP4

Replication ActiveX® controls (sqlinitx.dll, sqldistx.dll, sqlmergx.dll, and replerrx.dll) are no longer designated as "safe for scripting" and "safe for initialization." The security and functional behaviors of the controls have not changed since SP3; however, the security designations have been changed to meet security standards. These changes may affect applications that invoke embedded replication ActiveX controls in a Web page.

[Top]

5.3.2 New Parameter for Articles in Merge Publications

Introduced in SP4

A new parameter, @compensate_for_errors, can be specified when calling sp_addmergearticle. The parameter specifies whether compensating actions are taken if errors (such as a constraint violation) are encountered during synchronization. When set to TRUE (the default), a change that cannot be applied at a node during synchronization leads to compensating actions that undo the change at all other nodes. In some cases this behavior is desirable, but in some cases it can be problematic; for example, one incorrectly configured Subscriber that generates an error can cause changes to be undone at the Publisher and all other Subscribers.

Specifying a value of FALSE disables these compensating actions; however, the errors are still logged and subsequent merges will continue to attempt to apply the changes. Although data in the affected rows might appear to be out of convergence, as soon as you address the error the change can be applied, and data will converge.

Note   If the source table for an article is already published in another publication, then the value of @compensate_for_errors must be the same for both articles.

[Top]

5.3.3 New Schema Option for Replicating Identity Columns in Transactional Publications

Introduced in SP4

In previous releases, identity columns in transactional publications were replicated as the base data type, such as int, without the identity property set. This approach is appropriate for applications that do not allow inserts at the Subscriber. SQL Server 2000 SP4 introduces a new schema option (0x4) for transactional publications, which is used to replicate the identity column as an identity column. This is useful in a number of cases, including bidirectional replication and using the Subscriber as a warm standby server. In these cases, inserts can occur at the Subscriber, and the inserts result in the identity column being incremented.

To specify that an identity column should be replicated as an identity column:

1. When creating the table at the Publisher, specify the NOT FOR REPLICATION option for the identity column. This ensures that only user inserts, not replication agent inserts, increment the identity column. For more information, see "CREATE TABLE" in SQL Server Books Online.
   
   
2. When adding an article with an identity column, set the option 0x4 for the @schema_option parameter of sp_addarticle. For more information about this parameter, see "sp_addarticle" in SQL Server Books Online.
   
   
3. After initializing the Subscriber, execute DBCC CHECKIDENT for each table with an identity column. This allows you to specify a starting value for inserts into the identity column at the Subscriber, so that the values inserted will not be the same as those inserted at the Publisher. For example, you could specify that inserts at the Subscriber should start at 1,000,000:

   USE Northwind
   GO
   DBCC CHECKIDENT ('Employees', RESEED, 1000000)
   GO

For more information, see DBCC CHECKIDENT in SQL Server Books Online.

[Top]

5.4 Error Reporting

This section discusses enhancements to error reporting for SQL Server 2000 (64-bit) that are included with this service pack.

[Top]

5.4.1 Error Reporting Enabled for All Instances

During SQL Server 2000 (64-bit) SP4 Setup, if any of the selected SQL Server 2000 (64-bit) instances has Error Reporting disabled, SP4 will display the Error Reporting dialog. If you enable the Error Reporting feature in SP4, Error Reporting will be enabled for all selected instances of SQL Server 2000 (64-bit).

Error Reporting can be disabled for individual SQL Server instances after SP4 Setup completes.

To turn off error reporting by using SQL Server Enterprise Manager or Analysis Manager:

1. Start SQL Server Enterprise Manager or Analysis Manager on the 32-bit computer used to administer the 64-bit instance of SQL Server 2000.
   
   
2. Connect to the 64-bit instance of SQL Server 2000.
   
   
3. Click to select the name of the server.
   
   
4. Right-click the server name, and then click Properties.
   
   
5. Click to clear the Enable Error Reporting Feature check box.

For more information on Microsoft SQL Server error reporting, see the topic "Error Reporting (64-bit)" in SQL Server 2000 (64-bit) Books Online.

[Top]

5.5 Serviceability Enhancements

Introduced in SP4

SQL Server 2000 SP4 introduces new serviceability functionality that enables you to uninstall hotfixes applied to SP4 and later versions of SQL Server 2000 running on Windows XP and Windows Server 2003. (This same functionality was available with SQL Server 2000 (64-bit), but only after the application of an additional hotfix).

[Top]