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.

Approaches to deploying Sql Server database code

When you deploy your Sql Server code, there are two approaches, there is the manual way and the automated “compare & deploy” way.


The manual way is where you track what changes you have made and when you want to release, build a script or set of scripts which contain the updates. If you are modifying objects then you should use alter or drop/create and you need to manually create rollback scripts (should you need them).

I don’t like the manual method because:

  • If you work in a team, someone needs to co-ordinate what changes are being deployed
  • It is really easy to forget something and not include it in the deployment
  • The change scripts for each deployment are stored and to get to a particular point in time, you need to apply the base scripts and then all the updates
  • Manual = Error prone, I have no statistics to show you but it is a fact, believe it

I do like the manual method because sometimes you want to do something that a tool cannot, for instance you want to archive data so you can do things like migrating it in chunks or in smaller transactions. You might have a better way of adding a table or be better at working out what operations can be done on or offline such as changing some columns metadata.

Compare / Merge

I like the compare merge method because:

  • I can work in a team, we can all check our code in and a tool works out what code to deploy.
  • No one has to gather the changes and can forget to include something, if it is checked in then it goes in the deploy
  • I can include whatever manual scripts I like because I “build the deployment pipeline and I decide”
  • I can use the same system to generate rollback scripts automatically
  • I trust the tools I use (sqlpackage.exe or sqlcompare.exe) and they are not error prone
  • It is really easy to get a database into the current state, like 1 step setup easy

I don’t like the compare merge method because I sometimes have to think about changes and manually script them.

Manual is Sad Face Automated is a Happy Face


* 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.