Embrace DevOps as a Database Administrator

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:

clip_image001

 

Right click on the solution name and then click import to add the database:

image

 

Click on select connection and then browse to the right instance and the database:

image

 

When the DB is connected, click on import to import all the DB schemas

image

 

Now if you see the solution explorer, you will find all the schemas and the objects related to the DB:

image

 

Let’s add this solution to the source control and in this case I connect to Visual studio team services online account:

image

 

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):

image

 

After connecting to the TFS server, let’s check the source code by clicking on “Add Solution to source control under source control option”:

image

 

Create the project name, in this case, it’s DBcouncil and click OK :

 

image

 

When the project is created, go back to visual studio project and check in the code:

image

 

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:

 

image

 

when you login into this account, you will be routed to this dashboard:

image

 

Click on Database_Migration where our project was created and then click on code to “DBCouncil” project:

 

image

 

Here you can see all the schemas and their objects:

 

image

 

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.

 

image

 

Now , lets checking the changes here and see on the TFS online portal:

 

image

 

If you see the changeset for DBCouncil project, you will be able to see:

 

image

 

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:

image

 

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

 

HTH!

Advertisements

One thought on “Embrace DevOps as a Database Administrator

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s