I have been recently working with various client around enabling and adopting devops practices within enterprise organisations. It’s interesting to see many people think it’s just code development team and operations have to work together. In reality there is a team who develop/maintain databases which needs to be highlighted to be part of these practices which in my mind is considered as key enabler. To enable these I think we need to have the right tool sets and provide education for the database developers and Database administrators. Earlier I have written article about how you can source control the database and enable continuous deployment using redgate tools. This article is all about how you can do with different tool set combination like if you are already using SSDT and redgate tools works as treat to implement continuous delivery for databases.

Following are the pre-requisites to follow this article

1. Visual studio 2013 IDE
   
2. SQL Server Data Tools (Appropriate version)
   
3. Microsoft Azure SQL Databases
   
4. Redgate SQL CI (http://www.red-gate.com/)
   
5. Redgate SQL Release (http://www.red-gate.com/)
   
6. Teamcity as Continues Integration server (https://www.jetbrains.com/teamcity/)
   
7. Octopus Deploy as Deployment Tool (http://octopusdeploy.com/)
   
8. GitHub account
   

Let’s get started by creating the simple SSDT project using visual studio

Click on File->New Project

Now, since we are using the Azure SQL Database we need to make sure the target platform is set correctly as shown below

Since we already have a sample database running, I’m going to import as shown below by right click on the project

Add the new connection and make sure its working by clicking the test button. Refer below screenshot

Now, in the below Import screen click start

Now you will see the progress screen

Once the import is completed you can see the schema info in as highlighted below

Now, let take about how we are going to build this project in Teamcity

1. Install the Teamcity plugin
   
2. Install SQL CI from DLM automation suite
   
3. Install SQL Release from DLM automation suite
   

1. Create a teamcity project and associate build configuration using the Redgate CI Build Teamcity running as shown below
   
   
2. Complete the build step by providing the required values as shown below
   
   Now with few simple steps we have enabled continuous integration for databases and producing the nuget packages
   

Next step is to Deploy the database packages to various environments, this will be achieved by using the Octopus Deploy.

1. Import step templates from the community library, as shown below
   

   
   

2. Create a project, as shown below
   
   

 

3.2 Redgate – Create database release will produce the octopus artifacts for Database engineers to review

3.3 Authorization required step will pause the deployment for approval from appropriate database engineers.

3.4 Redgate – Deploy from create release will actually deploy the changes.

[Note : I will talk about DLM Dashboard in Next Blog coming blogpost]

Recap of what we have done so far

• We had a database and imported them into a SSDT project
  
• With Help of redgate tools setup Teamcity to generate the packages
• With Help of redgate tools setup Octopus Deploy to Enable validation of changes by approved team and deploy those changes.

Let’s walk through the entire process of making changes in the SSDT project and it gets build as artifacts then deploy them to the various environments.

First thing is first, Take a look at the current environment status

Now, we are going to make changes to the schema by adding the third table to the database. In this scenario Di_PlayDatabase is development and so on
In Visual Studio , open select the SSDT project and add a new Item then select table

Added the new table as below

Before we checkin the changes we can do a compare with the target database on target environment of your choice with appropriate access available to access the database as shown below

We have to check-in our changes to github. And move over to the Teamcity to see if the changes have been detected as pending changes as shown below (how cool of having a CI server)

Now, Team city Kicks of new build and generates the artifacts

Based on the finish build triggers, Teamcity automatically runs the deployment of this version into the test environment. For test environment we do not have authorization step but still you can go and take a look at the update scripts and changes.html as shown below

You can see in below screenshot then new schema change have been applied

Now, by click on the above button promote to UAT, now as per the defined rule approved person will have to go and review the changes

You can download the Highlighted file and review the changes

Also, download the Changes.html to view what’s changed and what is identical then so on.. How can is the below screen in bringing information right in front of you

After review, update

the comments as shown below and click proceed to continue the deployment

Promote the changes to production

This is again, same steps we did in UAT to make sure we have a repeatable process and the steps into production environments.
In Octopus Deploy Dashboard you can see all in the same version.

In my next coming blogpost, we will show you how DLM Dashboard will help to bring the transparency and highlight the database drift in various environments.

Happy Database Deployment by Code and Push button.

No more tons of documentation, emails, developer calls etc Enjoy

If you have any question please contact.