Automated Changeset deployments

bobprobst

We use RedGate SQL Source Control to TFS with a shared database and we use RedGate object locking to ensure people don't overwrite Dev Database changes.  

Every push to our Test instance requires a RedGate migration script.  We have a team lead (me usually) gate-keep this as he's the only one with rights to change Test.  We link our migration script changesets to sprint migration tasks IN TFS with the #A<Item Number> during check in.  When we push them to Test we run them in the creation order and Test is guaranteed to match Dev. Even if we have to iterate through several changes to an object, the scripts will apply each of those incremental changes and your final state will be accurate.  

In QA, we run all those scripts again in the same order and so on to Prod. 

Is there an automated way to apply these changesets to higher environments without opening and running each one?  Sometimes we end up with a couple dozen migration scripts and our DBAs get grumpy.

AlexYates

I would change your process. It's inefficient for various reasons.

For clarification, I'm assuming when you say ''migration script" you are not referring to Redgate SQL Source Control "migration scripts" feature. It's an overloaded term so I just want to be sure that's not what you meant. If it was we should talk about that.

First you need to decide if you want to use a migrations or a model based source control strategy. Currently you have a form of hybrid which is highly inefficient. If you really want a hybrid approach there are better ways to do it.

More info:
ore /workingwithdevs.com/delivering-databases-migrations-vs-state/

If you would like to go with model, you should look at the DLM Automation release objects and you shouldsprobably create fresh DlmDatabaseRelease object against prod or staging for each build. (Other strategies may also work but this would be a good start.) Then you will just get a single upgrade script and a handy diff report for the DBA. But you may or may not wish to consider leaving the release creation to nearer the deployment day.

If you would like to go with migrations, use ReadyRoll instead of SQL Source Control.

(Also, I highly recommend moving to the dedicated model. It'll make your life much much easier: http://workingwithdevs.com/shared-vs-dedicated/)

There are a lot of concepts here so if you'd like to talk through it feel free to contact me for a free 30 mins Skype chat. You can contact me either through any of the links in my signature or through the enquiries address on our website:
www.dlmconsultants.com
(Ask for Alex.)

AlexYates

Ruddy smily faces changing the meaning of my reply...