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
- SQL Server Database (Cloud hosted/on premise hosted)
- SQL Server Management Studio
Red-gate tools (http://www.red-gate.com/products/sql-development/sql-source-control/)
- SQL CI
- SQL Sync
- SQL Release
- SQL Compare
- SQL Source Control
- SQL Test Data Generator
- SQL CI
- 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
Install Tentacle on the database server and add the deployment target as dbserver as shown below
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
Next, include the step template Red gate deploy from package as shown below
Run the deploy. Changes will be deployed successfully
If you look at the steps it can’t be easier than make this happen.