Part 5: Continuous Delivery of SQL Server database by Source controlling in Git (Using Red-gate Tools, TeamCity and OctopusDeploy)

Most of the time in many organisation people are involved in shipping the code faster into various environments like Test, UAT, Staging and Production. After a while many hit the point of bottle neck in deploying the database changes where you cannot delivery continuously. I have been looking for a good product which can enable this process simpler. Looks like red-gate tools makes things easier and simpler by integrating with my tool sets like Teamcity and Octopus Deploy. In this article we are going to walk through creating a simple database, source controlling them in GitHub, teamcity to package and octopus deploy for deploying into various environments. In my view if you want to delivery your database changes quickly, DBA need to be involved in the CI process to start with by having Database in source control.

Tools / Setup required

  1. SQL Server Database (Cloud hosted/on premise hosted)
  2. SQL Server Management Studio
    1. SQL CI
    2. SQL Sync
    3. SQL Release
    4. SQL Compare
    5. SQL Source Control
    6. SQL Test Data Generator
  3. GitHub account / repo

Once the red-gate sql source control is installed, you open the management studio then you can see the options we are going to use.

Now I’m connected to the database, which has got few tables in it. As shown below

Now the next step is to link to the preferred source control system. In our case we are using GitHub

Once you have linked the source into your git account. Note [Make sure the bin path of git install directory is available in the environment variable]

Select the highlighted object and commit the changes. As shown below

Now if you look in the Git repo you will be able to see all the generated files. As shown below

Now I’m populating with some sample date using the Test data generator as shown below

I have created few tables now by running following commands

 

CREATE TABLE t1 ( c1 int, INDEX ix_1 NONCLUSTERED (c1))
CREATE TABLE t2( c1 int INDEX ix_1 NONCLUSTERED (c1))
CREATE TABLE t3( c1 int, c2 int INDEX ix_1 NONCLUSTERED)
CREATE TABLE t4( c1 int, c2 int, INDEX ix_1 NONCLUSTERED (c1,c2))

Now, lets check the sql source control to find out if red-gate have shown the changes


Now let’s create the nuget packages using teamcity of the sourced database using SQL CI Build.

Install the red-gate plugin which is usually found under the folder (C:\Program Files (x86)\Red Gate\DLM Automation Suite 1\TC plugin) where your DLM automation suite is installed.

Next lets add the build step with following details. Required software and tools on the agent please refer red-gate website.


When you run the build you should be able see the nuget packages being generated.

Nuget package contents

Now we have produced the package,, now there are two ways we can deploy the changes. First one we are going to use the teamcity and second option we are going to use the OctopusDeploy.

Create new build configuration with runner type as Red Gate SQL CI Sync

When you run the build configuration, you can see the below changes have been applied

And you can see the tables in the below server

Now if the commit the pending schema changes (as shown below) into source control it should do a build and deploy into the database

Now can see the pending changes in the Teamcity as shown below

Once the build is successful
Schema changes have been applied to the target database.

Now, let’s have a look at the Octopus Deploy to Push the database changes

  1. Install Tentacle on the database server and add the deployment target as dbserver as shown below

  2. Create the database project and add the process step to extract the database package created by SQL CI from teamcity, I’m using the same feeds

  3. Next, include the step template Red gate deploy from package as shown below

  4. Run the deploy. Changes will be deployed successfully

If you look at the steps it can’t be easier than make this happen. 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.