Embrace DevOps as a Database Administrator- automate the T-SQL code releases

In continuation to the series of posts on DevOps for a DBA, lets learn how the SQL code release can be automated. In the previous post, we discussed on importing the schema of the database in SQL Server Data Tools and putting  T-SQL code under source control.

Historically, all the releases have been manually executed on the production server. Let’s see how using SSDT this can be optimized. There are two ways to do that:

1. Leverage SQL Server Schema Comparison tool –> Manual

2. Leverage SQLPackage.exe  -> Automated

1. Leverage SQL Server Schema Comparison tool –  Let’s see how this tool can be used:

1) Click on New Schema Comparison tool:

image

2) Select the source of the comparison, in this case it’s the project where the changes have been made:

image

3) Target is going to be the database where the code needs to be deployed:

image

4) Lets click on Compare and see the results:

image

If you see the above output, it clearly shows the action which is to remove old column named Sales.Invoices.BillTOCustomerID and add Sales.Invoices.OrderID1 and subsequently change the procedure where that table is being used.

4) Moreover, you can also control the behavior of the deployment as follows:

image

So far, we have identified the delta of changes to be applied and now let’s deploy the change to the actual database. One way to apply the changes is to click on the update and make the changes happen:

image

5) When you click on yes, the changes will be applied to the target database:

image

6) Let’s get into the database on SSMS and see if the change has reflected there :

image

DevOps is all about automation and the above method has lots of manual intervention. Let’s discuss about SQL Package.exe and how it can help to automate the entire code release process.

1) Let’s locate SQLPackage.exe:

image

2) Let’s check the parameters of this exe:

image

3) In our case we will use the following command to publish all the changes automatically:

“C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\sqlpackage.exe
/action:Publish
/sourceFile: Location of the DACPAC file which will contain all the changes made to the db along with the entire script of the database
/targetconnectionstring : Target instance where all the changes need to be applied or need a fresh DB with all the changes

Location of the DACPAC file can be found under:

image

4) In our case, let’s apply the changes to the target database through script which can be automated using windows or through TFS build process :

Script:

“C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\sqlpackage.exe”

/action:Publish

/sourceFile:”C:\Users\harshch\source\repos\DBcouncil\DBcounil\bin\Debug\DBcouncil.dacpac”

/targetconnectionstring:”Data Source=localhost; Initial Catalog=WideWorldImporters; Integrated Security=true;”

image

5) Let’s check the changes in the SSMS:

image

This script can be put in TFS build or automate in windows scheduler to pull all the changes periodically and apply those to the test/dev/prod servers. Moreover, to create master tables in the build process, script items could be added as follows:

image

In the next posts, we will discuss on

1: How to deploy these changes directly to container

2. How to update the image with the latest changes

3. How to deploy the containers with the latest changes

4. How to manage containers with Azure Container Service

 

HTH!

Advertisements

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