The information in this article applies to:
NOTE: If for any reason you must remove SQL Server 6.5 Service Pack 5a after applying it, see the following article in the Microsoft Knowledge Base: Q216421 INF: How to Downgrade From SQL Server 6.5 Service Pack 5 or 5aJanuary 25, 1999 --------------------------------------------------------------- Microsoft SQL Server 6.5 Service Pack 5a--------------------------------------------------------------- This service pack is for use only by holders of a current, valid Microsoft(R) SQL Server(TM) version 6.5 license. It addresses some specific problems that were discovered in SQL Server version 6.5 after it was shipped. It is recommended that customers running SQL Server 6.5 upgrade to Service Pack 5 (SP5a). This service pack includes self-extracting zipped files for Intel and Alpha processor architectures: Microsoft is phasing out future development and engineering efforts for the MIPS and PowerPC platforms. Service Pack 5a is not available for these platforms. Service Pack 5a can be applied to all shipped versions of SQL Server version 6.5, including SQL Server 6.5, Enterprise Edition or the SQL Server version included with Microsoft Small Business Server (SBS). Because SQL Server service packs are cumulative, Service Pack 5a includes the fixes from all earlier SQL Server 6.5 service packs. Therefore, Service Pack 5a can be applied to an original installation of SQL Server version 6.5 or to any SQL Server 6.5 installation to which a service pack has been applied previously. For the latest year 2000 information and year 2000 information specific to SQL Server, refer to the SQL Server 6.5 information in the Microsoft Year 2000 Resource Center at http://www.microsoft.com/year2000/ Service Pack 5a provides enhanced sort order definitions that include support for the euro currency symbol. For the latest information on euro currency symbol support and details about SQL Server euro support, refer to the Microsoft Euro Currency Resource Center at http://www.microsoft.com/euro/ Service Pack 5a provides an optional installer for Microsoft Data Access Components 2.1 (MDAC 2.1), which also includes a newer generation of the Microsoft SQL Server ODBC driver (version 3.70). By installing MDAC 2.1 you are upgrading to the versions shipped with SQL Server version 7.0. System requirements for MDAC 2.1 are Microsoft Windows NT(R) 4.0 (or later), Microsoft Windows(R) 95, or Microsoft Windows 98. If you are running on Windows NT 3.51 or Windows clients, you will not be able to benefit from the latest data access components. For additional information see "Optional Installation of MDAC 2.1", Mdacread.txt, and refer to the Univeral Data Access web at http://microsoft.com/data/ Service Pack 5a installs updated Microsoft Distributed Transaction Coordinator (DTC) components (version 3.0) if the server platform is running Windows NT 4.0. By installing this release, you are upgrading Microsoft DTC to the versions provided with Windows NT 4.0 Service Pack 4 and SQL Server 7.0. The original DTC 1.0 components that were included with the original SQL Server release will be preserved if you are running on the Windows NT 3.51 platform. --------------------------------------------------------------- Service Pack Versions--------------------------------------------------------------- If you are not sure which service pack you have installed on a computer running SQL Server, you can verify the version by issuing SELECT @@VERSION from either ISQL or ISQL/w. The following table illustrates the correspondence between the version string reported by @@VERSION and the SQL Server service packs. --------------------------------------------------------------- Service Pack Installation--------------------------------------------------------------- Using the service pack setup program is the recommended method for installing the entire service pack. However, you can simply upgrade individual files to address individual issues. For example, to resolve a DB-Library issue, you only need to upgrade the file Ntwdblib.dll. If you do not want to upgrade all files, do not run the Setup program described in Step IV. Instead, search your hard drive and remove all occurrences of the file(s) you want to upgrade and copy the new file(s) from this service pack to the appropriate directory on the server. The exception to copying individual files is that the following files must always be upgraded together: Upgrade all four of these files or none of them; never upgrade just one or two of them. However, if you are currently running build 415 of SQL Server, you can perform the upgrade by replacing Sqlservr.exe and Opends60.dll only. You can use Microsoft Systems Management Server to install this service pack automatically on multiple Windows NT Server computers. The SMSSQ655.PDF file is a Package Definition File (PDF) that automates the creation of a SQL Server package in Systems Management Server. The SQL Server package can then be distributed and installed on Systems Management Server computers. The SMSSQ655.CMD file is a batch file that detects the platform of the computer and runs the appropriate version of setup. NOTE: If you are using replication, all of the replicated transactions in the distribution database must be distributed before applying this service pack. If you are not sure that all replicated transactions have been applied, you can unsubscribe users before upgrading to this service pack, and resubscribe them after the upgrade has completed. This ensures that undelivered transactions are cleaned out of the distribution database. It is recommended that all servers acting as replication Publishers and Distributors be upgraded to Service Pack 5a. However, a Distribution server running Service Pack 5a will interoperate with Publishers running earlier service packs (6.5, SP1, SP2, SP3, SP4, and SP5). The reverse is not true; you should not run a Publisher running Service Pack 5a against a Distributor running an earlier service pack. To apply the entire service pack to your server, complete the following steps: --------------------------------------------------------------- (I) Back up databases.---------------------------------------------------------------As a precaution, back up all of your databases, including the master and msdb databases. Installation of the service pack does not make modifications to user databases, but it does make modifications to the master and msdb databases. If you are running Service Pack 5 (build 415), these backups must be performed using either the ISQL command-line utility or the ISQL/w tool. For additional information, please see the following article in the Microsoft Knowledge Base: Q215458 BUG: LOAD DATABASE Fails with Error 603--------------------------------------------------------------- (II) Download and extract the Service Pack.---------------------------------------------------------------If you downloaded this service pack, you will first need to uncompress the files. To expand the files, place the self-extracting executable file for your processor architecture into a temporary directory on the computer running SQL Server. From that directory, execute the file. For example, if you are running SQL Server on an Intel-based computer and have downloaded the SP5aX86.exe file into the C:\SP5aTemp directory, run the following commands: This will uncompress the service pack files and place the two SMS files in the SP5aTemp directory. Additionally, a subdirectory will be created for the hardware platform (I386 or ALPHA), where the remainder of the service pack files will be placed. In this example, a subdirectory of "I386" will be created. You can rename the installation directories; however, you need to make sure that the directory name does not contain space characters. --------------------------------------------------------------- (III) Prepare the SQL Server system(s) for Service Pack application.---------------------------------------------------------------Verify that the PATH variable used to display or set the search path for executable files does not contain quotation marks. If it does, remove them. To verify your PATH variable, type PATH <CR> in the command prompt of the systems on which you plan to install Service Pack 5a. Running the service pack installation may fail with the following message if this is not corrected:
Verify that the SQL Server system on which you apply Service Pack 5a has at least 3 MB of free space in the master database. To verify this, log on to SQL Server and run the sp_spaceused stored procedure in the context of the master database. If the unallocated space figure is less than 3 MB, increase the size of the master database. To do this, perform the following steps:
--------------------------------------------------------------- (III-A) Prepare SQL Server, Enterprise Edition in a Windows NT cluster configuration.---------------------------------------------------------------SQL Server Enterprise Edition must be unclustered before you can apply Service Pack 5a. To uncluster SQL Server, follow the directions in "Removing a Virtual Server" found in the WhatsNew.rtf file provided with your original SQL Server, Enterprise Edition compact disc. Do this on all nodes of the Windows NT cluster where SQL Server has been clustered. If you are running a clustered version of Microsoft Distributed Transaction Coordinator (DTC), use the Windows Cluster Service Cluster Administrator to ensure that the DTC cluster resource is stopped before running the service pack setup. When you install Service Pack 5a with a clustered version of DTC, the service pack setup program upgrades the clustered version of DTC automatically on all nodes of the cluster. The DTC group needs to be owned by the node on which you first install Service Pack 5a. When you apply Service Pack 5a to other nodes of the cluster, make sure the DTC group remains owned by the node on which you first installed Service Pack 5a. If you are currently running a nonclustered version of DTC, Service Pack 5a installs a clustered version. For this you need to have a Resource Group that contains both a Network Name Resource and a Shared Disk Resource. Ensure that at least one such Resource Group exists. Setup will assign Microsoft DTC to the first Resource Group it finds that contains both a Network Name Resource and Shared Disk Resource. This DTC group must be owned by the first node on which you install Service Pack 5a. When you apply Service Pack 5a on other nodes of the cluster, make sure the DTC group remains owned by the first node on which you installed Service Pack 5a. --------------------------------------------------------------- (III-B) Stop applications before running Service Pack 5a Setup.---------------------------------------------------------------Before running Service Pack 5a Setup, shut down the following services and applications. If installing in a Windows NT cluster, ensure that these applications and services are stopped on all nodes in the cluster:
--------------------------------------------------------------- (IV) Run Service Pack 5a Setup.---------------------------------------------------------------From the directory containing the unzipped service pack files, run Setup.exe. If you received this service pack on a compact disc, change to the directory for your processor architecture (\I386 or \Alpha) and run Setup.exe. Setup will replace the existing SQL Server files with the new ones from this service pack. Setup also runs four .SQL script files to update several system stored procedures. An additional .SQL script file is run if the server is a distribution server for replication. The DTC installer installs a new version of Microsoft DTC on your system. In a clustered environment, it does this on all nodes of the cluster. Service Pack 5a Setup requires the SQL Server system administrator (sa) password. If run on Windows NT Enterprise Edition, Setup also asks for the Windows NT Administrator password on the remote node. There are two optional command line parameters for Setup.exe: a Password option and a ForceReboot option. Password Option: Setup uses an integrated connection and does not ask for a password. An integrated connection will work properly only if you are using the Named Pipes or multiprotocol network libraries. The keyword "Password" is case-sensitive ("PASSWORD" or "password" is incorrect)and the spaces must be specified after the /t and before and after the equal sign (=): Setup uses "" for a password with standard security using the sa login: where <value> specifies the password for the sa login. If the password provided is not valid, Setup prompts for a password. If no password parameter is provided, Setup checks the ISQLPASSWORD environment variable and uses it for the password, if it exists. After the Password option is accepted as a valid sa password, Setup completes the setup process and then stops automatically. Setup does not send a message to the monitor when it completes. ForceReboot Option: specifies that Setup should restart the computer when the service pack update is complete. The keyword "ForceReboot" is case-sensitive, and the spaces must be specified after the /t and before and after the equal sign (=). It is possible to use more than one parameter for Setup.exe; however, each option must be preceded by /t. For example: If your SQL Server is part of a clustered environment, repeat this on every node. --------------------------------------------------------------- (V) Restart the system.---------------------------------------------------------------When Setup has completed, restart the system. If your SQL Server is part of a clustered environment, do this on every node. --------------------------------------------------------------- (VI) Recluster and Restart SQL Server.---------------------------------------------------------------If you have applied Service Pack 5a in a Windows NT cluster configuration, you must recluster before restarting SQL Server. --------------------------------------------------------------- (VI-A) Recluster SQL Server, Enterprise Edition in a Windows NT cluster configuration.---------------------------------------------------------------If your SQL Server is part of a Windows NT cluster configuration, recluster it. Use the Cluster Setup Wizard that Service Pack Setup installed in the \Cluster subdirectory of your SQL Server installation. Follow the directions under "To install SQL Server on a cluster with high availability" located in the WhatsNew.rtf file provided with the original SQL Server, Enterprise Edition compact disc. Do this on all nodes of the Windows NT cluster where SQL Server has been clustered before installing Service Pack 5a. --------------------------------------------------------------- (VI-B) Restart SQL Server and SQL Server applications.---------------------------------------------------------------Restart the SQL Server, SQL Executive, and MSDTC services. Restart the applications and services you closed before running the Service Pack Setup. If you are upgrading from Service Pack 5 (build 415), perform backups of all databases. --------------------------------------------------------------- Optional Installation of MDAC 2.1.--------------------------------------------------------------- Service Pack 5a provides a separate Microsoft Data Access Components 2.1 (MDAC 2.1) installer, which is not run automatically as part of step (IV) Run Service Pack 5a Setup. If you plan to upgrade your server or client computer to any of the components delivered with MDAC 2.1, start the installer, Mdac_typ.exe, in the I386\Mdac or Alpha\Mdac directory and follow the instructions. Before doing that, be sure to review the instructions in the Mdacread.txt file. Even if you do not run the optional MDAC installer, Service Pack 5a Setup runs Instcat.sql, which updates the catalog stored procedures and guarantees compatibility to clients that are on MDAC 2.1. Versions of SQL Server 6.5 Service Pack 5a that are included with products that already contain the MDAC 2.1 components may not include the separate MDAC subdirectory as part of Service Pack 5a. Refer to the installation documentation for the product shipping Service Pack 5a for instructions on installing the MDAC 2.1 components. --------------------------------------------------------------- Client Computer Upgrade--------------------------------------------------------------- The Service Pack Setup program can be used to update computers running Windows NT and Windows 95 that have the SQL Server Client Utilities installed. To update these clients to Service Pack 5a, copy the Service Pack 5a files to a directory on the client computer and then run Setup.exe from that directory. If you require any of the updated Win16 components, you will need to copy the appropriate file(s) directly to the client. The client may have the same SQL Server 6.5 DLLs in multiple directories, so it is recommended that you first search your hard drive for all occurrences of the file you intend to replace. Remove all copies of the old DLL before copying the new DLL to the client. --------------------------------------------------------------- Documentation Notes--------------------------------------------------------------- I. New sp_tableoption Parameter (introduced in Service Pack 1) The sp_tableoption system stored procedure includes a new option, 'table lock on bulk load'. When this option is enabled for a table, bulk loading of data (using either the bcp utility or the bulk copy API) uses only an exclusive table lock. With the option disabled (the default behavior), an extent lock is acquired for each extent allocated by a bulk copy batch. Enabling the option allows you to specify larger batch sizes without needing to increase the sp_configure value for 'locks'. When using a fast (non-logged) bulk copy, this option may improve the speed of the bulk copy operation significantly. For slow (logged) bulk copy, this option may show only marginal improvement in the speed of the bulk copy operation. The following example enables this option for the sales table: II. Loading SQL Server 6.5 Database Dumps into SQL Server 6.0 Databases The Microsoft SQL Server 6.5 documentation states that a SQL Server 6.5 database dump can be loaded into SQL Server 6.0 if the 6.0 server has Service Pack 3 installed. However, SQL Server 6.0 Service Pack 3 does not allow the loading of version 6.5 database dumps. If you attempt to load a SQL Server 6.5 database dump into SQL Server 6.0 with Service Pack 3 applied, the load will be rejected and an error will be raised indicating that the dump is from an incorrect version. III. ODBC Driver Enhancements (Introduced in Service Pack 2) There are two new backward compatibility features supported in the SQL Server ODBC driver included in this service pack. The first disables the use of ANSI quoted identifiers by the driver. This allows character literals in SQL statements to be delimited by double quotation marks ("). The second disables ANSI NULL semantics, ANSI padding, and ANSI warnings. These are the ANSI settings that are enabled by default in the SQL Server 6.5 ODBC driver. Refer to the SET statement in the Transact-SQL Reference for details on these ANSI options. The default for these features is to have them enabled; for example, ANSI behavior is the default. To disable one or both of these options, use one of the following methods:
IV. New Trace Flag for Use with Replication (Introduced in Service Pack 2) This service pack supports a new trace flag: 8202. When this flag is enabled, single-row UPDATE statements will be replicated as DELETE/INSERT pairs. When this flag is not enabled (the default), single-row UPDATE statements are replicated as a single UPDATE statement. Multi-row UPDATE statements are always replicated as DELETE/INSERT pairs. For more information about using trace flags, refer to the SQL Server Transact-SQL Reference documentation. V. DBCC NEWALLOC Enhancements (Introduced in Service Pack 2) The SQL Server documentation recommends setting databases to "read only" or "single user" mode when running DBCC NEWALLOC on them to avoid spurious errors caused by in-progress transactions. In most cases these errors are 2540 or 2521 errors. This service pack greatly reduces the probability of getting these spurious 2540 and 2521 errors while users are updating the database. VI. New sp_configure Options (Introduced in Service Pack 2) The sp_configure system stored procedure installed with this service pack includes two new advanced configuration options: 'Protection cache size' and 'LogLRU buffers'. These options have the following syntax: <number of entries> is a value between 1 and 8192. The default is 15. <number of buffers> is a value between 0 and 2147483647. The default is 0. The protection cache is a per-connection list that holds the most recent results of object or statement permissions checks. The number of check results held is specified by the <number of entries> parameter. Entries in the list are invalidated automatically when permissions change. Each protection cache entry requires 28 bytes of memory on Intel computers and 32 bytes on RISC computers. If applications frequently reference a large number of objects from a single connection, setting this value higher may increase performance. After changing the value of 'Protection cache size', you must issue a RECONFIGURE statement, and then stop and restart SQL Server to enable the change. The updated DBCC SQLPERF(LRUSTATS2) command reports a statistic of "protection cache hit ratio" that can be used to evaluate the effectiveness of increasing the protection cache value. Increasing the per-connection protection cache takes away memory from SQL Server's data cache and may not result in a net performance gain. It is important to measure overall application throughput as well as the protection cache hit ratio when tuning this sp_configure option. The 'LogLRU buffers' option controls the caching of log buffers. Buffers are controlling structures that manage a page while it is in cache. When this option is set to 0 (the default, and current SQL Server 6.5 behavior), buffers for transaction log pages are kept in the same pool of buffers as data pages. When this option is set to a nonzero value, a private cache is established that is used to hold only buffers for the transaction log. The <number of buffers> parameter of the 'LogLRU buffers' option specifies the maximum number of log buffers that are allowed in the private transaction log cache. The location where a log buffer is written is determined by the 'LogLRU buffers' option. If this option is 0, the buffer goes into the main buffer cache, which is also used for data buffers. If the option is greater than 0, the buffer goes into the private log cache. If the buffer goes into the private log cache and the addition of that buffer causes the total number of buffers in that cache to exceed <number of buffers>, the log buffer added last will be removed from the private log cache. Keeping a private log cache may reduce the frequency of doing physical reads from the log disk when:
Using a private log cache reduces the size of the buffer cache used for data buffers. The size of the private log cache must be adjusted so that most log reads are satisfied from cache without adversely affecting data cache hit ratio. The SQL Performance Monitor and the DBCC SQLPERF statement can be used to tune this setting properly. DBCC SQLPERF(LRUSTATS2) shows the number of log buffers in the private log cache as well as the total number of log pages (private cache plus main cache). Additionally, it shows the number of physical log buffer reads done since the last time DBCC SQLPERF(LRUSTATS2) was run. Set 'LogLRU buffers' to approximately 10 percent of the total cache size and reduce it until DBCC SQLPERF(LRUSTATS2) indicates that physical log reads occur. Then increase it slightly. It is possible to estimate the required private log cache size under steady-state conditions. DBCC SQLPERF(IOSTATS) shows the number of 2-KB log buffers written (logical log writes). SQL Performance Monitor shows physical log writes that may include multiple 2-KB buffers. The rate at which log buffers are written (2-KB buffers per second) divided by the average time a transaction runs before rolling back (in seconds) gives the approximate size of the private log cache in 2-KB pages. VII. Modified sp_configure Options (Introduced in Service Pack 2) The maximum allowable values for these sp_configure options have been increased:
These values were increased because newer disk subsystems can support far more simultaneous I/O than earlier subsystems. Additionally, 2 GB of RAM is no longer unusual in large systems, so the 'backup buffer size' maximum has been increased to take better advantage of large memory systems. The 'backup buffer size' option controls buffer usage for dump/load operations and fast bulk copy. The value specified for this option determines the size of buffers used for dump/load operations in increments of 32 pages. For example, if 'backup buffer size' has a value of 5, that means that buffers of 327680 bytes (5 * 32 * 2KB/page) will be used. This option also controls the number of 16-KB buffer pairs used by each instance of fast bulk copy. For example, if 'backup buffer size' has a value of 5, bulk copy will use 5 pairs of buffers (buffers for fast BCP are 16 KB each) for a total size of 163840 bytes (10 buffers * 16KB/buffer). The behavior of 'max async I/O' and 'max lazywrite I/O' has changed slightly in Service Pack 2. Previously, 'max lazywrite I/O' could not be set higher than the value of 'max async I/O'. Also, 'max async I/O' was not dynamically configurable. With Service Pack 2, the value of 'max lazywrite I/O' can exceed 'max async I/O', and both are dynamically configurable. The upper limit for dynamic changes to these two options is determined at the time of SQL Server startup, and is the greater of either of them. Increasing either option above that limit requires stopping and restarting SQL Server. The purpose of this change is to allow the I/O for checkpoint and the lazywriter to be "throttled" independently of each other. For example, the lazywriter I/O can now be set to 300 and the max async I/O (for the checkpoint) can be set to a lower value, such as 100. If those were the values at the time SQL Server was started, either parameter could be changed dynamically to any value between 1 and 300. If either parameter is increased beyond 300, the change does not take effect until SQL Server is restarted. The value of 'max lazywrite I/O' can be tuned by observing the following SQL Performance Monitor counters:
The 'max lazywrite I/O' parameter should be adjusted high enough to maintain free buffers without adversely affecting the transactions/sec or outstanding reads and writes. The value of 'max async I/O' throttles the I/O done during a checkpoint and can be tuned by observing the following SQL Performance Monitor counters:
'max async I/O' should be adjusted so that checkpoints can occur within the checkpoint interval without adversely affecting transactions/sec or outstanding reads and writes. VIII. Euro currency symbol support (introduced in Service Pack 5) The character set used by SQL Server was chosen during your initial SQL Server 6.5 installation. The selected character set determines the characters that SQL Server recognizes in database character data. The SQL Server 6.5 Service Pack 5 database engine introduces character support for the euro currency symbol when code page ISO (default), CP1250, CP1251, CP1253, CP1254, CP1255, or CP1257 is selected. If you have specified the SQL Server ISO-1 default code page, the euro currency symbol is sorted as part of the control characters. For character sets CP1250, CP1251, CP1253, CP1254, CP1255, and CP1257, enhanced sort order definitions are provided with SQL Server 6.5 Service Pack 5 and installed by the Service Pack 5 Setup. No euro currency symbol support is available on OEM and Far Eastern SQL Server code pages 850, 437, 1256, CP932, CP936, CP949, and CP950. The enhanced sort orders change the sorting behavior of SQL Server. Because the euro currency symbol has not been defined in the character sets previously, this does not affect existing installations. However, if you used SQL Server's char and varchar datatypes to store binary (noncharacter) data, indexes may become invalid and need to be reindexed. --------------------------------------------------------------- Updated SQL Server Files for This Service Pack--------------------------------------------------------------- Additional query words: prodsql
Keywords : SSrvGen kbfix6.50.SP5 |
|
Last Reviewed: July 13, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |