Why we source controlled our databases

The initial reason we wanted our databases source controlled was to get rid of the conflicts arising because of the way we worked. To be specific, the way we worked was to manually create a change script and a rollback script for every change we made – for some deployments this meant we had to deploy dozens of scripts, all manually. Can you imagine the time it took for developers to handcraft each of those scripts, run them in a shared development environment, and then promote the changes to our test, acceptance and production environment? I’ll give you a clue – it wasn’t trivial.

Manual Changes are Slow and Boring

It meant the change code had to be written in one or multiple scripts, named and saved in a local repository, and then committed from the local repository to the central repository. After that, the script(s) had to be run against the development database. Actually, they had to be run against the development database twice, because the rollback script needed to be tested as well. Wouldn’t it be nicer to get rid of this hassle with scripts, and to make changes on the local version of the database we’re working on, and to commit our changes from within SSMS? Yes it would!

So we made some progress, and soon the change code was source controlled, but not the database code itself. Besides the fact that working this way was very time consuming, it also meant that developers had to be very disciplined in writing the change script according to an agreed upon standard (for example where to store the script, how to name it, which naming conventions for database objects, etc…). Even when developers agreed on a standard, sooner or later one or more would deviate from it – intentionally or unintentionally. Unfortunately, that is just what you get with manual work and that head-ache is, next to time-saving, another big reason why you should want to automate processes: to enforce standards.

You’ve Got to Have Standards

Standardization just makes your (IT) life so much easier and, when you enforce it by automation, deviations from the standards and the conflicts that causes typically become a thing of the past. Developers, testers and anyone else involved in the delivery process need not to be bothered with figuring out where files are, how they are named and if the code satisfies conventions. Instead, they are freed up to do what they were actually hired for. And best of all, as we discovered, there are tools to help accomplish this.

One of those tools is Redgate’s SQL Source Control. It’s an easy to install SSMS plugin which enables you to quickly source control your databases using your existing version control system. You can also use other tools like SSDT (SQL Server Data Tools), and we actually considered this alternative when choosing which path to take. One of our senior developers had even used SSDT in combination with Readyroll in the past for deploying database changes. However, SSDT has the disadvantage of not being able to create migration scripts (which is necessary in certain situations to prevent data loss), not able to source control data (you want to source control static data) and only allows you to work from within Visual Studio, not SSMS. A crucial factor for SQL Server DBA’s. There are some other advantages and disadvantages which I am not going to discuss here, because the important fact for our story is that our lead developer and I agreed to use Redgate’s SQL Source Control.

Why Source Control Your Application Code?

While the decision to source control our databases was based on the need to get rid of conflicts caused by the way we worked, having our databases in source control suddenly provided us with many of the same advantages as having our application code in source control. Besides having a backup of our database code, being able to retrieve a point-in-time version of that code, and providing an audit trail, we were now able to set up our Database Deployment Pipeline for Continuous Integration (CI).

How I set that up is for a future post – stay tuned!

 

 

3 thoughts on “Why we source controlled our databases”

  1. I specially liked the “need of standards” point of view and I can confirm the having standards/rules helps to reduce/eliminate conflicts.

    I used this post as reference to one of the reasons that I present on my post about database source control, hope you don’t mind.

Leave a Reply

Your email address will not be published. Required fields are marked *