Embrace DevOps as a Database Administrator – Build container images with latest code release

In the previous posts of this series, automating code release and putting T-SQL code under source control was discussed. In this post, we will discuss about how the latest code release can be put on the containers directly and leverage the same image to build Dev/Test/Prod environments.

The flow of this post will be :

1. Spin a SQL container on Linux

2. Restore a database on the container

3. Run SQLPackage.exe to automate the code release to container

4. Build an image with all the changes

5. Spin containers with the latest changes for dev/test/prod

Note : – I presume, you have an understanding about containers. If not, please check this post before reading further.

In the last post, we discussed about SQLPackage.exe to automate the code release.

image

and the input parameters for this exe were as follows:

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;”  << target connection string will point to the container

1) Let’s look at the container environment, we have got here.  Container was spun using the following commands and we have a container instance running with a DB:

docker run -e “ACCEPT_EULA=Y” -e “MSSQL_SA_PASSWORD=test123@” -e “MSSQL_PID=Developer” –cap-add SYS_PTRACE -p 1401:1433 microsoft/mssql-server-linux

image

2) Let’s copy the backup file to the container:

image

3) After restoring the database to the container instance , let’s run the schema comparison tool to identify the delta of changes:

image

4) Same set of changes are identified:

image

5) Let’s use SQLpackage.exe to deploy the change to container:

“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,1404; Initial Catalog=WideWorldImporters; Integrated Security=false; user id=sa ;password=test123@”   << target connection string pointing to the container>>

image

6) Let’s connect to SQL instance built on container and see if the changes have been deployed:

image

7) Now let’s commit these changes to the container image so that, next container we spin has all these changes inbuilt. Again, this entire lifecycle can be automated through powershell or windows scripts. Moreover, this script can be part of build process in TFS for automating the application release.

Syntax to commit the changes to image is  – docker commit < containerID > < newcustomimagename >  (mentioned in step 2 in the below screenshot)

image

if you see the size of image named SQL_2017_release1 is higher than the other images because the database has been restored in this image.

9) Now, let’s spin up a container from this image and see if we have the database already created:

c:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin>docker run -e “ACCEPT_EULA=Y” -e “MSSQL_SA_PASSWORD=test123@” -e “MSSQL_PID=Developer” –cap-add SYS_PTRACE -p 1405:1433 -v  -d sql_2017_release1

image

10)  Let’s connect to the new container SQL instance and see if we have the database created with all the changes:

image

We can see all the deployed changes were successfully ported to the new container. This new image could be used to build dev/test/Prod environments. In the next post, we will discuss about how to orchestrate containers using Kubernetes on Azure.

HTH!

Advertisements

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!