If you write code to SQL Server then you might be interested in this: at the end of 2017 I wrote a tSQLt tdd training course which has helped over 300 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. The course is free if you are happy to wait 10 weeks to complete it, with 1 lesson being made available per week - if you are in more of a hurry or you would like to help support the project you can purchase the course which makes it instantly available to you.

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.

Manual

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