A reference implementation of a continuous integration pipeline for legacy SQL Server databases

What am I doing?

Creating a publically available version of the adventureworks 2012 oltp database but by putting it under source control, implementing unit tests and improving the design and code to fit modern development guidelines.

Why?

There are two aims, firstly to show how to take an existing database and safely get it into source control. Once it is under source control we can start to document how it works and write unit tests which will allow us to make changes to it knowing that we are not breaking backwards compatibility or losing any functionality.

This will hopefully serve as a guide on how to take an existing database that has no documentation and no tests and bring it under our control.

The second aim is to show how we can build a continuous integration and possibly continuous deployment pipeline for our database, we will point out any challenges and how they were fixed as well as giving a reference guide on how you can do this yourselves on your own databases.

Why Adventureworks?

Adventureworks, as I understand it, has been designed to show off a piece of as much different functionality as possible. I know from testing my own db tools, using Adventureworks normally finds any outstanding bugs. What it doesn’t show is any best practices so it should be in the same state that many legacy databases are in today, they are undocumented, untested and have no coding standards.

To be clear, this isn’t a compaint about Adventureworks, this is about taking a real world imperfect database and trying to make it a little bit more perfect.

What Technology?

I am going to use SSDT to get the database in source control and use visual studio online to create the repository and build definitions, however I would love to see someone else, or maybe I will, write some guides on how to do the same thing with other tools.

I have chosen to use the SQL Server 2012 version as I think that will make it more accessible to people than SQL Server 2014 or even 2016!

What should the readers do?

Again following the aims, there are two things:

Firstly I am hoping that this can be a community project so if you have some time to improve some aspect of it, from changing the schema, to writing tests or documentation that would be amazing.

Secondly if you haven’t got your databases under source control or haven’t built a continuous integration pipeline then consider following along with your own databases, where I use a public visual studio online repository you can of course use your own internal or private source control.

Steps

As I publish each post I will link to them here:

Comments:

Anonymous

June 26, 2015 - 04:09

How about using git and jenkins?

Just seems to me that using Microsoft only tools is so 1990s and git is by far and away the market leader in source control. I really don’t know the CI marketplace but the last 10 years I’ve seen nothing but Hudson and now Jenkins. BTW, I don’t profess to know either of these technologies but would be far more inclined to learn them if (you) provided guidance as they seem more appropriate than the MS stack.

Anonymous

June 26, 2015 - 04:15

Hi, me again. Just saw on SQL

Hi, me again. Just saw on SQL ServerCentral you are in fact using git. Great. Hopefully jenkins in the future?!

Ed Elliott

June 26, 2015 - 17:13

Hey,

Hey,

Honestly it really doesn’t matter what source control or build server you use (except please don’t use visual source safe).

You can setup source control for SQL Server using TFS, GIT, SVN, Mercurial, anything.

You can run your CI process using any of the build servers TFS, Hudson / Jenkins, TeamCity, Bamboo, anything.

Just use whatever you already have setup or what you have experience with!

Ed