What's the right way to "test" a SQL Service Pack?

  • I've read a lot about testing service packs, and not just blindly installing them and hoping for the best...

    What is the correct way to test? Is there a recommended checklist kind of thing?

    Any input appreciated.

  • Let me first compliment you on not just installing them blindly in production and hoping for the best. You should test them and make sure they work first. I have seen SQL 2005 SP4 installation completely break a SQL Server, so you should always test.

    I test by first setting up a test server and patching it up to the level of production. I then gather a collection of databases that's representative of the databases in production and migrate them to the test server. This could be a few or all of them, depending on your situation. I then have the developers test their applications against the test server. The amount of testing will usually be determined by management, but it should be enough that the developers are comfortable that their applications will work properly. Don't forget about database jobs that run weekly or monthly. Depending on your server, there can be an awful lot to test and it may take a long time, but testing and finding problems in test sure beats finding problems in production.

  • Thanks Ed...makes sense.

  • I know you marked Ed's answer as correct, and I agree it is. I'd just like to add that I consider service packs a code change so any testing that would be necessary for a code change to my application(s) would be what I would want to put my service pack through. For in-house code this is typically, apply and unit test in dev, promote to QA, promote to UAT, and, once all issues are solved, promote to production. Granted a SQL Server may host databases for 10 applications and you have to test all 10, but that's how I'd recommend doing it.

  • Not everyone can afford the luxury of multiple boxes even if they're VM but I keep a Dev, Staging, and Prod systems up and running at all times. My first step is to have NetOps backup the Dev box while I'm doing full SQL Backups of all databases and then apply the CU, SP, or what have you. Then, full regression testing is done by QA. Once that's done, same thing with the staging box. QA does a smoke test there and then many other groups are added to the regression testing. If it makes it through all that without unexpected incident, then same thing for prod with the necessary scheduled outage.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply