Although there is sound logic in applying a service pack to these machines before a production box, if something goes wrong on a development or test server, the time taken to rebuild and reconfigure them can be far longer than for their production counterparts, as they often have other software installed on them such as remote debuggers, server side profiling tools etc. So whilst your customers may not see problems when they hit your web site, you could be delaying a critical release or damaging a service level agreement for version upgrades by not thinking this one through. So, what do you do?

My advice would be to have a mini-development box where service packs can be applied in a 'sand-pit' environment. This basically means that you can play around with it and break it, and it's not going to matter. You don't have to have a high spec machine for this kind of box either, it can be any kit which supports your server side software and provides an adequate response time to a small number of users. An old desktop PC with a 1Ghz+ CPU and a couple of gig of RAM will suffice.

If it's a simple environment you're replicating, you can easily burn an image of your current development box onto DVD and restore it onto the new one. Once it's up and running, apply your latest service pack, point a developer at it and run some tests. Then make it available and let developers know that it's theirs to trash (you can always dig out the DVD again if the worst happens).

The important lesson to learn here is that even if this server becomes unusable, the developers can still develop and the testers can still test. There's no loss of productivity, as the main development and test boxes are still available, albeit without the new service pack applied.

Once this server has been used for a couple of months and no howling errors have been spotted, a formal set of tests can be run against it to provide a more "official" degree of confidence in the new service pack code.

The best way to handle this is to apply the new service packs in the same way as has been done for the development and test boxes. Simply image an existing production database server and restore it on another box. If you have the luxury of connecting a spare web server to it to run tests, do it. Ideally, use a test web server that's used to test your web side code before it goes live, just point it at the newly restored image of your production server instead of your normal development/test database server.

This way tests can be run against it without compromising the production server that customers see, and at the same time preserving the development and test environments, allowing developers and testers to get on with their jobs in the event of something going critically wrong.

Hardware is inexpensive at the moment and you don't need a multi processor, fully RAIDed server to perform these tests as you're code testing, not load-testing. Therefore a decent speed CPU with a stack of third party memory chips and a load of disk space should suffice. This also keeps the time taken to setup and configure hardware to a minimum, and reduce costs so that it doesn't frighten your boss at budget time.

At Norb Technologies we recently experienced some interesting problems in our development environment as a result of service packs being out of sync on a development box and the SQL Server. You can read about it in the article Job Details Vanish in SQL Server Management Studio.

Bob Jackson also wrote a blog about the problems in SQL Server Service Pack 2 and 2a. Re-writing History with SQL Server 2005 SP2a explored the coding problem inside SP2 which caused maintenance plan problems and a major issue with running select @@version.