SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
SQL Server Service Packs: Applying them to Development and Production Servers
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
Introduction
You’ve decided to apply the latest service pack release from Microsoft to your development, test and production SQL Servers. So how do you go about planning how to do this? This article discusses how to apply a service pack to your environment, in order to have minimum impact on developers, testers and end users.
Test and Development Boxes
These boxes are often treated with a reasonable degree of complacency compared with production servers, but given that your code's going to be developed and then tested against them, it's important to treat them with reverence.

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.

Production Servers
This is where things start to get even more critical. Customers are going to see the results or problems here, particularly if the database server is the back end to a web server or service.

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.

The Cost
All of this may seem unnecessarily expensive, but if you consider the cost of just a few developers sitting around doing nothing whilst their database server is re-built, it starts to look like a much cheaper option after just a day of outage time.

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.

Conclusion
Whilst not the ideal solution for every single environment, for many companies the method described above will provide a useful platform for testing service packs prior to them going into a development, test or production environment. A little outlay at this stage will save a lot of time and problems in the future.

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.

I 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.

Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form