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

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.