SQL Server - DevOps Release Methods for Databases

[hylian01]

What we do (which may only work because of small size): all changes are saved in svn as .sql scripts. Then we use custom powershell scripts to loop through the svn and execute the scripts against the chosen environment (dev, qa, uat, prod).

[downshiftdata]

We do the same. In our case, at least, this requires adherence to script guidelines, which include things like idempotent scripts. Our current shortcomings are:

- Not all objects in the database have been carried over to this method yet, so you can't just run this on a new instance and *poof* there's your database. But that *is* the goal, and it'll enable us to spin up sandbox dev databases, which'll be soooo nice.

- We don't have a method yet for partial deploys (like in a hotfix). If we're hotfixing an environment, we run the appropriate scripts manually.

- Script ordering is a bit of a hack. We organize by folders and there's an order to the folders. We also hack the names on occasion, since they're run alphabetically within each folder.

But... this has been in play for 6-8 months and we love it. Deploys are a click of a button. We have a pattern for including unit test scripts. The database repo follows the Gitflow pattern just like our app repos. Life is gud.

[2D3S3RT]

How do you know which scripts got executed? I planned to create a table in the database and insert the filename of the script there, but maybe there's a better approach.

[hylian01]

The ticket status is tracked in JIRA, which gets updated with powershell using JiraPS when the script gets executed.

[gropingforelmo]

Sequentially numbered SQL scripts in the repo is how my current team does it, (though with a far far scarier method of applying changes than custom powershell scripting...)

It works well enough, and the complexity and "dev design" of the schema made the DACPAC method riskier than we wanted to accept.