I have been writing posts related to embracing new trends like Cloud/NoSQL/Business analytics etc. Currently, I have been speaking about DevOps and containers in various events and this will be a great learning to share.
I think this is a new evolution for software development industry which everyone is embracing with open arms. The agility and speed it provides to manage the software code and deployments, has been really impressive. Being a data guy, I was figuring out a way to join this bandwagon. Finally, I got something interesting on how as a Database professional , one can be part of this. DevOps is anyways a big change but I will just talk about how a database guy can contribute.
Generally, DBAs have to do lots of code release on daily basis and move around lots of changes from dev to test, staging or pre-prod and then production server. Generally, DBAs struggle to keep track of all the scripts and it takes up 20-30% of their working hours throughout. In this series of posts, I will explain how this can be optimized and how as a DBA, the practices like Source control can be helpful and adding containers to this can make the entire process really simple and easy to manage. While writing this post, I have presumed that the reader has understanding of DevOps concept.
In this post, let’s talk about a free of cost tool , SQL Server Data Tools(SSDT) which can be downloaded from here. Let’s see how we can play around with this tool and how it can help in code release automation .
Open Visual studio and create New SQL Server Database project and click OK:
Right click on the solution name and then click import to add the database:
Click on select connection and then browse to the right instance and the database:
When the DB is connected, click on import to import all the DB schemas
Now if you see the solution explorer, you will find all the schemas and the objects related to the DB:
Let’s add this solution to the source control and in this case I connect to Visual studio team services online account:
In this case, I will connect to Database_Migration project – You first need to have your account here or you want connect to the local TFS server (Connect with your development team on this):
After connecting to the TFS server, let’s check the source code by clicking on “Add Solution to source control under source control option”:
Create the project name, in this case, it’s DBcouncil and click OK :
When the project is created, go back to visual studio project and check in the code:
Now let’s see how the code looks like on TFS. In this case we have used Visual studio team services i.e. TFS online account. let’s open the link to access the online account of team services. However, even On-premise TFS or Git and other source control tools are also supported:
when you login into this account, you will be routed to this dashboard:
Click on Database_Migration where our project was created and then click on code to “DBCouncil” project:
Here you can see all the schemas and their objects:
Now let’s make some code changes and see how the changes could be tracked. In this case, we have renamed the location column for cities table and it also shows where this change will impact e.g. if there is any reference in stored procedure or the other objects. Here it shows the reference of this column in stored procedure GetCityUpdates – if we click on apply, the references will also be updated.
Now , lets checking the changes here and see on the TFS online portal:
If you see the changeset for DBCouncil project, you will be able to see:
it saves you a hassle of checking the history of changes manually. I have seen DBAs keeping all the change scripts in either emails or a shared folder. Instead, if that can be put under source control, you can find out changes like this:
All these changes are still in the project in SSDT, these changes are yet to be deployed to the database.
In the next blog posts, I will share
1. How to automate the code release process
2. Integration of the DBs with containers on Windows and Linux
3. How containers can help to setup the dev/test/prod environments within seconds
4. How to manage these containers with Azure Container Service – Kubernetes