2

I have a database (SQL Server 2008 R2), which is mostly under source control (so one file per DB object, grouped together in folders e.g. tables, views, storedprocedures). At the moment, changes are made by writing SQL upgrade scripts, and then some homebrew batch files to execute them (and very error-prone they are too).

So, we are looking at whether migrations would do the trick for us, but I have yet to see a good explanation of the best practices. Most blog posts seem to assume an empty database, then throw in a couple of migrations (usually CreateUsers and CreateRoles), but then don't show what happens after that? If you have hundreds of stored procedures, do you want them, as we currently have them, in per-object .sql files, and then have your migrations reference those files? Are we mixing up state-based deployments and migration-based deployments?

In other words, if we are moving to migrations, should we have a single SQL file that creates our entire database at a certain known state (snapshot #1) (with hundreds of tables and hundreds of stored procedures), write a whole bunch of migrations over the course of a major project, then at the end of that project, create a new snapshot, and add that to source control? So the only thing in our VCS is the snapshots, and the migrations that move us between snapshots? But then, how can you trace the history of the e.g. Users table, if you don't have the individual objects under version control?

David Atkinson
  • 5,759
  • 2
  • 28
  • 35
David Keaveny
  • 3,904
  • 2
  • 38
  • 52

1 Answers1

4

You might be interested in the approach taken by ReadyRoll (commercial) or Flyway (open source). Full disclosure: I work for Redgate who make ReadyRoll.

These are both tools/frameworks that help you organise migration scripts and take a lot of the legwork out of it for you. For example deciding which scripts to run when you make an update.

ReadyRoll is a hybrid approach.

It specifically tackles the problem of lots of stored procedures (or other programmable objects) changes by source controlling the 'state' of these objects. e.g. just keeping the last version version controlled as a drop/create. For table changes it stores a numerical sequence of alter scripts in a migrations folder.

So that you have a per object history it automatically creates and stores the create script for every object as a 'schema model'. So you can look in your VCS history and see how objects have changed over time.

There's an good series of blog posts on different techniques for version controlling databases by Vladimer Khorikov.

Jon
  • 583
  • 3
  • 13