SQL Server: Upgrading databases

Question

We've got a product that uses a Microsoft SQL Server database. We've got customers using various older versions of the product.

How do people write/structure their database upgrade scripts in this situation?

Answer 1

You need a set of release-to-release patch scripts. On production systems with live data this is a bit trickier as you may also have to manipulate data in these scripts.

Redgate SQL Compare Pro is quite a good tool for generating these scripts or doing QA functions on scripts (obviously you shouldn't really use it for both roles). Because you have live customer data involved you will have to regression test the upgrades.

I don't think there's an easy way to do this. You will have to develop the script and test any data manipulations that it does. Do the tests from version to version for some useful set of upgrades. The key here is to arrange a production mirror test environment that you can test the patches in before you apply them to a live system.

P.S. don't forget to back up and test restore on the production database before you actually finally run the patch script, but you already knew that ;-}

Answer 2

We have one SQL script that does both a clean installation and an upgrade. After installing SQL Express or full SQL server, we run this script that does the following:

1. Creates the tables if they don't exist with CREATE TABLE.
2. Delete and recreate all stored procedures
3. Make modifications to tables such as field changes, field additions etc.

We keep on extending this script, so each release will only add to this SQL script file. This means we can run this script on production systems to update them to the latest schema use the script for new installations to create the schema from scratch.

This way we're guaranteed that the upgrade script will bring even the oldest installation up to the latest version.