As we all know, SQL Server SP4 arrived on the scene late last week.  It’s been a LONG time since we’ve had a major service pack release for SQL Server 2000, so this service pack is very large.  If you look at the release notes, the service pack fix list includes 285 fixes for SQL Server and an additional 90 fixes for analysis services.  You can find the complete fix lists, along with release notes and the service pack downloads here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en

Here are some snippets I noticed in the release notes and fixes that are interesting:

• SQL Query Analyzer will permit connections to SQL Server 2005. However, some functionality may not be available.  That’s still really kewl!!!
• FIX: Concurrency enhancements for the tempdb database
  • This particular issue caused us a LOT of issues at my current employer, so it’s good to see this as part of the fixes.
• There are a lot of issues related to cursors.  Imagine that.  :)  Reading this fix list should give you several more reasons to avoid cursors when at all possible.  They SUCK!!! 
• Profiler (we hope) will finally return the CPU counters correctly.  This has always been aggravating.  Hopefully, they also fixed the issues with functions not showing up correctly in Profiler when called from stored procedures.

As with any of the service packs they have released for SQL Server, thorough testing should be completed before rolling the service pack to a production system.  There have been service packs in the past that didn’t exactly go smooth, if any of you were around in the 7.0 and pre-7.0 days.  In addition, they usually catch some things during the first few weeks they missed or didn’t get quite right (SP3a?).  Here is the general release roadmap we are currently planning:

1. Review the release notes and fix lists to determine what needs to be tested at SQL Server and applications levels before releasing service pack to any environment. 
   1. Review release notes and fix list.
   2. Monitor MS and SQL Server forums for issues.
   3. Make a formalized test plan from review.
2. Release to development environment and test for two weeks.
   1. Have DBA teams and applications teams test and signoff on the test plan when complete. 
   2. Work through issues as encountered and document.
3. Roll to QA and test for an additional week.
   1. Have QA team test and signoff.
   2. Work through issues as encountered and document.
4. Roll to UAT, Release and Production.
   1. Complete final test of environment and major production applications after release.

When we complete the review and have the test plans created, I will add them to the blog.  These are not extensive test plans.  They are created just to insure we test all the major components and processes we believe might be affected after our review of the service pack documentation.

As one last item to cover on the service pack, if you haven't gone through the process of upgrading MDAC components throughout the environment, then you need to get a test plan together and get it done.  Old MDAC installations caused a host of issues with the latest SQL Server service pack release in our environment.  In addition, there have been a lot of important performance, stability, and security fixes throughout the MDAC release cycle.  At a minimum, all MDAC components should be at 2.7 SP1 Refresh even before the upgrade.  With the upgrade though, you should be up to the latest version.  You can find out more about this by reading the release notes for SQL Server SP4 and MDAC 2.8.  Here is the download site for all MDAC components:

http://msdn.microsoft.com/data/mdac/downloads/default.aspx 

UPDATE #1:  (20050516)

Well, I'm glad we didn't install anything yet.  :)  We're still smoothing out the details of the test plan.  We got this little notification from Microsoft about SP4 not working very well with AWE enabled systems though:

http://www.microsoft.com/sql/downloads/2000/sp4.asp

I seem to remember saying something earlier in this post about being careful and service packs not going very well sometimes.  We'll keep watching this one and keep you posted.  Hopefully, nobody rushed to install it on their production systems with massive amounts of RAM.