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.