Database Change Management - Setup for Initial Create Scripts, Subsequent Migration Scripts

Question

I've got a database change management workflow in place. It's based on SQL scripts (so, it's not a managed code-based solution).

The basic setup looks like this:

Initial/
    Generate Initial Schema.sql
    Generate Initial Required Data.sql
    Generate Initial Test Data.sql
Migration
     0001_MigrationScriptForChangeOne.sql
     0002_MigrationScriptForChangeTwo.sql
     ...

The process to spin up a database is to then run all the Initlal scripts, and then run the sequential Migration scripts. A tool takes case of the versioning requirements, etc.

My question is, in this kind of setup, is it useful to also maintain this:

Current/
    Stored Procedures/
        dbo.MyStoredProcedureCreateScript.sql
        ...
    Tables/
        dbo.MyTableCreateScript.sql
        ...
    ...

By "this" I mean a directory of scripts (separated by object type) that represents the create scripts for spinning up the current/latest version of the database.

For some reason, I really like the idea, but I can't concretely justify it's need. Am I missing something?

The advantages would be:

• For dev and source control, we would have the same object-per-file setup that we're used to
• For deployment, we can spin up a new DB instance to the latest version either by running the Initial+Migrate, or by running the scripts from Current/
• For dev, we do not need a DB instance running in order to do development. We can do "offline" development on the Current/ folder.

The disadvantages would be:

• For each change, we need to update the scripts in the Current/ folder, as well as create a Migration script (in the Migration/ folder)

Thanks in advance for any input!

Answer 1

I think it will just make things more complex in the long run. Whole versions need to live in a single script so that you can test that script in one context and know it will work correctly in another context like production.

Answer 2

Actually, this is the best way. As cumbersome as it may sound, it is better than the alternatives of using SQL Compare like tools or VSDB .schema file deployment. I have argued for exactly the smae approach for some time now: Version Control and your Database. My apps deploy the v1 schema from the initial script, then run upgrade script for each version. Each script know how to upgrade from version N-1 to N, and only that. Final result is the current version.

The biggest draw back is lack of an authoritative .sql file too look to find the current version of any object (procedure, table, view etc). But the advantages of being able to deploy your app over any previous version, and the advantage of deploying by well controlled and tested scripts far outweigh the drawback.

If you feel bad for using this deployment process (script to deploy v1. then apply v1.1, then v1.2 ... until finally you apply v4.5, current) then keep this in mind: exactly the same process is used by SQL Server internally to upgrade the database between releases. When you attach an older database, you see the famous 'database is running the upgrade from version 611 to 612' and you see that the upgrade goes step by step, does not upgrade straight to current version 651 (or whatever is current in your case). Nor does the upgrade runs a diff tool to deploy v 651 over v. 611. That is because the best approach is the one you just use, upgrade one step at at time.

And to add an actual answer to your question, after posting a rather oblique rant (Is a topic I have strong opinions about, can you tell?): I think is valuable to have a scripted version of the current version, but I think it should be a contiguous integration build process deliverable. In other words, your build server should build the current database (using the upgrade scripts) and then, as a build step, script out the database and produce a build drop with the current version schema script. But those should be only used as a reference for searching and code inspection, not as a deployment deliverable, my 2C.

Answer 3

Martin,

If you're in the real world, then your production database only accepts updates - you never "create" it from scratch. So the most important thing for you to be storing, watching, reviewing, etc. is the set of update scripts. Those are the scripts that will make it to production, so those are the only ones of real importance.

You're doing the right thing by making them primary. But developers needs to be able to get a "current picture" of what the schema looks like. DBAs like to do this, too, although (too) frequently they do it by logging in to the production servers and firing up some kind of gui tool. (yikes!)

The only reservation I have about your approach is the current/previous schema by object type. Those scripts should be generated automatically, from dumping the database itself. If you can automatically categorize them by type, then great! If not, do what you can to make them easy to navigate, but the guiding rule should always be "generated automatically from a live database."