Create Azure Container Instances for SQL Server

Problem

Developers often need new SQL Server instances with production data to start or continue working on their projects. Having shared database environments leads to resource contention and confusion sometimes. Deploying a new SQL Server instance on a VM can also be quite challenging and it is difficult to get multiple operation teams clear on what the development requirements are.

Solution

Docker is an excellent tool that solves this problem, developers can create their own environments and start working on them immediately. In this tip, I will show you how to create SQL Server serverless containers using Azure Container Instances.

As mentioned at the beginning of this tip, working in a SQL Server shared database environment sometimes is difficult. Just imagine having multiple people working on the same database at the same time running all kind of queries, changing stored procedures code or event worse dropping database objects.

Having isolated development environments through containers is a better way to use company resources. Containers are lightweight, there is no SQL Server installation required, it is just a matter of having the container engine and the desired image to execute.

Now imagine for a moment having serverless containers running on Azure. Azure Container Instances (ACI) allows you to deploy containers not having to worry about managing the infrastructure that runs them. They are deployed somewhere in the Azure data center specifically in the region chosen for the resource group.

ACI is a great solution for shops looking to enable their teams to build and deliver agile environments without having to invest much in the infrastructure.

Pre-requisites

There are few pre-requisites we need in order to start using ACI:

I will assume you already completed all these steps, installing and configuring Azure CLI is out of the scope of this tip.

Getting started with ACI

ACI provides a rich set of Azure CLI commands we can use to manage our containers, let’s take a look in detail to all the commands I will be using to manage my container in ACI:

Command Description
az container create Creates the container in the resource group
az container delete Deletes the container from the resource group
az container list List all containers, the resource group and subscription can be also specified
az container start Starts one specific or all containers in the resource group
az container stop Stops one specific or all containers in the resource group
az container show Returns the container properties in JSON format
az container logs List the complete log of a specific container in the resource group

Now we have the basic understanding of ACI and the list of commands, let’s start creating serverless SQL Server containers.

Creating a Resource Group for Azure Container Instances (ACI)

The first thing I’m going to do is create a new resource group in the “WestUS” region, I will call this resource group “MSSQLTips”:

[dba mastery] $ az group create -l westus -n MSSQLTips

Below is the data returned after creation.

{
  "id": "/subscriptions/a3729944-2d39-4be1-8251-0529dd60c431/resourceGroups/MSSQLTips",
  "location": "westus",
  "managedBy": null,
  "name": "MSSQLTips",
  "properties": {
    "provisioningState": "Succeeded"
  },
  "tags": null,
  "type": null
}

As you can see, the output above confirms my new resource group called “MSSQLTips” was successfully created in the “WestUS” region. The next obvious step is to create the container and assign it to my “MSSQLTips” resource group, but first I would like to go through the parameters required to create a container in ACI.

Creating a Container in Azure Container Instances (ACI)

The “az create container” command differs a little bit from the traditional “docker run” we are familiar to use when working with containers. Don’t get me wrong, the concept is the same, but there are few differences I would like to point out before moving forward.

Let’s take a look at the parameters we will use to create the SQL Server container.

environment-variables

This is different then using the Docker CLI, this parameter receives a collection of environment variables passed in the form of an array.  All the key and values must be passed in a single line, something like this:

–environment-variables EnVar1=Value1 EnVar2=Value3 EnVar3=Value3

dns-name-label

The dns-name-label parameter is optional but recommended, it will create and assign a public IP and DNS name so we can connect to this resource externally from the internet.

cpu

This parameter defines the amount of CPU cores assigned to the container. It is important to note the default value is 1, if we proceed with the default our container create command will fail. According to SQL Docs, we need at least 2 cores as minimum system requirements to run SQL Server on Linux.

memory

This parameter defines the required memory for the container. According to SQL Docs, we need at least 2 gigabytes.

port

This parameter defines the port to open for our TCP\IP connection from the remote machine. For more information about the SQL Server on Linux system requirements, please check the information from SQL Docs here.

It’s time to put all the parts together and create the container in ACI, here is how the “az container create” command will look:

[dba mastery] $  az container create --resource-group MSSQLTips \gt; --name serverless-sql-01 > --image mcr.microsoft.com/mssql/server:2017-CU16-ubuntu > --environment-variables ACCEPT_EULA=Y MSSQL_SA_PASSWORD=SqLr0ck$ > --dns-name-label serverless-sql-01 > --cpu 2 > --memory 2 > --port 1433
 - Running ..

After issuing the above and waiting a few minutes, a JSON output that contains all the details and properties of this container is returned. It will be a little bit hard to go through all the information, therefore I decided to truncate the output to show you the most important details.

  "location": "westus",
  "name": "serverless-sql-01",
  "networkProfile": null,
  "osType": "Linux",
  "provisioningState": "Succeeded",
  "resourceGroup": "MSSQLTips",
  "restartPolicy": "Always",
  "tags": {},
  "type": "Microsoft.ContainerInstance/containerGroups",
  "volumes": null

As you can see, the provision state confirms my container called “serverless-sql-01” was successfully created for the resource group called “MSSQLTips” located in the Azure “WestUS” region.

Join me in the next part of this tips series where I will explain how to connect, monitor and interact with SQL Server instances created as serverless containers in ACI.

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *