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
end
if (select v from version) = 2
alter table newtable add column newcolumn ...
update newtable set newcolumn = ...
update version set v = 3
end
if (select v from version) = 3
...
end
...
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?
Best Answer
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:
that is because this is how the SQL Server itself takes care of schema versioning.