I have at least a passing interest in how to manage databases and version control.
I want to compare two different approaches to version control of databases. There are two major challenges with managing databases: version control and deployment. This post focusses specifically on tools for SQL Server, but the same issues would exist for most relational databases.
SSDT – SQL Server Data Tools
SSDT is the successor to ‘Data Dude’ from Microsoft. It focusses more on the version control side of managing databases. It integrates with Visual Studio 2010 and later, including TFS version control. You can point it at a database and say: generate a model from this. You can ‘build’ databases with syntax checking of your databases, generating errors & warnings when missing tables or columns are referenced. This is basically just another type of Visual Studio Project.
The tool manages tables, procs, triggers, functions etc as create statements. In other words, tables are stored as:
CREATE TABLE MyTable
procs, functions etc are similarly stored.
Deployments are managed by comparing your version controlled model to the database you want to upgrade. SSDT provides options to either generate a script to upgrade the database (in SQL CMD mode) or it will execute the upgrade against the database.
DbUp concentrates solely on managing upgrade scripts and the process for running those scripts.
DbUp is not concerned with syntax checks or version control.
DbUp focuses on ensuring that upgrade scripts are run just once on the database. Each script is stored in the version table, if the script has already been run it doesn’t run the script.
Comparing the approaches
The two approaches are almost polar opposites. SSDT focusses on version control, DbUp focusses more on deploying changes.
Why the SSDT approach is great
You can treat tables/procs/etc like any other piece of code. You can see a history of all checkins that have been made to a table, when columns were added etc. You can run a comparison of what was there before against what is there now. You can also see a snapshot in time of what was there.
This is very much focussed on the version control.
Where the SSDT approach falls down
When it comes up upgrades, SSDT will generate a single upgrade script. This has the following problems:
- You are trusting SSDT to generate an upgrade script that will work. In some cases (primarily when there is data in the database) it is impossible for SSDT to generate a valid upgrade script. I’m not too comfortable with trusting the magic tool to generate a valid upgrade script
- The upgrade script is a single script. If the script fails part of the way through (eg through a change to the database between the time the script was generated and the time it was run), you have a major problem. And your major problem is occurring at the worst possible time: deploying to production.
- The deployment is not repeatable. Any script that is generated is specific to the database being upgraded. It is common to have a Uat/Joint test/Staging environment before production that is used as a testbed for deploying to production. Typically there is some variation between these environments, which can be handled by SSDT with command variables. As a result you will need to generate a different script for each environment. This means that the upgrades are not repeatable.
There are other failings of SSDT (worthy of a blog posting on their own).
Why the DbUp approach is great
DbUp ensures that upgrade scripts scripts are run against the database just once. This means deployments across multiple environments are repeatable.
Where the DbUp approach falls down
DbUp focusses entirely on the database upgrade to the detriment of version control. The reality is that in most cases databases include real code (procs, triggers, functions etc). Simply versioning changes makes it very hard to see what changes have been made.
This is an issue even for changes to tables. It makes it hard to see what the state a single table was at a given time and how that table has changed over time, given that you have collection of change scripts for a table.
There must be a better way…
DbUp is a deployment strategy with no real version control strategy.
SSDT is a version control strategy with a deeply flawed deployment strategy.
Wouldn’t it be nice if there was some sort of middle ground that provided the best of both worlds?