SQL Server Continuous Deployment "In a Box"

What is this?

Well if you read the name aloud “SQL Server Continuous Deployment in a box” then, if I have done my work correctly choosing the title for the blog, give a hint :)

what is the big idea?

There is really some great tooling for SQL Server - second to none really when it comes to RDBMS’s and setting up Continuous Deployment pipelines is actually pretty simple once you know which parts to plug together. I even did it in 55 minutes once (https://www.youtube.com/watch?v=9YJQTx3bPek).

What I wanted to do was to allow people to spin up a vm, install ssdt (or visual studio with ssdt), install a local dev instance of SQL Server (or use localdb), run a script and add some parameters and have everything they need to be able to make changes to some code and have that code automatically deployed to a production database.

Now a little word about that word “production”, you could set this up to point to your production database but what I would suggest for the demo is that you use a copy of your production database or something which you will call “production” - the tools here can all be used in a real life setup but you wouldn’t normally host everything on your development machine.

How does it work?

The idea is that anyone who can download from the internet can do this, so setup the pre-requisites (ssdt and sql) and then either clone the repo (https://github.com/GoEddie/SQLServer-Continuous-Deployment-In-A-Box/) or download the latest zip from:

https://github.com/GoEddie/SQLServer-Continuous-Deployment-In-A-Box/arch…

Note to get the zip, you don’t need to use git or sign up for an account or anything (other than clicking the link)

Once you get that then extract the folder, open powershell as an administrator, change to the src folder and run these two little commands:

Unblock-File *.ps1
.\ContinuousDeploymentFTW.ps1

What does this do?

Unblock-File *.ps1 - removes a flag that windows puts on files to stop them being run if they have been downloaded over the internet.
.\ContinuousDeploymentFTW.ps1 - runs the install script which actually:

  • Downloads chocolatey
  • Installs git
  • Installs Jenkins 2
  • Guides you how to configure Jenkins
  • Creates a local git repo
  • Creates a SSDT project which is configured with a test project and ssdt and all the references that normally cause people problems
  • Creates a local Jenkins build which monitors your local git repo for changes
  • When code is checked into the repo, the Jenkins job jumps into action and…

If you check into the default branch “master” then Jenkins:

  • Builds the SSDT project
  • Deploys the project to the unit test database
  • Runs the tSQLt unit tests
  • Generates a deployment script for the “production” database

and what you have there is continuous delivery in a box, now I know that isn’t what you were sold by the title but I wanted to show a couple of different approaches to this so if you use git to create a release branch and check-in on it by changing to the directory with the SSDT project in powershell and doing:

git checkout -b release

Make a change and then…

git add .

git commit -m “a change that will go straight to production \o/”

You will see that a “Release” jenkins build is created automatically because the job we set up initially is a “Jenkins Multi-branch pipeline” - don’t worry about that but what you see is Jenins:

  • Builds the SSDT project
  • Deploys the project to the unit test database
  • Runs the tSQLt unit tests
  • Deploys the SSDT project to the “production” database

Nice hey?

Why the choice of technology?

SSDT - this doesn’t need ssdtm you could do this with readyroll, dbup etc anything
Git - aren’t most people moving to git nowadays?
Jenkins 2 - for the multi-branch pipelines which means it automatically creates builds from the Jenkinsfile which is checked into source control

Sounds hard to setup?

It isn’t all you need to do is configure Jenkins, create a user and give my script the username and token and also the connection details to the unit test and production databases. If you like when you get the SSDT project you can import from your production database which will then be deployed to your unit test database, or you can leave it empty, or add a couple of objects - whatever suits you!

Prerequisites

I would create a VM, install SSDT or Visual Studio with ssdt in (2015 or 2017), install a local SQL Server 2008+ and restore a copy of your production database that should be it.

I made a video to show the awesoness of all of this:

https://the.agilesql.club/assets/videos/SQLCDINABOX.mp4

I made the video to see how much fun it was to make videos, it was very fun but this will be the only one ;)

Enjoy and good luck!