Description of Service Pack 2 for SQL Server 2000: November 9, 2001
- Service pack versions and how to remove Service Pack 2.
- How to download and how to extract Service Pack 2.
- How to install Service Pack 2.
- How to redistribute database components of Service Pack 2 client components.
- How to handle issues that affect sites on servers that are running Service Pack 2.
- 1.0 Introduction
- 2.0 Downloading and extracting SP2
- 3.0 Service pack installation
- 3.1 Back up your SQL Server databases
- 3.2 Back up your Analysis Services databases
- 3.3 Make sure that the system databases have enough free space
- 3.4 Stop services and applications before running SP2 Setup
- 3.5 Install Database Components SP2
- 3.6 Install Analysis Services SP2
- 3.7 Install Desktop Engine SP2
- 3.8 Restart services
- 3.9 Restart applications
- 3.10 Installing on a failover cluster
- 3.11 Installing on replicated servers
- 3.12 Applying SP2 to non-writable databases or filegroups
- 3.13 Uninstalling SP2
- 3.14 Reapplying SP2
- 4.0 Unattended installations
- 5.0 Documentation notes
- 5.1 Database and Desktop Engine enhancements
- 5.2 Analysis Services enhancements
- 5.3 Replication enhancements
- 5.3.1 Transactional replication UPDATE custom stored procedure
- 5.3.2 Transactional Replication UPDATE statements on unique columns
- 5.3.3 Restrictions removed from concurrent snapshot processing
- 5.3.4 Transactional replication scripting custom stored procedures
- 5.3.5 Merge replication retention-based meta data clean up
- 5.3.6 Backup and restore issues for merge replication
- 5.3.7 Restoring replicated databases from different versions of SQL Server
- 5.3.8 A new -MaxCmdsInTran parameter for Log Reader Agent
- 5.3.9 Restriction on non-unique clustered indexes
- 5.3.10 A new –maxnetworkoptimization command line argument for Snapshot Agent
- 5.4 Meta Data Services
- 5.5 Data Transformation Services
- 5.6 Enhancements to the Virtual Backup Device API
- 6.0 English Query enhancement
- 7.0 DB-Library and Embedded SQL for C
1.0 IntroductionThis release of Service Pack 2 (SP2) for Microsoft SQL Server 2000 is provided in three parts:
- Database Components SP2 provides updates for the database components of an instance of SQL Server 2000, excluding instances of the SQL Server 2000 Desktop Engine. Database Components SP2 includes upgrades to:
- The data engine.
- Database client tools and utilities such as SQL Server Enterprise Manager and osql.
- Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.
- Analysis Services SP2 provides updates for the SQL Server 2000 Analysis Services components of a SQL Server 2000 installation, including:
- Analysis Services.
- Analysis Services client components, which include Analysis Manager and the Microsoft OLE DB Provider for Analysis Services.
- Database client connectivity components, such as the Microsoft OLE DB Provider for SQL Server 2000, the SQL Server 2000 ODBC driver, and the client Net-Libraries.
- Desktop Engine SP2 provides updates for the database components of an instance of the SQL Server 2000 Desktop Engine (also referred to as MSDE 2000), including:
- The data engine.
- SQL Server 2000 sites can use Database Components SP2 to upgrade their database components without upgrading their Analysis Services components or instances of the SQL Server 2000 Desktop Engine.
- Analysis Services 2000 sites can use Analysis Services SP2 to upgrade their Analysis Services components without upgrading instances of the Desktop Engine or their database components.
- Desktop Engine sites can use Desktop Engine SP2 to upgrade instances of the Desktop Engine without upgrading Analysis Services or instances of other editions of SQL Server 2000.
NoteIf you have separate instances of both the Desktop Engine and other editions of SQL Server on the same computer, you will need to apply the Desktop Engine SP2 to the Desktop Engine 2000 instances and the Database Components SP2 to the other instances of SQL Server 2000.
NoteDesktop Engine SP2 is the only service pack part made available in Portuguese (Brazil), Swedish, and Dutch, because SQL Server 2000 Desktop Engine is the only version of SQL Server 2000 produced for those languages. The SQL Server 2000 components upgraded by Database Components SP2 or Analysis Services SP2 are not available in these languages. Portuguese (Brazil), Swedish, and Dutch users who want to apply Service Pack 2 to a version of SQL Server other than the Desktop Engine must download the Service Pack 2 files that match the language of the edition they will upgrade, such as downloading the English-language Service Pack 2 files if they are running an English-language version of SQL Server 2000. You will find download instructions below in Section 2.0 "Downloading and extracting SP2."
Details of Database Components SP2 installation
Database Components SP2 Setup automatically detects which edition of SQL Server 2000 is present on the instance of SQL Server 2000 being upgraded, and only upgrades the components that have been installed for that instance. For example, when the service pack is applied to a computer running SQL Server 2000 Standard Edition, it will not attempt to upgrade components that ship only with SQL Server 2000 Enterprise Edition.
Database Components SP2 can be applied to a single default instance or a named instance of SQL Server. If multiple instances of SQL Server 2000 need to be upgraded to SP2, you must apply SP2 to each instance. When one instance on a computer with one or more instances of SQL Server 2000 is upgraded to SP2, all of the tools will be upgraded to SP2. There are not separate copies of the tools for each instance on a computer.
When the service pack is installed, it makes changes to the system tables for maintenance reasons, and it also upgrades user and distribution databases that are members of a replication topology. Due to these changes, SP2 cannot be removed easily. To revert to the build you were running before you installed SP2, you must first uninstall the instance of SQL Server 2000. Then you must reinstall that instance of SQL Server 2000. If you were running SQL Server 2000 SP1, then you will need to reapply SP1 to the instance. For more information about uninstalling SP2, see Section 3.13 "Uninstalling SP2."
NoteTo remove SP2, you must have a backup of the master, model, and msdbdatabases, taken immediately prior to applying SP2. For more information, see Section 3.1 "Back up your SQL Server databases' and Section 3.2 "Back up your Analysis Services databases."
Additional information about SP2
A list of the fixes contained in this service pack is provided in Microsoft Knowledge Base article Q306908. Each fix listed in 306908 has a link to a Knowledge Base article describing the problem addressed by the fix. These articles are published at the Microsoft Product Support Services Knowledge Base.
Follow the links to the FIX articles to see information about each fix.
Any information relevant to SQL Server 2000 Service Pack 2 that was not available in time to be included in this Readme file will be published in Microsoft Knowledge Base article Q306909. This article is available at the Microsoft Product Support Services Knowledge Base.
If you received a QFE fix after October 3, 2001, the fix is not likely to be included in SP2. Please contact your primary support provider about a QFE fix against SP2.
1.1 Identifying the current version of SQL Server or Analysis ServicesUse the following techniques for finding out which version of SQL Server or Analysis Services you have installed.
To identify which version of SQL Server 2000 you have installed, type SELECT @@VERSION at the command prompt when using the osqlor isqlutilities or in the Query window in SQL Query Analyzer. The following table shows the relationship between the version string reported by @@VERSION and the SQL Server 2000 version number.
|@@VERSION||SQL Server 2000 version|
|8.00.194||SQL Server 2000 RTM|
|8.00.384||Database Components SP1|
|8.00.532||Database Components SP2|
Desktop Engine on Windows NT 5.0 (Build 2195: Service Pack 2) Enterprise Evaluation Edition on Windows NT 5.0 (Build 2195: Service Pack 2) Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 2) Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 2) Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2) Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2)
To identify which version of Analysis Services you have installed, follow these steps:
- From the Start menu, point to Program Files, SQL Server 2000, Analysis Services, and then click Analysis Manager.
- In the Analysis Manager tree, right-click the Analysis Servers node and select About Analysis Services.
- The following table shows which version of Analysis Services you have.
|Help About||Analysis Services version|
|8.0.194||SQL Server 2000 Analysis Services RTM|
|8.0.382||Analysis Services SP1|
|8.0.532||Analysis Services SP2|
2.0 Downloading and extracting SP2This service pack is distributed in three ways:
- On a SQL Server 2000 Service Pack 2 CD-ROMthat contains service packs for the following components:
- Database Components SP2
- Analysis Services SP2
- Desktop Engine SP2
- In three self-extracting files that can be downloaded from the Internet :
- SQL2KSP2.exe (Database Components SP2)
- SQL2KASP2.exe (Analysis Services SP2)
- SQL2KDeskSP2.exe (Desktop Engine SP2)
- On a SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM(Desktop Engine only)
2.1 Database and Analysis Services SP2 filesWhen downloading and extracting Database Components or Analysis Services SP2 installation files from the Internet, please use the following guidelines.
Download one of the files listed in section 2.0, and place the self-extracting file into a folder on the computer that is running the instance of SQL Server 2000 on which you are installing this service pack. From that folder, execute the file. The self-extraction program will prompt you for the name of the folder into which you want the service pack files placed.
On the local drive where you have downloaded the service pack installation files, you will need free space approximately three times the size of the self-extracting file. This includes space to store the file, space for storing the extracted service pack files, and temporary working space required by the self-extraction program itself.
You can rename the chosen folder after extracting the components; however, make sure the directory name does not contain blank spaces. You can use the same target folder for each of the above files; they will not overwrite or interfere with each other.
The self-extracting files are used to build a set of directories and files on your computer that are the same as those present on the SP2 CD-ROM. If you download SP2 you must extract the files to build the service pack directories before you can run SQL Server Setup.
For Database Components and Analysis Services, the setup process is the same for both the SP2 CD-ROM and the extracted SP2 files.
NoteSome of the files in service packs are system files, so you cannot view them unless you follow this procedure: In Windows Explorer, on the View menu, click Options, click the View tab, and then select the Show all files check box.
2.2 Desktop Engine SP2 fileWhen downloading and extracting the Desktop Engine SP2 installation file from the Internet, please use the following guidelines.
Download and extract the Desktop Engine SP2 file as described above for the Database Components and Analysis Services SP2 files, with the following exceptions.
If you download the Desktop Engine SP2 file from the Internet, you can apply the service pack only to instances of the Desktop Engine that were installed from sqlrun01.msi. If you attempt to apply the service pack to instances that were created using sqlrun02.msi – sqlrun16.msi from the Setup.exe file that was downloaded from the Internet, you will receive one of the following errors:
3.0 Service pack installationTo install SP2, follow the installation instructions below. Not all steps are required, depending on which of the following SQL Server 2000 components or configurations to which the service pack is being applied:
- Database Server components
- Database Client components
- Analysis Services
- Desktop Engine
- Failover cluster
- Replication server
NoteThe service pack is language specific. Apply the service pack with the same language as the SQL Server component that you are upgrading.
NoteBefore you install SP2 on the French version of Windows NT 4.0, read and follow the instructions in Knowledge Base article 259484. You can access the article from the Microsoft Product Support Services Knowledge Base.
Before starting an installation
Changed from SP1
The following applies to all component installations except Analysis Services:
SP2 Setup does not upgrade user databases except for those user databases that are actively involved in replication topologies. Databases that are not involved in replication topologies do not have any dependencies on SP2. For example:
- You can restore a user database backup from an instance of SQL Server 2000 or SQL Server 2000 SP1 to an instance of SQL Server 2000.
- You can restore a user database backup from an instance of SQL Server 2000 SP2 to an instance of SQL Server 2000 SP2, to an instance of SQL Server 2000 SP1, or to an instance of SQL Server 2000.
- You can detach a user database from an instance of SQL Server 2000 or SQL Server 2000 SP1 and attach it to an instance of SQL Server 2000 SP2.
- You can detach a user database from an instance of SQL Server 2000 SP2 and attach it to an instance of SQL Server 2000 or SQL Server 2000 SP1.
- You can engage in log shipping between instances of SQL Server 2000, SQL Server 2000 SP1, and SQL Server 2000 SP2. SP2 Setup does upgrade user databases that are members of a replication topology.
If SP2 Setup detects any user databases or filegroups that are not writable, it:
- Applies the SP2 replication updates to all writable user databases.
- Writes a list of the non-writable databases to the Setup log, located at winnt\sqlsp.log.
- Displays the following warning message: Setup has detected one or more databases and filegroups which are not writable.
NoteThis message will not affect unattended installations. To learn more about unattended installations, see Section 4.0 "Unattended installations."
For more information about making a database writable, see Section 3.12 "Applying SP2 to non-writable databases or filegroups."To learn more about reapplying SP2, see Section 3.14 "Reapplying SP2."
NoteDuring installation, Setup makes no distinction between read-only databases and databases that are offline or suspect. If a replication database or filegroup is in any of these conditions during setup and is involved in a replication topology, the service pack must be reapplied after the database is made writable.
NoteSince non-writable databases will no longer cause Setup to fail, you no longer need to remove log shipping before upgrading to SP2.
Preparing for a Systems Management Server distributed installation
SQL Server 2000 Service Pack 2 cannot be installed remotely. However, you can use Microsoft Systems Management Server to install SP2 automatically on multiple computers running Microsoft Windows NT Server 4.0. To do so, you must use a package definition file (Smssql2ksp2.pdf file) that automates the creation of a SQL Server package in Systems Management Server. The SQL Server package can then be distributed and installed on computers running Systems Management Server. The Sms2kdef.bat file is a batch file that detects the platform of the computer and runs the appropriate version of the Setup program.
Systems Management Server cannot be used to install the Desktop Engine SP2.
3.1 Back up your SQL Server databasesThe following applies to all component installations except Database Client Components:
Before installing either Database Components SP2 or Desktop Engine SP2, back up the master, msdb, and modeldatabases. Installing the service pack makes modifications to the master, msdb, and modeldatabases, which makes them incompatible with pre-SP2 versions of SQL Server. These backups are required if you decide to reinstall SQL Server 2000 without SP2.
It is also prudent to backup your user databases, although SP2 will only perform updates in databases that are members of replication topologies.
3.2 Back up your Analysis Services databasesThe following applies only to Analysis Services:
Before installing Analysis Services SP2, back up your Analysis Services databases by making a copy of your Microsoft Analysis Services\Data folder, which is installed by default under the C:\Program Files folder. If you have not migrated your Analysis Services repository to SQL Server, make a backup copy of the file Msmdrep.mdb, located in the Microsoft Analysis Services\bin folder. You also can save your Analysis server registry entries by running Regedit.exeand by using the Export Registry Fileitem on the Registrymenu to export the key
3.3 Make sure that the system databases have enough free spaceThe following applies to all component installations except Database Client Components and Analysis Services:
If the autogrowoption has not been selected for the masterand msdbdatabases, the databases must have at least 500 kilobytes (KB) of free space. To verify that they have this space, run the sp_spaceusedsystem stored procedure for the masteror msdbdatabase. 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 autogrowoption has been selected for the masterand msdbdatabases, and there is sufficient room on the drives, you can skip this step.
To verify that this option has been selected in SQL Server 2000, open SQL Server Enterprise Manager, right-click the icon for the database, and click Properties. Verify that the Automatically grow filecheck box is selected.
To verify that this option has been selected in Desktop Engine, issue the following SQL statements:
- sp_helpdb master
- sp_helpdb msdb
3.4 Stop services and applications before running SP2 SetupThe following applies to all components:
You can apply SP2 without shutting down services. If you do not shut down services, you will be prompted to reboot when Setup is complete. Without rebooting, the services will fail to start.
You can apply SP2 without restarting your computer by stopping the following services and applications before applying the service pack:
- Microsoft Distributed Transaction Coordinator (MS DTC) and the Microsoft Search and MSSQLServerOLAPService services.
- MSSQLServer and SQLServerAgent services for the instance being upgraded (for example, MSSQL$NamedInstance).
- Microsoft Component Services, Microsoft Message Queuing, and Microsoft COM Transaction Integrator.
- All applications, including Control Panel. This step is recommended, but not required.
3.5 Install Database Components SP2The following applies to all component installations except Desktop Engine and Analysis Services:
Run the Setup.bat script from either of these locations:
- The directory containing the extracted service pack files from SQL2KSP2.exe.
- The service pack directory on the SQL Server 2000 SP2 CD-ROM.
The Setup program then:
- Replaces the existing SQL Server 2000 or Desktop Engine files with the SP2 files.
- Runs several Transact-SQL script files to update system stored procedures.
- Displays an Authentication Mode dialog box if it detects that the installation is using Mixed Mode Authentication with a blank password for the sa login. Leaving the sa login password blank provides users with easy administrative access to SQL Server or Desktop Engine and is not recommended; help protect your systems by enforcing an sa password or by using Windows Authentication. To learn more about this dialog, see Authentication Mode Dialog later in this Section.
- Displays a dialog box if it detects that any databases or filegroups are not writable. At this point, if any of these databases or filegroups are part of a replication topology you can either:
- Stop the setup process, make these objects writable and restart the setup.
- Continue with the current SP2 setup and reapply SP2 at a later time after making these objects writable.
The Authentication Modedialog box does not default to the current settings for the installation. The dialog box defaults are:
- On computers running Microsoft Windows 98 or Windows Millennium Edition, the default setting is Mixed Mode Authentication (the only authentication mode supported on these operating systems). The dialog box prompts you to specify a password for the sa login. If you specify a password, the Setup program will change the sa login password. If you leave the password blank, the Setup program will connect and not change the password.
- On computers running Windows NT 4.0 or Windows 2000, the default setting is Windows Authentication. Use the dialog box to switch to Windows Authentication Mode or Mixed Mode with an sa login password that is not blank.
The Setup program places a record of the actions it performed in the Sqlsp.log file in the Windows directory of the computer on which it was run. If multiple instances are upgraded, only the most recent upgrade will be recorded in this log.
3.6 Install Analysis Services SP2The following applies only to Analysis Services:
To install Analysis Services SP2, run Setup.exe from either of the following locations:
- The Msolap\Install subdirectory in the directory containing the extracted Analysis Services SP2 file from SQL2KASP2.exe.
- The Msolap\Install directory on the SP2 CD-ROM.
3.7 Install Desktop Engine SP2The following applies only to Desktop Engine:
The service pack for the SQL Server 2000 Desktop Engine (also referred to as MSDE 2000) is intended for developers who create redistributable applications that use the Desktop Engine. If you are running an application that uses the Desktop Engine, contact your software provider for information about upgrading instances of the Desktop Engine installed by these applications. For more information, see the topic "Distributing the SQL Server 2000 Desktop Engine" in SQL Server Books Online.
The service pack for the Desktop Engine is provided in three forms:
- SQL Server 2000 Service Pack 2 CD-ROM: The CD-ROM includes all of the files required to upgrade existing instances of the Desktop Engine. The maximum number of instances supported for the Desktop Engine is 16. This CD-ROM does not include the merge modules or .msi files that are required to install a new instance of the Desktop Engine.
- SQL2KdeskSP2.exe:This file includes an upgrade for a single instance of the Desktop Engine that was created with sqlrun01.msi. The download cannot be used to upgrade instances of the Desktop Engine that were created using any of the other .msi files (sqlrun02.msi through sqlrun16.msi). The file does not include the merge modules or .msi files that are required to install a new instance of the Desktop Engine. For more information, see Section 2.2 "Desktop Engine SP2 File."
NoteTo learn how to determine which .msi file was used to create an instance of MSDE, please refer to the Knowledge Base article Q311762. You can access the article from the Microsoft Product Support Services Knowledge Base.
- SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM: This CD-ROM is available by request to MSDN Universal subscribers through Microsoft Product Support. It includes all of the files required to upgrade existing instances of the Desktop Engine. In addition, it contains the merge modules and .msi files that are required to install a new instance of the Desktop Engine. This CD-ROM is provided for developers who create applications that require the Desktop Engine. For more information, see "Distributing SQL Server Applications" in SQL Server Books Online.
Note The SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM does not include the Service Pack for SQL Server or Analysis Services.
Use Setup.exe to apply this service pack. The Setup.exe in Desktop Engine SP2 accepts the same parameters as the SQL Server 2000 Desktop Engine Setup.exe released with SQL Server 2000. The reference material for Setup.exe can be found in:
- The topic "SQL Server 2000 Desktop Engine Setup" in SQL Server 2000 Books Online.
- The following sections of the original SQL Server 2000 Readme.txt file:
- The original installation of the Desktop Engine was notinstalled from a CD-ROM.
- If you create your own CD-ROM for installing SP2, the volume label on the CD-ROM must be SQL2KSP2. If you are an Independent Software Vendor (ISV) creating a CD-ROM for distributing the Desktop Engine as a component of your application, the CD-ROM volume label must be identical to the VolumeLabel property in the Media table of the Windows Installer Package (*.MSI).
If you need to apply Service Pack 2 directly from the CD-ROM, you must first upgrade Windows Installer to version 2.0.2600.0 or later. This process will require that you reboot your computer.
To upgrade Windows Installer:
- Make a note of the complete path of the original installation.
- Run InstMsi20.exe.
- Enter the complete path of the original installation in the text box.
- The MSDE subdirectory that is in the directory containing the extracted service pack files from SQL2KDeskSP2.exe.
- The location on your hard drive or the network share to which you copied the MSDE subdirectory from the SQL Server 2000 SP2 CD-ROM.
Applying patch packages
The patch packages distributed in this Service Pack will upgrade the default numbered instances of MSDE (01-16) included on the SQL Server 2000 CD. To upgrade an instance of MSDE, you must apply its corresponding patch. For example, to upgrade an instance originally installed from SqlRun03.msi, you must use SQLRun03.msp.
NoteBecause these files are very large, the downloadable version of this Service Pack contains only one patch package: SQLRun01.msp. The other fifteen patch packages are on the SQL Server 2000 Service Pack 2 CD.
Use Setup.exe from the command line to apply a patch package to an installed instance of MSDE. If you know what installation package was used to install your instance of Desktop Engine, you can specify that Setup.exe use the corresponding patch with the /p option. For example:
Setup /p SQLRun03.msp
- All of the .msp files are located in a folder named Setup directly under the folder containing Setup.exe. This requires the SQL Server 2000 Service Pack 2 CD.
- When the instance to be upgraded is not the default instance, its instance name is specified with the INSTANCENAME variable.
Setup /p SQLRUN INSTANCENAME=MyInstance
NoteDo not use opening and closing quotation marks around the instance name.
If Setup cannot find the correct patch package, you will get an error message.
NoteWhen using setup.exe to install MSDE SP2 on a computer running Windows 98 or Windows Millennium Edition, you must use the UPGRADEUSER switch to specify a login that is a member of the sysadmin fixed server role. For more information on UPGRADEUSER, see Section 3.1.23 of the original SQL Server 2000 Readme.txt file.
Redistributing the service pack
We recommend that independent software vendors (ISVs) who wish to redistribute this service pack do so as follows:
- Install the Service Pack on an instance of Desktop Engine configured to match the one distributed with your software.
- Generate your own patch package based on the differences between the old and new installations.
- Distribute your custom patch to your customers.
NoteYou may redistribute a complete copy of SP2.
On the SQL Server 2000 Desktop Engine Service Pack 2 CD-ROM, the merge modules and installation files are located in \MSDE. For a complete listing, see "Using SQL Server Desktop Engine Merge Modules" in SQL Server Books Online.
3.8 Restart servicesThe following applies to all components:
When the Setup program completes, it may prompt you to restart the system. After the system restarts (or after the Setup program completes without requesting a restart), use the Services application in Control Panel to make sure MS DTC and the Microsoft Search, MSSQLServer, MSSQLServerOLAPService, and SQLServerAgent services or their instance-specific equivalents are running. Back up the upgraded master and msdb databases.
3.9 Restart applicationsThe following applies to all components:
Restart the applications you closed before running the service pack setup.
3.10 Installing on a failover clusterThe following applies only to SQL Server 2000 components that are part of a failover cluster:
To install this service pack on a failover cluster:
- Run this service pack from the node that owns the group containing the virtual server you are going to upgrade. This will install the service pack files on all failover cluster nodes.
- In the Setup dialog box, enter the name of the virtual server you are upgrading.
- Keep all nodes of the cluster online during setup. This ensures that the upgrade is applied to each node of the cluster.
If you are upgrading a default (non-clustered) instance of SQL Server to a virtual server, you must first perform the upgrade from a default instance to a virtual instance, and then apply SQL Server 2000 SP2. For more information about upgrading, see "How to upgrade from a default instance to a default clustered instance of SQL Server 2000 (Setup)" in SQL Server Books Online.
If you need to rebuild a node in the failover cluster, you should:
- 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 Books Online.
- Run the original SQL Server 2000 Setup to add the node back to the failover cluster.
- Run SQL Server 2000 SP2 Setup.
3.11 Installing on replicated serversThe following applies only to SQL Server 2000 components that are part of a replication topology:
Deploy this service pack across replicated servers in this sequence:
- Distributor (if separate from the Publisher)
In merge replication, the distribution database is used only to store agent history. Typically, the distribution database resides on the same computer as the published database. However, it is also possible to have a remote distribution database for merge replication at sites that want to centralize agent history logging.
You may not be able to upgrade all the servers in a replication topology immediately; however, replication operations are generally unaffected between servers running instances of SQL Server 2000 and this service pack. The exceptions to this are covered in Section 5.3.7 "Restoring replicated databases from different versions of SQL Server."
NoteSP2 Setup does upgrade user databases that are members of a replication topology. If any of the databases that are members of a replication topology are non-writable, to apply SP2 to those databases you must make them writable and reapply SP2 Setup. For more information about making a database writable, see Section 3.12 "Applying SP2 to non-writable databases or filegroups." To learn more about reapplying SP2, see Section 3.14 "Reapplying SP2."
An existing backup scheme that accounts for replication will allow restoring a database to a known point after the SP2 upgrade in case of a failure. After applying SP2, a log or full database backup is recommended for any user database involved in a replication topology. This way, in case of a failure of a replication database, SP2 will not have to be reapplied after the database has been restored.
3.12 Applying SP2 to non-writable databases or filegroupsThe following applies only to SQL Server 2000 components that are part of a replication topology:
When non-writable databases or filegroups exist, the Setup program will display the following message:
NoteThis message will not affect unattended installations. To learn more about unattended installations, see Section 4.0 Unattended Installations.
NoteDuring installation, Setup makes no distinction between non-writable databases and databases that are offline or suspect. If a database or filegroup is in any of these conditions during Setup, the service pack must be reapplied. For more information about bringing a database online, see "Attaching and Detaching a Database" in SQL Server Books Online, and for more information about diagnosing suspect databases, see "Server and Database Troubleshooting" in SQL Server Books Online.
To apply SP2 to a read-only database:
- Make a read-only database writable using ALTER DATABASE as follows:
ALTER DATABASE databaseSET READ_WRITE
- Repeat Step 2 for all read-only databases.
- Apply (or reapply) the service pack.
- If required, make the database read-only again using ALTER DATABASE as follows:
ALTER DATABASE databaseSET READ_ONLY
- Make the read-only filegroup writable using ALTER DATABASE as follows:
ALTER DATABASE DatabaseMODIFY FILEGROUP filegroup_name READWRITE
- Repeat Step 1 for all read-only filegroups.
- Apply (or reapply) the service pack.
- Make the filegroup read-only again using ALTER DATABASE as follows:
ALTER DATABASE DatabaseMODIFY FILEGROUP filegroup_nameREADONLY
3.13 Uninstalling SP2The following applies to all components except Analysis Services:
To revert to the pre-SP2 version of SQL Server 2000 components, you must have taken a backup of the master, msdb, and modeldatabases prior to installing SP2. For more information, see Section 3.1 "Back up your SQL Server databases."
NoteIf any of the databases are involved in replication, you must disable publishing.
- To disable publishing: 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.
- Select the Publication Databases tab.
- Clear the check box for each database that is involved in replication. This will allow the databases to be detached.
- Detach all user databases. For more information, see "How to attach and detach a database (Enterprise Manager)" in SQL Server Books Online.
- Uninstall SQL Server. In Control Panel, double-click Add/Remove Programs, and then select the instance of SQL Server that you want to uninstall.
- Install SQL Server 2000 from the CD-ROM or the location from which you originally installed SQL Server.
- Restore the databases master, msdb, and model from the last backup that was created before applying SP2. This automatically attaches any user databases that were attached at the time the backup was created.
- Attach any user databases that were created after the last backup of the master database.
- Configure replication if necessary.
Warning When you revert to the pre-SP2 version of SQL Server, all changes made to the databases master, msdb, and model since applying SP2 are lost.
3.14 Reapplying SP2The following applies to all components:
In the following cases, it will be necessary to reapply SQL Server 2000 SP2:
- When new SQL Server 2000 components have been added after SP2 was applied.
- When databases or filegroups that are part of a replication topology and were read-only during the initial application of the service pack are made writable.
4.0 Unattended installationsDatabase Components SP2 can be applied to an instance of SQL Server 2000 running on a computer in unattended mode. The Database Components SP2 CD-ROM contains ISS files that can be used to perform unattended SP2 setups. These files are located in the root directory on the CD-ROM and can be used to perform different types of installations:
- sql2kdef.iss is the unattended setup file used to apply Database Components SP2 to a default instance of SQL Server 2000 running on the Windows 2000 or Windows NT 4.0 operating system. The corresponding batch file to use is Sql2kdef.bat.
- sql2knm.iss is the setup file used to apply Database Components SP2 to a named instance of SQL Server 2000 running on the Windows 2000 or Windows NT 4.0 operating system. You must change the instance name in the ISS file to identify the instance that is being upgraded.
- sql2ktls.iss is the setup file used to apply Database Components SP2 to a tools-only installation. This file can be used for installation without any modifications.
- sql2k9x.iss is the setup file used to apply Database Components SP2 on a computer running the Windows Millennium Edition or Windows 98 operating system. The instance name will need to be changed to identify the instance being upgraded.
- sql2kcli.iss is the setup file used to apply Database Components SP2 to a client components installation on a computer running the Windows 95 operating system.
- msolap.iss is the setup file that stores the list of parameters for running an unattended setup of Analysis Services SP2.
4.1 Redistributing SP2 Data Access ComponentsThis service pack includes a self-extracting file, Sqlredis.exe, and a version of Redist.txt. These files are in the Database Components SP2. By default, when Sqlredis.exe is executed, it:
- Executes the Mdac_typ.exe from Microsoft Data Access Components (MDAC) 2.62. This installs the MDAC 2.62 core components and the versions of the SQL Server and Desktop Engine client connectivity components that ship with this service pack.
- Installs Microsoft Jet ODBC drivers and connectivity components.
5.0 Documentation notesThis section covers issues that may occur as well as new features that will be available when you run this service pack. These issues apply to running the service pack to upgrade to SQL Server 2000 and SQL Server 2000 SP1 and are not the result of fixes contained in this service pack.
The Analysis Services and Meta Data Services segments in this section will not apply to Desktop Engine only installations.
5.1 Database and Desktop Engine enhancementsThe following issues apply to SQL Server 2000 instances on which the Database Components SP2 has been installed, and Desktop Engine instances on which Desktop Engine SP2 has been installed.
5.1.1 Installing new instances of the Desktop Engine
Introduced in SP1
Follow these procedures to install a new instance of the Desktop Engine after applying SP2.
- In the \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033 directory, rename the following files to *.rdl:
- In the \Program Files\Microsoft SQL Server\80\Tools\Binn directory, rename the following files to *.dld:
- Install the Desktop Engine without SP2.
- Rename the semnt.rdl and sqlsvc.rdl files in the \Program Files\Microsoft SQL Server\80\Tools\Binn\Resources\1033 directory to *.rll.
- Rename the semnt.dld, sqlsvc.dld, sqlresld.dld files in the \Program Files\Microsoft SQL Server\80\Tools\Binn directory to *.dll.
5.1.2 Using Chinese, Japanese, or Korean characters with Database Components SP2
Introduced in SP1
If you install Database Components SP2 on a server running the Windows NT 4.0 or Windows 98 operating system and later upgrade to Windows 2000, the Windows 2000 upgrade replaces certain system files. These system files are necessary for sorting Chinese, Japanese, or Korean characters. If you use Chinese, Japanese, or Korean characters in your SQL Server databases, rerun the version of Sqlredis.exe that came with SP2 after you perform the upgrade to Windows 2000. For more information about executing Sqlredis.exe, see Section 4.1 "Redistributing SP2 Data Access Components."
NoteYou do not have to reapply Sqlredis.exe on client computers or on servers that do not have databases containing Chinese, Japanese, or Korean characters.
5.1.3 Hash teams removed
Introduced in SP1
Hash teams have been removed. Because of certain enhancements to SQL Server 2000, hash teams no longer produce the performance benefits they offered in SQL Server version 7.0. In addition, removing hash teams makes SQL Server 2000 more stable.
Therefore, the query optimizer no longer generates query plans using the hash teams.
In rare cases, the removal of hash teams may cause the query to process more slowly. Analyze such queries to see whether creating more suitable indexes will return query performance to its previous level.
5.1.4 Affinity mask switches added
Introduced in SP1
Two affinity mask switches have been added to this service pack.
Affinity mask I/O switch
With this service pack, you can specify which CPUs will be used to run threads for disk I/O operations. This switch must be used in conjunction with the affinity maskoption. For more information, see the 298402in Microsoft Product Support Services Knowledge Base.
Affinity mask Connection switch
With this service pack, you can configure systems enabled for Virtual Interface Architecture (VIA) to bind the SQL Server connections from certain network cards to a processor or set of processors. This switch must be used in conjunction with the affinity maskoption. For more information, see the 299641 in Microsoft Product Support Services Knowledge Base.
5.1.5 SQL Server Agent logs account information
Introduced in SP2
The SQL Server Agent Job History now records the Windows account under which each job step was run. This information helps administrators diagnose security issues with scheduled jobs, including scheduled jobs defined for replication and Data Transformation Services tasks.
5.1.6 Filtered indexed view
Introduced in SP2
If you have encountered SQL Server 2000 bug 355069 as documented in Microsoft Knowledge Base Article 306467, this service pack will only prevent future occurrences of unexpected results due to data modifications. So in addition to applying this fix, all indexes based on views with filter conditions need to be re-created. For more information, see the 306467in Microsoft Product Support Services Knowledge Base.
5.2 Analysis Services enhancementsAnalysis Services enhancements in SP2 include:
- Remote partitions.
- Updated Analysis Services redistributable client setup.
- Support for third-party data mining algorithm providers.
- Ability to install Analysis Services on a computer with updated client files.
5.2.1 Remote partitions
Introduced in SP1
When a remote partition is created on a local server that has SP1 or later version installed on it, the remote server must use a domain user account that has full access permissions to the parent cube on the local server. Any user account that is a member of the OLAP Administrators group on the local server will have full access permissions.
In addition, if the local server has SP1 installed on it, the remote server also must have SP1 installed on it in order to create or administer remote partitions.
5.2.2 Updated Analysis Services redistributable client setup
Introduced in SP1
The Analysis Services SP1 and later versions includes updated versions of the following client redistributable setup programs:
NotePTSFull.exe includes MDAC; PTSLite.exe does not.
Use these updated client setup programs in your applications to prevent or solve client setup issues that you may encounter when using Analysis Services and Microsoft Office XP.
NoteWhen using Analysis Services with Office XP, upgrading the client is highly recommended.
5.2.3 Support enabled for third-party data mining algorithm providers
Introduced in SP1
The Analysis Services SP1 and later includes support for the addition of third-party data mining algorithm providers. For more information about developing a data mining algorithm provider, see the "Third Party Data Mining Providers" white paper, and the OLE DB for Data Mining Resource Kit, which includes the code for a sample data mining algorithm provider, at the Microsoft Data Access and Storage Development Center.
5.2.4 Installing Analysis Services on a computer with updated client files
Introduced in SP1
If you install SQL Server 2000 Analysis Services on a computer that contains updated client files, such as SQL Server 2000 SP1 or Office XP, you must apply Analysis Services SP1 or later to ensure that the client works properly and that you can browse cubes.
5.3 Replication enhancementsReplication enhancements in SP2 include:
- Custom stored procedure for transactional replication updates
- Transactional replication updates on unique columns
- Restrictions removed from concurrent snapshot processing
- Ability to script custom stored procedures in transactional replication
- Merge replication retention-based meta data clean up
- Restoring replicated databases from different versions of SQL Server
- New -MaxCmdsInTran parameter for Log Reader Agent
5.3.1 Transactional replication UPDATE custom stored procedure
Introduced in SP1
During transactional replication setup, custom stored procedures for insert, delete and update actions are created in the subscription database. Regardless of how many columns are affected by an UPDATE statement, the update custom stored procedure will update all the columns in the subscription table. Any column that has not changed is simply reset to the same values that existed before the update. Typically, this action causes no problems. However, if any of these columns are indexed, this resetting can become expensive.
If you are using transactional replication and have several indexes on the subscription table, and only a few column values are changing because of updates, the index maintenance overhead may become a limiting performance factor when changes are applied at the Subscriber. For example, a subscription database that is being used for reporting purposes may have many more indexes than the publication database. Dynamically building the UPDATE statement at runtime may improve performance. The update will include only the columns that have changed, thus creating an optimal UPDATE string.
This service pack includes a new stored procedure, sp_scriptdynamicupdproc, which generates a custom stored procedure you can use at the Subscriber to dynamically build the update statement at runtime. However, extra processing will be incurred at runtime in order for the dynamic UPDATE statement to be built.
Generates the CREATE PROCEDURE statement that creates a dynamic update stored procedure. The UPDATE statement within the custom stored procedure is built dynamically, based on the MCALL syntax that indicates which columns to change. Use this stored procedure if the number of indexes on the subscribing table is growing and the number of columns being changed is small. This stored procedure is run at the Publisher on the publication database.
sp_scriptdynamicupdproc [ @ artid =] artid
[@ artid =] artidIs the article ID. artidis an int, with no default.
Returns a result set that consists of a single nvarchar(4000)column. The result set forms the complete CREATE PROCEDURE statement used to create the custom stored procedure.
sp_scriptdynamicupdproc is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself, which usually causes no problems. If the column is indexed, extra processing occurs. By contrast, this stored procedure uses a dynamic approach: it includes only the columns that have changed, which provides an optimal UPDATE string. However, extra processing is incurred at runtime when the dynamic UPDATE statement is built. It is recommended that you test both the dynamic stored procedure approach and the static default approach, and then choose the optimal solution for your particular needs.
PermissionsMembers of the publicrole can execute sp_scriptdynamicupdproc.
This example creates an article (with artidset to 1) on the authorstable in the pubsdatabase, and specifies that the UPDATE statement is the custom stored procedure to execute:
EXEC sp_scriptdynamicupdproc @artid = '1' The statement returns: create procedure [sp_mupd_authors] @c1 varchar(11),@c2 varchar(40),@c3 varchar(20),@c4 char(12),@c5 varchar(40),@c6 varchar(20), @c7 char(2),@c8 char(5),@c9 bit,@pkc1 varchar(11),@bitmap binary(2) as declare @stmt nvarchar(4000), @spacer nvarchar(1) select @spacer =N'' select @stmt = N'update [authors] set ' if substring(@bitmap,1,1) & 2 = 2 begin select @stmt = @stmt + @spacer + N'[au_lname]' + N'=@2' select @spacer = N',' end if substring(@bitmap,1,1) & 4 = 4 begin select @stmt = @stmt + @spacer + N'[au_fname]' + N'=@3' select @spacer = N',' end if substring(@bitmap,1,1) & 8 = 8 begin select @stmt = @stmt + @spacer + N'[phone]' + N'=@4' select @spacer = N',' end if substring(@bitmap,1,1) & 16 = 16 begin select @stmt = @stmt + @spacer + N'[address]' + N'=@5' select @spacer = N',' end if substring(@bitmap,1,1) & 32 = 32 begin select @stmt = @stmt + @spacer + N'[city]' + N'=@6' select @spacer = N',' end if substring(@bitmap,1,1) & 64 = 64 begin select @stmt = @stmt + @spacer + N'[state]' + N'=@7' select @spacer = N',' end if substring(@bitmap,1,1) & 128 = 128 begin select @stmt = @stmt + @spacer + N'[zip]' + N'=@8' select @spacer = N',' end if substring(@bitmap,2,1) & 1 = 1 begin select @stmt = @stmt + @spacer + N'[contract]' + N'=@9' select @spacer = N',' end select @stmt = @stmt + N' where [au_id] = @1' exec sp_executesql @stmt, N' @1 varchar(11),@2 varchar(40),@3 varchar(20),@4 char(12),@5 varchar(40), @6 varchar(20),@7 char(2),@8 char(5),@9 bit',@pkc1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598
5.3.2 Transactional replication UPDATE statements on unique columns
Introduced in SP1
In transactional replication, UPDATE statements usually are replicated as updates. But if the update changes any column that is part of a unique index, clustered index, or expression used as a unique constraint, the update is performed as a DELETE statement followed by an INSERT statement at the Subscriber. This is done because this type of update could affect multiple rows and there is a chance for a uniqueness violation if updates were delivered row by row.
However, if the update affects only one row, there is no chance for a uniqueness violation. Therefore, trace flag 8207 has been added to this service pack to allow updates to any unique column that affect only one row to be replicated as UPDATE statements. This optimization has been added specifically for applications that install user-defined UPDATE triggers at the Subscriber and require these triggers to fire for updates that affect only one row on a unique column.
To use trace flag 8207, turn it on from the command prompt (sqlservr.exe -T8207) or at runtime using DBCC TRACEON(8207, -1) before the Log Reader Agent is started.
ImportantTypically, trace flag 8207 is used with read-only transactional replication. Do not use this trace flag with updatable subscriptions if the primary key UPDATE can occur at the Subscriber.
5.3.3 Restrictions removed from concurrent snapshot processing
Introduced in SP1
In SQL Server 2000, concurrent snapshot processing was not recommended if the publishing table had a unique index that was not the primary key or the clustering key. If data modifications were made to the clustering key while a concurrent snapshot was being generated, replication could fail with a duplicate key error when applying the concurrent snapshot to a Subscriber. In this service pack, there are no longer any restrictions on using concurrent snapshot processing.
5.3.4 Transactional replication scripting custom stored procedures
Introduced in SP1
When setting up nosync subscriptions (that is, subscriptions that do not receive the initial snapshot), the custom stored procedures for INSERT, UPDATE and DELETE statements need to be created manually. Typically, these statements are created at the Subscriber when the initial snapshot is delivered. A new stored procedure, sp_scriptpublicationcustomprocs, has been added to generate scripts for the custom stored procedures at the publication level. This new functionality may make it easier to set up nosync subscriptions.
In a publication in which the option to auto-generate a custom procedure schema is enabled, this stored procedure scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles. sp_scriptpublicationcustomprocs is particularly useful for setting up subscriptions for which the snapshot is applied manually.
sp_scriptpublicationcustomprocs [@publication] = publication_name
Is the name of the publication. publication_nameis a sysname with no default.
Return code values
0 (success) or 1 (failure)
Returns a result set that consists of a single nvarchar(4000) column. The result set forms the complete CREATE PROCEDURE statement necessary to create the custom stored procedure.
Custom procedures are not scripted for articles without the auto-generate custom procedure (0x2) schema option.
Execute permission is granted to public; a procedural security check is performed inside this stored procedure to restrict access to members of the sysadmin fixed server role and db_owner fixed database role in current database.
This example generates a script of the custom stored procedures in a publication named Northwind.
exec Northwind.dbo.sp_scriptpublicationcustomprocs @publication = N'Northwind'
5.3.5 Merge replication retention-based meta data clean up
Introduced in SP1
When there is a large amount of meta data in merge replication system tables, cleaning up the meta data improves performance. Prior to SQL Server 2000 SP1, meta data could be cleaned up only by running sp_mergecleanupmetadata. However, SQL Server 2000 SP1 and later includes retention-based meta data cleanup, which means that meta data can be automatically deleted from the following system tables:
- Any before image tables, if present
Retention-based meta data cleanup occurs as follows:
- If the –MetadataRetentionCleanup Merge Agent parameter is set to 1, as it is by default, the Merge Agent cleans up the Subscriber and the Publisher that are involved in the merge.
- If the –MetadataRetentionCleanup parameter is set to 0, automatic cleanup does not occur. In this case, manually initiate retention-based meta data cleanup by executing sp_mergemetadataretentioncleanup. This stored procedure must be executed at every Publisher and Subscriber that should be cleaned up. It is recommended, but not required, that the Publisher and Subscribers be cleaned up at similar points in time (see Preventing False Conflicts later in this Section).
NoteThe –MetadataRetentionCleanup 1 parameter is now part of all Merge Agent profiles that ship with SQL Server 2000 SP1 and later. If you upgrade a server to SP1 or SP2 and then add merge replication, the Merge Agent profile is automatically updated to include this parameter. If you upgrade to SP1 or SP2 a server that already has merge replication enabled, the Merge Agent profile is not automatically updated; update the profile by executing sp_add_agent_parameter (see "Additional parameter for sp_add_agent_parameter,"described later in this section).
Additional parameter for sp_add_agent_parameter
The system procedure sp_add_agent_parameter now has a MetadataRetentionCleanup parameter, which allows you to add or remove meta data retention cleanup from Merge Agent profiles. A value of 1 indicates that the profile should include cleanup; a value of 0 indicates that it should not include cleanup. For example, to add meta data retention cleanup to a profile, execute:
EXEC sp_add_agent_parameter @profile_id=<my_profile_id>, @parameter_name='MetadataRetentionCleanup', @parameter_value=1
For automatic retention-based cleanup to occur in a database involved in merge replication, the database and the Merge Agent must both be on servers running SQL Server 2000 SP1 or later. For example:
- A SQL Server 7.0 pull Subscriber will not run cleanup at a SQL Server 2000 SP1 Publisher.
- A SQL Server 2000 SP1 push Merge Agent will not run cleanup in a SQL Server 2000 (without SP1) Subscriber database.
- A SQL Server 2000 SP1 push Merge Agent will run cleanup in a SQL Server 2000 SP1 Publisher database, even if it has subscribers that are SQL Server 2000 or earlier.
Preventing false conflicts
Retention-based meta data cleanup prevents non-convergence and silent overwrites of changes at other nodes. However, false conflicts can occur if both of the following conditions are met:
- The meta data is cleaned up at one node and not another.
- A subsequent update at the cleaned-up node occurs on a row whose meta data was deleted.
To prevent this conflict, make sure meta data is cleaned up at related nodes at about the same time. If -MetadataRetentionCleanup 1 is used, both the Publisher and Subscriber are cleaned up automatically before the merge starts, thereby ensuring that the nodes are cleaned up at the same time. If a conflict occurs, use the merge replication conflict viewer to review the conflict and change the outcome if necessary.
If an article belongs to several publications or is in a republishing scenario, it is possible that the retention periods for a given row at the Publisher and Subscriber are different. To reduce the chance of cleaning up meta data on one side but not the other, it is recommended that those different publications have similar retention periods.
NoteIf there is a large amount of meta data in the system tables that must be cleaned up, the merge process may take longer to run. Clean up the meta data on a regular basis to prevent this issue.
5.3.6 Backup and restore issues for merge replication
Introduced in SP1
A publication database that is restored from a backup should first synchronize with a subscription database that has a global subscription (that is, a subscription having an assigned priority value) to guarantee correct convergence behavior. Synchronization ensures that the changes that were lost at the publication database because of the restore operation are reapplied accurately.
Do not synchronize the publication database with a subscription database that has an anonymous subscription. Because anonymous subscriptions do not have enough meta data to apply changes to the publication database, such synchronization could lead to the non-convergence of data.
When you are planning backup and restore operations for merge replication, consider the following additional issues:
- Restore the subscription databases from backups that are not beyond the retention period.
Restore a subscription database from a backup only if the backup is no older than the shortest retention period of all publications to which the Subscriber subscribes. For example, if a Subscriber subscribes to three publications with retention periods of 10, 20, and 30 days, respectively, the backup used to restore the database should not be more than 10 days old.
- Synchronize before generating a backup.
It is strongly recommended that a Subscriber synchronize with the Publisher before you perform a backup. Otherwise, the system might not converge correctly if the Subscriber is restored from this backup. Although the backup file itself might be very new, the last synchronization with a Publisher could be almost as old as the retention period. For example, consider a publication with a retention period of 10 days. The last synchronization was 8 days ago, and now the backup is performed. If the backup is applied 4 days later, the last synchronization will have occurred 12 days ago, which is past the retention period. If the Subscriber had synchronized right before the backup, the subscription database would be within the retention period.
- Reinitialize the Subscriber if you change the publication retention value.
If you need to change the publication retention value, manually reinitialize the Subscriber to avoid the non-convergence of data. The retention-based meta data clean up feature deletes outdated meta data from merge system tables when the publication retention period is reached.
The publication retention value is used to determine when subscriptions that have not synchronized within the retention period should expire. If, after a clean up, the publication retention period is increased and a subscription tries to merge with the Publisher (which has already deleted the meta data), the subscription will not expire because of the increased retention value. Furthermore, the Publisher does not have enough meta data to download changes to the Subscriber, which leads to non-convergence.
- Use the same publication retention values for all Publishers and their alternate synchronization partners. Using different values may lead to non-convergence.
5.3.7 Restoring replicated databases from different versions of SQL Server
Introduced in SP1
Restoring a backup to the same server and database—running the same version as the server from which the backup was created—will preserve your replication settings. If you are restoring a replicated database to a version of SQL Server that is different from the version used to back up the database, consider the following issues:
- If you are restoring to SQL Server 2000 SP2 from a backup created with SQL Server 2000, and you want to preserve replication settings, you must run sp_vupgrade_replication. Running sp_vupgrade_replication ensures that the replication meta data has been upgraded. If you do not run sp_vupgrade_replication, the replication meta data may be left in an unpredictable state.
- If you are restoring to SQL Server 2000 from a backup created with SQL Server 7.0 (whether it be the release version, Service Pack 1, Service Pack 2, or Service Pack 3), and you want to preserve replication settings, you must re-create the backup before installing service packs. It is possible to directly restore to SQL Server 2000 SP2 from a backup of a replicated database created in SQL Server 7.0, but replication settings will not be maintained.
5.3.8 A new -MaxCmdsInTran parameter for Log Reader Agent
Introduced in SP1
Starting with SP1, a new command prompt parameter, -MaxCmdsInTran, has been added for the Log Reader Agent. For transactions affecting a large number of commands (typically mass updates or deletes), the Distribution Agent must wait for the Log Reader Agent to write the entire transaction to the distribution database before it can start propagating the transaction to the Subscriber. This delay blocks the Distribution Agent and reduces the parallelism between the two agents.
By using –MaxCmdsInTran, the Log Reader Agent breaks large transactions into smaller chunks, and each chunk contains the same or fewer commands than the -MaxCmdsInTran input. Therefore, the Distribution Agent can start processing earlier chunks of a transaction while the Log Reader Agent is still working through later chunks of the same transaction.
This improvement in parallelism between the Log Reader Agent and the Distribution Agent contributes to better overall replication throughput. Note, however, that the transaction chunks are committed at the Subscriber as individual transactions, which breaks the ACID property of atomicity. This outcome is not a problem in most circumstances, though it is recommended that you test this to make sure.
Defining the –MaxCmdsInTran parameter
Specify a positive integer (1 or above) for the -MaxCmdsInTran parameter value. Specifying a value of 0 is equivalent to not using the parameter at all. Because this parameter improves performance only when the transaction is very large, a value of 5000 or greater for this parameter is typical. For example:
logread.exe -MaxCmdsInTran 10000.
5.3.9 Restriction on non-unique clustered indexes
Introduced in SP2 (applies to Transactional Replication only).
You cannot create a non-unique clustered index on a table after it is published for transactional replication. Before creating the index you must first drop any publication that includes the table.
5.3.10 A new -MaxNetworkOptimization command line argument for Snapshot Agent
During normal processing, merge replication can send DELETE commands to Subscribers for rows that do not belong to the Subscriber's partition. DELETE commands of this type are referred to as irrelevant deletes. Irrelevant deletes do not affect data integrity or convergence, but can result in unnecessary network traffic.
To reduce network traffic from irrelevant deletes, you can use the new Snapshot Agent parameter -MaxNetworkOptimization with merge replication publications. Setting the parameter to 1 minimizes the chances of irrelevant deletes, which maximizes network optimization.
NoteSetting this parameter to 1 is useful only when the synchronization optimization option of the merge publication is set to true (the @keep_partition_changes parameter of sp_addmergepublication).
The default is 0 because setting the parameter to 1 can increase the storage of meta-data and cause performance degradation at the Publisher if multiple levels of join filters and complex subset filters are present. You should carefully assess your replication topology and only set -MaxNetworkOptimization to 1 if network traffic from irrelevant deletes is unacceptably high.
You can add this parameter to the Snapshot Agent profile by executing the system procedure sp_add_agent_parameter:
EXEC sp_add_agent_parameter 1, 'MaxNetworkOptimization', 1
5.4 Meta Data ServicesThe following items apply to SQL Server 2000 Meta Data Services.
5.4.1 Meta Data Browser exports in Unicode
Introduced in SP1
Meta Data Browser now exports XML-based meta data in Unicode. Before SQL Server 2000 SP1, the browser exported ANSI code, which does not support non-English characters. This functional change is transparent to the user. As of this SP2 release, exported data is always expressed as Unicode. You can still export in ANSI code by setting the value of the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Repository\Engine\XMLExport to"0". The following list represents the values that you can set for this registry key:
5.5 Data Transformation ServicesThe following items apply to SQL Server 2000 Data Transformation Services (DTS).
5.5.1 DTS Wizard no longer limits string columns to 255 characters
Introduced in SP2
When you export data to a text file, the DTS Import Export Wizard now configures the package to write up to 8000 characters of any column containing string-type data.
5.5.2 Security context logged for DTS packages run by SQL Server Agent
Introduced in SP2
SQL Server Agent records the security context under which each step in a job runs. Beginning with SP2, the security context appears in the Job History dialog box. When you run a DTS package from a step in a job, the SQL Server Agent logs the user account under which the package is run. This information helps administrators diagnose permissions and authentication issues that occur when DTS packages are scheduled to run on a server.
5.5.3 SQL Server Agent Proxy Account improvements
Introduced in SP2
Prior to SP2, DTS packages stored on the server could not be run under the credentials of the SQL Server Agent Proxy Account unless the proxy account had access to the user TEMP folder for the account under which either the server (in the case of jobs run from xp_cmdshell), or the agent (in the case of agent jobs) was running. Because of this, users often had to adjust the TEMP environment variable for the SQL Server or SQL Agent startup account to point to a directory that was accessible to both the startup and the proxy accounts, like C:\TEMP. For SP2, DTS has been enhanced to use the system TEMP folder if the user TEMP folder is unavailable, greatly reducing the need for these adjustments.
5.6 Enhancements to the Virtual Backup Device APIThe following item applies to the SQL Server 2000 Virtual Backup Device API.
5.6.1 Capturing multiple databases in a single snapshot
Introduced in SP2
The Virtual Backup Device API allows independent software vendors to integrate SQL Server 2000 into their products. This API is engineered to provide maximum reliability and performance. It fully supports the backup and restore functionality for SQL Server 2000, including the complete range of hot and snapshot backup capabilities.
In SP1 and earlier, there was no way to freeze and back up more than a single database at one time. SP2 now provides server-side support for freezing and capturing multiple databases in a single snapshot through the VDC_PrepareToFreeze command.
The Virtual Backup Device Specification in SP2 contains updated information on the VDC_PrepareToFreeze command. An updated version of the Virtual Device Interface header file (vdi.h) can be found at \devtools\include in the SP2 setup directory.
6.0 English Query enhancementIntroduced in SP1
Microsoft has released a security enhancement for English Query applications. This enhancement is not installed as a part of the Service Pack. However, it is recommended that you apply it if you are using English Query. The security enhancement is located on the SP2 CD-ROM in the folder \EQHotfix. Details about the English Query enhancement are available at the Microsoft Product Support Services Knowledge Base.Search the Knowledge Base for article 297105.
7.0 DB-Library and Embedded SQL for CIntroduced in SP1
While the DB-Library and Embedded SQL for C application programming interfaces (APIs) are still supported in SQL Server 2000, no future versions of SQL Server will include the files needed to do programming work on applications that use these APIs. Connections from existing applications written using DB-Library and Embedded SQL for C will still be supported in the next version of SQL Server, but this support will also be dropped in a future release. When writing new applications, avoid using these components. When modifying existing applications, you are strongly encouraged to remove dependencies on these technologies. Instead of DB-Library or Embedded SQL for C, you can use ADO, OLE DB, or ODBC to access data in SQL Server. Please refer to SQL Server Books Online for more information on these technologies.
Identyfikator artykułu: 889552 — ostatni przegląd: 06/20/2014 09:31:00 — zmiana: 2.0
- kbdatabase kbservicepack kbhowto kbinfo KB889552