I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the current version of the schema, and I want to create a single script that upgrades the schema from any earlier version to the current version.

Something like this (pseudocode):

if (select v from version) = 1
    create table newtable ...
    update version set v = 2
if (select v from version) = 2
    alter table newtable add column newcolumn ...
    update newtable set newcolumn = ...
    update version set v = 3
if (select v from version) = 3

There are many problems here.

Mixing DDL and DML can lead to problems, like adding a new column and trying to update it in the same batch will cause an error that the column doesn't exist. So I thought that I should always separate them into different steps, and each step should be a different batch. Of course, every change step will end in a DML to change the version number, but that's okay. There should be transactions inside the DML batches, and let's not worry about the DDL batches.

I also want to make sure that during one execution, only one step is executed, because error handling could be a nightmare. Stopping the script after one step is also not trivial, neither in SSMS nor in SQLCMD. In my above sample, if we start at version 1, it will be updated to 2, then 3, then 4, etc. So I thought I would reverse the order of changes. First comes the change from 3 to 4, then from 2 to 3, then from 1 to 2, this way, only one step is executed at a time.

Does this look okay? Are there other things to consider?

  • 3
    Check out tools like Liquibase or Flyway. May 7 '14 at 9:20
  • DML will change the version number, not DDL May 7 '14 at 9:25
  • @Mark thx, fixed it.
    – fejesjoco
    May 7 '14 at 10:03

I did something very similar, but not as a single script. I used migrations. I also described this in Version Control and your Database. And indeed, every step is a different script, and the application drives the upgrade (the migration). Every step is tested. Every change is an upgrade, be it DDL or DML, it matters not. There are DML changes like changing some catalog entries or application lookup tables, these are upgrades. Of course, I'm not talking about DML for the actual application content.

Mixing DML and DDL should not cause problems. Scripts can contain multiple batches. Mixing DML and DDL inside a transaction can lead to problem though, and should be avoided.

Do not try to make the scripts idempotent (safe to run twice). That is going to add a tonne of problems like check if table exists etc. Do not try to make the scripts ACID (either all upgrade succeeds, or nothing); it is impossible. In case of error, revert to a backup. If the cost of restore is prohibitive (huge DB) then you should have a good battery of tests.

Test the scripts with significant size DBs so you don't run into size-of-data operation surprises in production upgrade.

Oh, and if these erorrlog messages look familiar:

Converting database 'x' from version 611 to the current version 655. 
Database 'x' running the upgrade step from version 611 to version 621. 
Database 'x' running the upgrade step from version 621 to version 622. 
Database 'x' running the upgrade step from version 622 to version 625.

that is because this is how the SQL Server itself takes care of schema versioning.

  • After I asked the question, that project was abandoned. A few months later, I ended up using Entity Framework and EF migrations. The migrations that you linked look very similar.
    – fejesjoco
    Nov 11 '14 at 21:08

This is just my 2 cents worth...

The way the company I work for currently does their changes is by keeping each script separate and having every script re-runnable, therefore if you notice an issue with a piece of code, there will not be a problem altering it and re-running the whole script.

So we wrap every DDL statement inside a check to see if the change has already been made:

IF OBJECT_ID(N'dbo.my_table', 'U') IS NULL

Otherwise, with the way you've currently got the setup, if you make a mistake in version 2 and accidentally omit a key DDL statement, you cannot just add it to that version and re-run the entire v2 script. Firstly because the version number would have updated to 3, then, even if you alter the version number back to 2, the following line would throw an error as the column already exists:

alter table newtable add column newcolumn ...
  • I started with the assumption that whenever I ship a script, even if it's wrong, it is final and will not be changed. One version means one schema only. An error is always fixed in a new schema version. I think it's actually easier this way. I want to keep everything in one file because the order of the scripts matters. I want to run things automatically, where the administrator only has to acknowledge that everything ran fine.
    – fejesjoco
    May 7 '14 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.