Microsoft is about to bring out many new changes in SQL Server: the all-new SQL Server 2005, the intriguing SQL Server Express and SQL Server 2000 Service Pack 4. It's the last one that will probably have the most immediate impact for SQL Server users -- with its new collection of fixes, functionality expansions and feature add-ons for SQL Server.
Service Pack 4, like all service packs before it, is cumulative; it contains everything from all previous service packs. It has not yet been released, but it is in final testing and will soon be promoted to a release candidate.
Here's what you'll find among the most important new features in SQL Server in SP4:
64-bit enhancements. 64-bit hardware is slowly becoming the norm, not the exception. Consequently, SP4 includes newly added platform support for the Advanced Micro Devices Inc.'s AMD64 and Intel Corp.'s extended platforms in the WOW (Windows-on-Windows) subsystem, as well as big fixes specifically for the 64-bit version of SQL Server. It allows people to run the 32-bit version of SQL Server now and will let them transition seamlessly to the 64-bit version later when needed. (This is also the first SQL Server service pack to patch the 64-bit release of SQL Server in general.)
MSXML 3.0SP6 is included. XML support in SQL Server is becoming more important and more widely used, so it makes sense to have support for it rolled into service packs from now on. Service Pack 3 (and 3a) for SQL Server 2000 didn't include MSXML, and you had to install it separately. Service Pack 4 includes MSXML by default and includes the 3.0 version which contains some performance enhancements over the previous 2.6 release. However, SQLXML 3.0 SP3 will not be included in Service Pack 4; you will need to download and install it separately (after adding SP4) to make use of it.
Bug fixes. More than 230 bugs have been repaired or addressed, as opposed to only about 80 in Service Pack 3. Some of the fixed bugs deal with incompatibilities or breaks in functionality that appeared in Service Pack 3. For instance:
-- Merge publications cannot synchronize on SQL Server 2000 Service Pack 3. This reports as a "permissions error," but it is in fact because of a problem with merge replication in databases with binary collations.
-- DTS Designer may generate an access violation after you install SQL Server 2000 Service Pack 3. Admins who relied on DTS to streamline common bulk-copy operations will be glad to know this is fixed.
Several of the repaired bugs are important to everyone, not just administrators:
-- "Performance monitor shared memory setup failed: -1" error message appears when you start SQL Server. One common complaint about SP3 was that performance counter data was no longer collected if certain programs (such as the Performance Monitor) were left running when SQL Server was stopped and restarted.
-- SQL Server 2000 protocol encryption applies to JDBC clients. In the past, Java Database Connectivity in Windows did not allow encryption because JDBC could not make calls to the appropriate APIs. SP4 repairs this.
-- "There are fewer concurrency problems during heavy usage of tempdb. This tended to affect databases that generated many temporary tables at the same time.
A complete list of the repaired bugs is available in the Microsoft Knowledge Base.
Bear in mind that if you are planning to make a clean deployment of SQL Server 2000, it should be possible to obtain SQL Server 2000 with Service Pack 4 pre-slipstreamed into it from Select, Partner and MSDN sources. Microsoft also plans to allow installation media to be slipstreamed by the user for manual deployments in SQL Server 2005 and greater.
Note also that if you are using SQL Server 2000 Analysis Services, there is a separate service pack for this product. Not everyone uses the Analysis Services with SQL Server, so it makes more sense to deploy it as a separate service pack anyway.
Serdar Yegulalp is editor of The Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!
