This is the 1st in a series of posts on SQL Server and Continuous Integration. Some of the tools I’ll be covering are:
- Git
- Visual Studio
- ReadyRoll
- GitLab
- tSQLt
- SQL Cover
Three months ago I performed my first public talk at my local usergroup in Southampton. I decided to base the talk on what I had been doing at work, source control and deployments for SQL Server databases. The talk went well and I have since done a 2 hour version in Exeter. This series is based on those talks and the work I have done since. I’ll be talking about the various tools I use to drive my Continuous Integration and how they fit together. I didn’t really have a budget so free tools feature heavily. Some tools worked straight away while others fell by the wayside. If you are interested in source controlling databases and consistently deploying their changes to production then this series should give you some food for thought.
Source Control for the Reluctant DBA
I had been using source control for years but it’s always felt like a tick box exercise that I was doing because I had to. I had never used it to review old versions to see where code went wrong or to quickly roll back changes if I decided I no longer wanted to go in a certain direction with the code. I never felt like I was getting anything back from using source control. Sometimes it takes a problem to arise for you to see the value of a solution.
In 2015 I started to inherit the code base for our internal maintenance database, the UtilityDB. This database is used to store performance metrics and to manage tasks such as index maintenance and backups. This database is installed on all of our instances.
Towards the end of 2015 I realised that we had a problem, some of these databases were suffering from schema drift. Some instances had additional tables and stored procedures, some had later versions of functions. This made deployment of new features difficult. Every deployment was becoming a bespoke job that took a lot of time. I couldn’t rely on the testing I had done in dev because those tests were against a different schema.
After becoming more and more frustrated with inconsistent schemas across our environments, and the headaches that it caused for deployments, I came to the idea that I needed to align all of our UtilityDBs so that they each had the same schema. This would allow me to reliably deploy changes that were developed and tested on a copy of the database in dev. If the new code worked in dev it could be expected to work in production. Part of this aligning process would involve having a master copy that was used as the single source of truth. It is this copy that all the databases in production will be aligned to. Suddenly source control became the obvious place for this and I was now aware that this box ticking exercise I had been doing would become the most valuable process in the whole development cycle.
Git
After this (likely obvious to you) realisation I began to experiment with the various tools out there to get source control to 
work for me. I had been using Git to a novice level for a few years to source control my script library. Based on this, and Git being the main focus of any presentation on source control I have ever seen, I kept with it.
Source controlling stateless objects like scripts, whether they are .sql, .bat or PowerShell is easy. All you do is get the latest version from source control, add some new functionality and upload it to source control. You then use the latest version in production. With databases it’s not as simple. Databases have states caused by the data they contain. We can’t store and track all of our data in every deployment of our databases in source control, we just track changes to the schema and code that hold and process this data. Dropping a column will drop the data contained in the column and alter the state of the database. If we decide that a change to a script is incorrect we can easily revert to the previous version, but with a database we somehow need to restore the state of the database to how it was before we made the change.
This is just the introduction to this series. In the next post I will cover the basics of Git and the tools that enable me to use it for my SQL Server projects.
The post SQL Server and Continuous Integration appeared first on The Database Avenger.
