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
- Visual studio 2013 IDE
- SQL Server Data Tools (Appropriate version)
- Microsoft Azure SQL Databases
- Redgate SQL CI (http://www.red-gate.com/)
- Redgate SQL Release (http://www.red-gate.com/)
- Teamcity as Continues Integration server (https://www.jetbrains.com/teamcity/)
- Octopus Deploy as Deployment Tool (http://octopusdeploy.com/)
- 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
- Install the Teamcity plugin
- Install SQL CI from DLM automation suite
- Install SQL Release from DLM automation suite
Create a teamcity project and associate build configuration using the Redgate CI Build Teamcity running as shown below
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.
Import step templates from the community library, as shown below
Create a project, as shown below
3.1 Extract the nuget packages which is a standard process
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.