If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

How do you audit automatically generated sql deployment scripts?

I saw recently an email where someone stated that the reason that they manually create deployment scripts is that because of the restirctions put on them by the requirements for auditing and the ability to be able to demonstrate exactly what was deployed at what time.

When automating deployments it really makes sense to use a tool to generate the deployment so you can concentrate on writing productive code instead of deployment scripts. How do you balance the ideal of spending less time writing deployments but also ensure you can audit what happened?

If your deployment process looks something like:

  • 1. Compare source and destination + Deploy changes

Then you don’t have much chance, but if you split it into two discrete steps:

  • 1. Compare source and destination + Create deploy script
  • 2. Run deploy script against target

Then this means that you automatically have an audit trail of what was deployed (the deployment script). You also get the added benefit that the deployment script can be manually reviewed and a friendly dba can run it manually or automatically if they have the capabilities.

If you are writing a deployment pipeline for a database and you miss out the continuous delivery step and go straight for continuous deployment then life is actually a little more complicated, especially from an auditing / non-trust of tools point of view.

Subscribe

* indicates required

Please select all the ways you would like to hear from Agile Sql Club:

You can unsubscribe at any time by clicking the link in the footer of our emails. For information about our privacy practices, please visit our website.

We use Mailchimp as our marketing platform. By clicking below to subscribe, you acknowledge that your information will be transferred to Mailchimp for processing. Learn more about Mailchimp's privacy practices here.