This is the 2nd post in the series named SQL Server and Continuous Integration. The previous post explained the problems I encountered at work and how they provided the inspiration for using source control in my SQL Server projects. This post will focus on using the version control system (VCS) Git with SQL Server. At the end of this post there is a very brief description of Git.
Your VCS is important because when you use it to push local changes to your central CI enabled repository it will trigger automated tests and possibly even deployments. The VCS you choose is not that important because most modern ones (Git, SVN, BitKeeper, Mecurial) can be used to drive your CI processes. I would suggest using what you currently use, but if you don’t currently use a VCS I would recommend giving Git a try.
Script library
The first use I found for Git, was to version control my script library. I used GitHub as my central cloud hosted repository and GitKraken as my Git client (see below for the list of clients I tried). This allowed me to access my scripts from any machine with an internet connection. I created a local repository in the folder that contained the templates for SSMS. This allows me to navigate my script library in SSMS with the template viewer. Check Russ Thomas’ post for more on this technique, although it looks like we will get a built in solution soon.
So scripts are easy, but how do we get our databases into source control? Essentially we need to script them out and then source control these scripts. We could do this manually but using a tool to automate this is much better.
First I started with Redgate’s SQL Source Control (SSC) plugin for SSMS. At first this seemed to do everything I needed but after a few weeks I started to hit limitations. The main problem I had was that my project involved two databases and that SSC uses a different repo for each database. This was causing me a problem with versioning. I found that I had to maintain a list of which versions of DB_1 were compatible with each version of DB_2. I wanted both databases in the same repo so I could version the project as a whole. SSC was the only VCS plugin for SSMS that I found so, as a DBA, I reluctantly turned to Visual Studio.
Visual Studio and SSDT
VS has a GUI for working with Git that is simple and efficient but IMO not as intuitive as some of the visual clients at the end of this post. SQL Server Data Tools is a plugin for Visual Studio that allows the developer to connect to a database and have it automatically scripted out into a SSDT project. This allows use of the Team Explorer UI in VS to source control the files with Git in a SSDT project.
SSC and SSDT require the use of compare tools to build deployment scripts. This is referred to as a state based migration. I’d done deployments like this in the past and saw that people reviewing the release found it difficult to review these scripts when the changes were more than trivial. For this reason, I decided to look at some migration based solutions. Migration solutions generate scripts during the development process that will be used to deploy changes to production. This allows the developer to break the changes down into small manageable individual scripts which in turn makes code reviews easier and deployments feel controlled. These scripts sit in the VS project and are therefore source controlled in the same way as the database.
DbUp
DbUp is an open source migration based console application. It’s a .net library that you include in your VS project. It generates migration scripts and maintains a ‘versions’ table so it knows which scripts have been deployed and which ones are pending. It’s built by the people at Octopus deploy.
ReadyRoll
A hybrid (state/migration) system that builds on top of SSDT that scripts out the database and adds a UI to build migration scripts. I instantly found the migration approach made much more sense to me (but that doesn’t mean the state based method is defunct) and I was soon generating migration scripts and testing their deployments.
Over the following weeks and months, I got excited, frustrated and then excited again. It took a while to fully understand and feel comfortable with the software. It’s not a magic wand that will magically remove all of your deployment issues over night, but I do believe it is the best way to manage database development similar to what I outlined in the previous post.
The next few posts will focus on the configuration, use and features of ReadyRoll.
Git
I’m not going to write a user guide to Git here as there are already great resources on this topic. I will give a brief explanation of Git and why I think it fits with database development so well.
Essentially VCSs are used by developers to make periodical snapshots of the files they are working on. These ‘snapshots’ allow the developer to review the evolution of each file and potentially discover when a bug was introduced and who introduced it. In this situation, it would be possible to rollback the erroneous changes to a previously known working state.
In most VCSs developers can create branches which are copies of the main project at the time the branch was created. These branches allow developers to work in isolation of other developers. The cost of this isolation is the process of merging the branch back into the main project when the work is complete.
Git is a Distributed Version Control System (DVCS) which means each developer has a complete VCS on their local machine that is referred to as a repository. This allows developers to make multiple snapshots (commits), revert changes and even create their own branches on their local machine. DVCSs don’t require developers to push every change to a remote central VCS. This means a lot of operations can be processed quicker as they are local and work can take place off-line.
DVCSs allow developers to work on their own copy of the database. This is really important as multiple developers working on a shared database can cause conflicts with each other.
In theory, DVCSs can be used to transfer patches between developers in a peer-to-peer fashion. In most cases a central repository is setup on a server that everyone can access. This central repo will be the one true version of the code that everyone synchronises with and will be used to build public releases. The price of this flexibility is the process of dealing with any conflicts that may occur when multiple developers commit multiple changes to the central repo.
Git was developed for Linux and the command line but it has been ported to Windows in a few forms.
Posh-Git
A PowerShell based client that improves on the basic Git functionality that is found in PowerShell.
Git for Windows
Installs an emulation of BASH complete with all the Git commands found in Linux.
The command line is not for everyone and because of this there is an array of visual clients.
VScode
Microsoft’s new lightweight text editor come lightweight IDE.
GitHub Client
The visual client provided by the website GitHub, who host remote repositories.
GitKraken
A stylish GUI that makes viewing the differences between any two commits of a project a breeze.
The next post, Visual Studio, will cover the built in tools we can use within Visual Studio.
The post Source Control for the Reluctant DBA appeared first on The Database Avenger.
