Article ID: 889552
Microsoft SQL Server 2000 Service Pack 2 (SP2) provides updates for the database components of a SQL Server 2000 installation. This article discusses the following:
Note Desktop 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."
Note To remove SP2, you must have a backup of the master, model, and msdb databases, 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.
To identify which version of SQL Server 2000 you have installed, type SELECT @@VERSION at the command prompt when using the osql or isql utilities 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.
Collapse this tableExpand this table
Note The beginning of the line indicates the SQL Server edition. This is followed by current operating system information.
To identify which version of Analysis Services you have installed, follow these steps:
Collapse this tableExpand this table
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.
Note Some 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.
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:
This installation package could not be opened. Verify that the package exists and that you can access it, or contact the application vendor to verify that this is a valid Windows Installer package.
To upgrade instances of the Desktop Engine that were created using sqlrun02.msi - sqlrun16.msi, you must apply the service pack from the Microsoft CD-ROM.
The upgrade patch cannot be installed by the Windows Installer service because the program to be upgraded may be missing, or the upgrade patch may update a different version of the program. Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch.
Note The service pack is language specific. Apply the service pack with the same language as the SQL Server component that you are upgrading.
Note Before 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:
If SP2 Setup detects any user databases or filegroups that are not writable, it:
Note This 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."
Note During 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.
Note Since 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.
Before installing either Database Components SP2 or Desktop Engine SP2, back up the master, msdb, and model databases. Installing the service pack makes modifications to the master, msdb, and model databases, 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.
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.exe and by using the Export Registry File item on the Registry menu to export the key
HKEY_LOCAL_MACHINE\SOFTWARE\OLAP Serverto a file for backup.
If the autogrow option has not been 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 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 has been selected for the master and msdb databases, 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 file check box is selected.
To verify that this option has been selected in Desktop Engine, issue the following SQL statements:
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:
Run the Setup.bat script from either of these locations:
The Setup program then:
The Authentication Mode dialog box does not default to the current settings for the installation. The dialog box defaults are:
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.
To install Analysis Services SP2, run Setup.exe from either of the following locations:
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:
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:
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:
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.
Note Because 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:
If you do not know what installation package was used, type SQLRUN after the /p option. The SQLRUN switch tells Setup.exe to choose the correct patch package itself. This will only work if the following conditions are met:
The instance name can also be specified in an .ini file. For more information, see the topic "Customizing Desktop Engine Setup.exe" in SQL Server Books Online.
Note Do 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.
Note When 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:
Note You 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.
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.
Restart the applications you closed before running the service pack setup.
To install this service pack on a failover 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:
Deploy this service pack across replicated servers in this sequence:
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."
Note SP2 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.
When non-writable databases or filegroups exist, the Setup program will display the following message: In general, you may 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 SP2 Setup to that instance of SQL Server 2000.
Note This message will not affect unattended installations. To learn more about unattended installations, see Section 4.0 Unattended Installations.
Note During 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:
To revert to the pre-SP2 version of SQL Server 2000 components, you must have taken a backup of the master, msdb, and model databases prior to installing SP2. For more information, see Section 3.1 "Back up your SQL Server databases."
Note If any of the databases are involved in replication, you must disable publishing.
In the following cases, it will be necessary to reapply SQL Server 2000 SP2:
The Analysis Services and Meta Data Services segments in this section will not apply to Desktop Engine only installations.
Introduced in SP1
Follow these procedures to install a new instance of the Desktop Engine after applying 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."
Note You do not have to reapply Sqlredis.exe on client computers or on servers that do not have databases containing Chinese, Japanese, or Korean characters.
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.
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 mask option. For more information, see the 298402 in 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 mask option. For more information, see the 299641 in Microsoft Product Support Services Knowledge Base
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.
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 306467 in Microsoft Product Support Services Knowledge Base
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.
Introduced in SP1
The Analysis Services SP1 and later versions includes updated versions of the following client redistributable setup programs:
Note PTSFull.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.
Note When using Analysis Services with Office XP, upgrading the client is highly recommended.
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
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.
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 =] artid Is the article ID. artid is 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.
Permissions Members of the public role can execute sp_scriptdynamicupdproc.
This example creates an article (with artid set to 1) on the authors table in the pubs database, and specifies that the UPDATE statement is the custom stored procedure to execute:
Generate the custom stored procedures to be executed by the Distribution Agent at the Subscriber by running the following stored procedure at the Publisher:
After running this stored procedure, you can use the resulting script to manually create the stored procedure at the Subscribers.
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.
Important Typically, 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.
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.
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
[@publication] = publication_name
Is the name of the publication. publication_name is 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.
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:
Retention-based meta data cleanup occurs as follows:
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:
Meta data cleanup in topologies with different versions of SQL Server
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:
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:
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.
Note If 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.
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:
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:
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:
To use this parameter, the Publisher must be running SQL Server 2000 SP1 or later, and the Log Reader Agent and distribution database must be upgraded to this service pack. Otherwise, -MaxCmdsInTran is ignored.
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.
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.
Note Setting 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:
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:
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.
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.
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.
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.
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
(http://support.microsoft.com/search/?adv=1&spid=2852). Search the Knowledge Base for article 297105.
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.
Article ID: 889552 - Last Review: June 20, 2014 - Revision: 2.0