In my June 21 commentary, I discussed SQL Server 2000 Service Pack 1 (SP1) and shared some thoughts about service packs in general. I heard from many readers who disagreed with my advice. Crafty consultants never admit they were wrong. Let's just say that my original comments, although accurate, were based on a set of assumptions that might not have fully represented the wide spectrum of business scenarios the SQL Server community faces. (With lines like that, at least I have a career in politics if I decide to retire from my SQL Server vocation!)
My original recommendation was to apply the latest service pack even if you're not aware of any problems the service pack will solve for you. I still stand by the core of that advice, but I'd like to temper my original comments.
First, I'm more of a development DBA than a production DBA. Those roles are very different, and I believe you should manage service pack rollouts in different ways for different environments. You should preemptively apply service packs for systems under construction or systems that are changing rapidly. Your existing code might not be subject to a bug that the latest service pack fixes, but if you're writing new code all the time, you could easily run up against one of the bugs soon. If you've ever run through a service call with a Microsoft Product Support Services (PSS) engineer, you know that one of the first questions is, "Which service pack are you running?" If PSS can't quickly find a solution to the problem, the typical response is to see whether the newest service pack solves the problem. Personally, I'd rather apply the service pack in a proactive, controlled manner than react in troubleshooting mode. However, some code bases, such as those in an existing production environment, are mature and probably won't change in the near future. In such environments, "if it ain't broke, don't fix it" is sound advice.
Security patches are an exception to the "if it ain't broke" maxim. Did you know that Microsoft has issued two patches for security vulnerabilities in SQL Server 2000? Did you know that both patches are rolled into SQL Server 2000 SP1? I like simplicity, and I'd rather keep track of which service pack I'm using than try to figure out whether all my servers have all the security patches. You can find security bulletins for all Microsoft products on Microsoft's Web site.
The following reader comment made two other important points: "How can you possibly justify encouraging people to apply a new service pack 10 days after its Web-site release? At this point, how do you know that the service pack won't create more bugs than it fixes? My experience with such releases doesn't give me a great deal of confidence. My recommendation is that if you don't need something particular from the release, let it 1) age in the field long enough for overall quality to be assessed (30 to 60 days), then 2) install it as part of a planned maintenance program with formal test and backout plans."
Although I believe that you should proactively roll out service packs, I'm also 100 percent in favor of letting "the other guy" find service-pack bugs for me. Immediate installation might be appropriate in a pure development environment, before you ever release the application to production and before you perform the final quality-assurance tests. However, unless you know the service pack resolves a problem you're facing, let a service pack "cool off" before you apply it to a production system. Of course, it's also prudent to roll out the service pack to your development servers before you touch the production boxes.
Finally, I can't overemphasize the importance of having a backout plan. Read the readme.txt file for SQL Server 2000 SP1, and you'll find that you can't simply uninstall the service pack—a complete SQL Server rebuild is necessary. Never apply a service pack to a production system unless you're prepared to restore the entire SQL Server system from backup or explain to your boss's boss why the company's mission-critical application suddenly stopped working.