Getting Started with SQL Server Azure Container Instances ACI

By:   |   Updated: 2019-09-26   |   Comments (4)   |   Related: More > Azure

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


Last Updated: 2019-09-26


get scripts

next tip button



About the author
MSSQLTips author Carlos Robles Carlos Robles is Data Platform MVP and multi-platform DBA with +10 years of experience in database technologies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, October 01, 2019 - 7:41:10 PM - Carlos Robles Back To Top

Irene,

Yes, that's correct Azure Container Service is deprecated.

This article is about Azure Container Instances, this service went GA in April 2018 having its last update on May 2019. This last update is really interesting allowing users to create AKS serverless nodes, which is a huge deal in terms of pricing and scalability.

More information:

https://azure.microsoft.com/en-us/blog/azure-container-instances-now-generally-available/

https://azure.microsoft.com/en-us/updates/?product=container-instances

Thanks!


Tuesday, October 01, 2019 - 7:23:53 PM - Carlos Robles Back To Top

Hi Linda,

Thanks for your comment! As mentioned at the beginning of this article creating a "serverless" container in Azure Container Instances does not differ much from the traditional "docker run" command. That means the answer to your question about the "--image" parameter, is that simply works the same way as any other container running on Docker. It is up to you to choose the image, in my example I used the latest one available for SQL Server 2017 running on Ubuntu.

I think the title of this article is also self-explanatory, this is not an introduction to ACI. It is indeed a way to get you started with this technology to create SQL Server serverless containers.

About the the "az container create" question, I did include some information at the beginning:

Command Description
az container create  Creates the container in the resource group

I understand you are looking for the specific information of each parameter, and yes that's missing on Microsoft docs. I personally think the reason is because this technology assumes you are already familiar with containers, which is my case. If you look for more information about how to run a docker container I have a previous article published about how to create a SQL Server container in RHEL:

https://www.mssqltips.com/sqlservertip/5785/run-and-manage-sql-server-2019-ctp-20-rhel-docker-container/

I do not recommend to use the Windows image at least for now, it has not been updated in the last two years. In contrary, the Linux based images are updated whenever a new CU is available.

If you want to learn more about the official SQL Server images published by Microsoft, check here:

https://hub.docker.com/r/microsoft/mssql-server-windows-developer/

https://hub.docker.com/_/microsoft-mssql-server

If you want to follow up about this topic, or anything related to SQL Server on containers please feel free to reach me out by email: [email protected] I will be more than happy to help!

Thanks!


Thursday, September 26, 2019 - 8:22:40 PM - Irene Back To Top

Is Azure Container deprecated? https://docs.microsoft.com/en-us/azure/container-service/kubernetes/container-service-kubernetes-walkthrough


Thursday, September 26, 2019 - 12:34:01 PM - Linda LF Back To Top

Thanks for this tip, but I was looking for more details on the SQL-portion.  Specifically the parameter [ --image mcr.microsoft.com/mssql/server:2017-CU16-ubuntu ] could use further explaining.  What if I wanted to build a container for a SQL Server Azure Container Instance on Windows?  I believe that there is a greater demand for SQL on Windows than Windows on ubuntu, but I could be wrong.

In fact, there were several parameters on that [az create container] command that were not included in your article.  It would be really great if the article explained all of them, but perhaps as this was a 'getting started with...' article they were not intended to be included.  While this got my interest, I don't feel like I have enough information to replicate your results in my environment.

Unfortunately the documentation for the command [az container create] found at [https://docs.microsoft.com/en-us/cli/azure/container?view=azure-cli-latest#az-container-create] left a lot to be desired as well.  It isn't your fault that the detail provided for the [--image] command only stated "the container image name", instead of where/how to get one and what it is.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools