TSQL-Tuesday #108: A Journey Into CI/CD


This month’s TSQL Tuesday is hosted by Malathi Mahadevan (w | t) and she asks us to write about one thing you want to learn that is not SQL Server.

As a database administrator in SQL Server you might think it’s easy to think we only deal with that technology. But the role of the database administrator has been changing for a while now and we need to know more about other technologies besides SQL Server.

In my daily job, I’m not just a database administrator, but I’m also a database developer. As a developer, I had to make the shift from developing databases in SSMS to Visual Studio, which I still have to get used to but I’m getting there.

The reason I kind of had to do that was that it was time. Our IT team was going to switch from SVN to Git. We needed more collaboration, standards, and processes to deploy new releases. For me, that was a BIG learning curve.

As a DBA I never had to deal with source control for our databases. At previous jobs, I never did a lot of development, mostly maintenance, and the responsibility for the databases was mostly with the third parties and development teams.

So now our projects are developed using the right tools, and the source code is saved in a way that others can collaborate, but there is still the process of deployment that’s done manually.

Why use CI/CD?

CI/CD, or Continuous Integration/Continuous Development, is the cornerstone of DevOps.

We’ve heard about DevOps for a while now and it never stuck with me until now. If you know me,

I’m a real advocate of automation. I like to automate about everything repetitive to make sure things run as smoothly as they can without the obvious PEBCAK (Problem Exists Between Chair And Keyboard) situations.

Handling all the releases on my own makes the development process prone to mistakes and delays. I want to have a process where I can wear a single hat, either the developer or the DBA.

I do not want to be responsible for both the development and pushing the releases through the pipeline. CI/CD can help me with that but saying some fancy buzzword is not going to help me.

How to learn CI/CD?

First of all, this was another big learning curve for me. For me to go from database development in SSMS to a CI/CD pipeline was something like climbing Mount Everest.

But as cliche as this might sound, just start with a step and another and in the end, you’ll get there. The first thing I had to do was learn what CI/CD was. What does it entail?

How does it integrate with my tools? What tools do I need to implement to get it to work?

I found that the best way to learn CI/CD is to just start and learn from my mistakes. The following steps made me familiar with CI/CD

  1. Read a lot
  2. Migrate database development to Visual Studio
  3. Build the architecture

Step 1: Read a lot

The first thing I did was spend a lot of time reading articles on the internet about what CI/CD is.

If you google for that you get enough content to learn what it is. I always want to know the ins and outs of certain technology and I want to know enough to get me started.

I find that diving into new technologies lets me make mistakes which makes me learn at a faster pace and will make it easier in the end.

Step 2: Migrate database development to Visual Studio

I already started developing tSQLt unit tests for all my objects and I separated the data model and the data from the tests into separate projects within the solution.

This article helped me set up the solution to make sure everything was nicely separated and organized.

Step 3: Build the architecture

After investigating what I needed I decided to install Jenkins for the build server. Jenkins is free and it’s easy to install.

I set up a virtual machine to make sure I was able to create snapshots with every step to be able to revert if something went wrong. And I can assure you that I made a lot of mistakes.

This article helped me understand how to set up projects in Jenkins for SSDT.

I made some other changes to the project in Jenkins to be able to push the results to a server in conjunction with dbatools and a remote Git server.

That’s going to b another article that will be released soon.

What’s the next step?

The next step is to implement a service that will be responsible for the delivery of the artifacts to different destinations like a test, acceptance, and finally production server.

I’m still trying out a couple of solutions but I think I’ll probably go with Octopus Deploy.

After that, it’s time to put all the knowledge into production. Getting the projects loaded and getting the process implemented within the company.

The latter is going to be the most difficult one because it’s a huge change but I’m confident that in the end, it will make things go smoother.

DevOps is not going to go away and CI/CD will make your life easier in the long run.