|
|
|
|
 |
 |
|
|
| 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 Bob Jackson wrote about in his 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. |
|
| Top of Page |
|
|