SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
SQL Server Service Packs - When Should You Apply One?
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version
A New Service Pack is Released
You arrive at your desk and download your email to find a newsletter telling you Microsoft have released another Service Pack for SQL Server. You have a server room full of production SQL Servers and a fair few development and test boxes too.

The service pack is out today. If you apply it now, what are the consequences going to be? You're not sure, so you decide to wait. But just how long do you wait? When is the best time to install a new service pack? This can be a difficult question for both junior and seasoned DBAs alike. On the one hand the service pack may fix critical security bugs or performance problems; on the other hand, will it break your systems and leave you with a crisis on your hands?

There was a major problem in Service Pack 2 which I wrote about in the blog Re-writing History with SQL Server 2005 SP2a. Microsoft then tried to fix it in SP2"a", but caused further confusion as running an @@version revealed that both service packs had the same version number.
We Ask Our Expert DBA, When Should We Apply a Service Pack?
It's a big responsibility making the decision to apply a service pack, so we put together a list of questions to ask our expert DBA for his advice.

Q: Before applying the latest service pack, I want to find out which versions my SQL Servers are currently on. Is there a quick way to do this across all servers when I have a high number of installations?

A: Well, there are an awful lot of tools out there which will do it at a price, and although SQLPing is probably the quickest and cheapest way to achieve this, to date it only reports base installation versions, not service packs due to the way Microsoft's own code works. So perhaps the quickest way to do it is to set up a linked server to all of your SQL Servers (if you're not sure you know where they all are, run "osql-L" or "sqlcmd –L" from the command line), then run select @@version against each one and dump the returned number into a table.

Once you have all your servers and the version numbers in a table, you can look up the version numbers to check which service packs, if any, are installed.

Q: What's the best way to document which servers have which service pack? Do I need to do this? Can I run different service packs for different servers or should I keep them all on the same version number?

A: Documenting which servers have which service pack can be easily done using the technique described above. If you want it to run daily or monthly, pop the code into a stored proc and call it from a scheduled job. If you want to get really fancy, call the stored proc from Reporting Services and get it to email you a pretty report, maybe highlighting any servers where the version number does not equal the maximum version number.

It's a good idea to keep all your SQL Servers on the same service pack, however this isn't always possible. If you have third party software installed, it may be that the vendor only supports a specific version, so it may be that some servers don't get upgraded with the rest.

Q: If I'm going to wait a while before applying a service pack, what are the best web sites to check out if there are any problems?

A: There are loads of good SQL Server sites out there, but there are also a couple of good techniques you can use. Before you apply a service pack, run a query against a search engine such as All The Web or Google (if you don't mind the adverts), such as +"sql server 2005" +"service pack 2" +problem and see what others are reporting.

Or set up an account at KBAlertz.com and dedicate it to sending you SQL Server alerts.

Q: What about Hotfixes?

A: Hotfixes are generally not recommended for most sites. They are an interim measure designed to plug a gap for customers with a very specific problem, prior to the release of the next major service pack. As a general rule, they are to be avoided. However, in the event of a major problem such as the SQL Slammer virus, it may be prudent to apply a hotfix.

However you should do it calmly, think about it thoroughly before you begin and only apply it if there is a pressing need for it. Otherwise, avoid them at all costs and await the next service pack release.

Q: My production servers are running fine. They ain't broke is what I'm trying to say. Do I really need to service pack them?

A: No. In fact sometimes you absolutely should not apply a service pack. "What?" I hear you say. Well, if you're running any sort of third party software such as an Accounts or HR package on your SQL Servers, the chances are that the vendors will not support new service packs until they have had a chance to evaluate and familiarize themselves with the changes that they introduce to their software.

Sometimes this can be a few months after a release, sometimes 12-18 months. The reason is simple: if you service pack a SQL Server and the service pack introduces problems which did not previously exist, your vendor is going to spend an awful lot of time dedicated to debugging code which has stopped working because of a bug that Microsoft may have introduced with the service pack. And time is money, which is a cost to your business’ bottom line.

However, do be aware of what each new service pack fixes and if the fixes are relevant to your servers. If you think an issue with a third party package can be fixed by applying a service pack, run a test against a non-critical test box, or inform your vendor. But NEVER apply it to a production system until you get the go ahead. Otherwise you may find yourself explaining to your boss why the support package you pay $10,000s per year for has been invalidated.

Let's face it: applying service packs is a pain in the neck. All that clicking to accept and confirm, entering awkward user names and passwords, what DBA needs it? However, there is a simpler way. Take a look at our article, How to Upgrade SQL Server Instances Quickly and Quietly for details about how to speed up the service pack application process.

Q: What can you tell me about the service packs that have been released for SQL Server 2005?

A: SQL Server 2005 RTM, version number 9.00.1399, was the base release, so this is before any service packs. This version is ok, with no known problems and no major security issues.

SQL Server 2005 Service Pack 1, version number 9.00.2047 is also ok to install.

However, Service Pack 2 should not be installed. This contained some pretty severe bugs which Microsoft had to fix when it released Service Pack 2a shortly afterwards. So, it's always wise to wait a while, if only a few weeks. The phrase "Fools rush in where experienced DBAs fear to tread" would be appropriate when it comes to service packs.

I'd recommend you sit back and wait when a new service pack is released. Let others install it first, or indeed install it yourself on a test server and give a thorough check over. Search the web for problems that others have experienced and wait until you are comfortable before installing on your production servers.

Q: I'm ready to install, what next?

A: Once you've made the decision to go ahead and apply your service pack, take a look at our article SQL Server Service Packs: How to Apply them in Development and Production Environments.
If you found this article useful, please visit our sponsors who keep us going...

SQL Enterprise Monitor 2009
This tool gives you unlimited server monitoring at a really great price!

Ensure critical services are running, compare enterprise-wide security and configuration settings, find troublesome queries and resource-intense database activities.
Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form