By: Carlos Robles | Updated: 2019-12-09 | Comments | Related: 1 | 2 | 3 | > Azure
Problem
Developers often need new SQL Server instances with production data to start or continue working on their projects. Having shared SQL Server database environments often leads to resource contention and confusion. Deploying a new SQL Server instance on a VM can also be quite challenging, it is difficult to get multiple operational teams clear on what the developer requirements are. 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.
Solution
In the first part of this short article series, we learned how to create an Azure resource group to host a SQL Server container using the Azure Container Instances services.
In this second part, I will show you the next step on how to manage the SQL Server container hosted in Azure Container instances.
Pre-requisites
There are few pre-requisites we need in order to start using ACI:
- A Microsoft Azure account
- A laptop or personal computer (with internet access)
- Have Azure CLI installed
- Have a SQL Server container created in ACI (Check the previous tip about this topic)
Managing a container life cycle on Azure Container instances (ACI)
Containers are ephemeral, they are created one day and the next day paused / stopped or even destroyed to be rebuild from a recent version of the image that includes minimal changes in the configuration.
Here is a graphical representation of the container life cycle:
Azure container instances really shine when it comes to a simple platform to host containers, you can focus on designing and building your applications instead of managing the infrastructure that runs them.
Regardless of how the infrastructure is managed, we really need to learn about how to manage our containers through this life cycle. Conveniently for us, we have plenty options to do it:
- Azure client
- Azure portal
- Azure cloud shell
- PowerShell
In my humble opinion the first two options (Azure client & Azure portal) are the best, because the PowerShell cmdlets are a little bit limited at the time of writing this article. The Azure cloud shell experience is basically the same as running Azure CLI on any platform (Windows, Linux or macOS).
Let’s take a look at the most common administrative tasks we should perform when hosting a SQL Server container in Azure container instances.
The Azure client (AZCLI) approach
The following examples will show you how to manage a SQL Server container running in ACI using Azure CLI commands.
List resources in Azure Resource Group
First things first, we need to list all the resources hosted in our resource group just to make sure we have the correct SQL Server container name.
I will be re-using the resource group I created in the first part of this tip, so I know for sure my Azure resource group is called "MSSQLTips" so I will use the "az resource list" command to list all resources in this resource group.
Run the command as follows:
[dba mastery] $ az resource list -g MSSQLTips
Results
[
{
"id": "/subscriptions/a3729944-2d39-4be1-8251-0529dd60c431/resourceGroups/MSSQLTips/providers/Microsoft.ContainerInstance/containerGroups/serverless-sql-01",
"identity": null,
"kind": null,
"location": "westus",
"managedBy": null,
"name": "serverless-sql-01",
"plan": null,
"properties": null,
"resourceGroup": "MSSQLTips",
"sku": null,
"tags": {},
"type": "Microsoft.ContainerInstance/containerGroups"
}
]
Nice! We are absolutely sure that our container called "serverless-sql-01" is part of the "MSSQLTips" resource group in the "WestUS" region of Azure.
Checking Azure Container Instance (ACI) metadata
Using the information returned by the previous command, we will be able to learn more details about our container. We can "query" the container metadata through the "az container show" command.
This command requires a few parameters of course, such as the resource group and resource name. At this point, we are very familiar with both, so it is just a matter of running the command as follows:
[dba mastery] $ az container show --resource-group MSSQLTips --name serverless-sql-01
You can expect to see a very large output returned in JSON format. For presentation purposes, I will list just a summary of the things you will find in the output:
- OS type
- Provisioning state
- Environment variables
- Image name
- Events
- Resources
- CPU
- Memory
- Network
- IP Address
- Ports
- FQDN (Fully qualified domain name)
Checking the status of an Azure Container Instance (ACI)
As mentioned before the "az container show" command returns a very large result set of data. In some of the cases this is acceptable, however there are scenarios where we are interested to find just the container status for example. Azure CLI supports filtering through the "--query" argument which turns to be a really nice option for us.
This argument allows us to filter the results using JSON query language, so we can retrieve specific information from the output. Let’s try to get the actual status of our container, running the following command:
[dba mastery]$ az container show --resource-group MSSQLTips --name serverless-sql-01 --query "{Status:instanceView.state}" --out table
Results
Status -------- Running
Note - I’m specifically filtering the result by the JSON object instanceView.state. The status of my container is "running" as I expected. Please note I added an additional argument "--o table" just to convert the output from JSON to tabular format.
Getting the public IP and FQDN of Azure Container Instance (ACI)
Using the same method as before, now let’s run a query to find out the public IP address and the FDQN (fully qualified domain name) of our container.
Run the command as follows:
[dba mastery]$ az container show --resource-group MSSQLTips --name serverless-sql-01 --query "{IP_Adress:ipAddress.ip,FQDN:ipAddress.fqdn}" --out table
Results
IP_Adress FQDN ------------- ------------------------------------------ 10.80.140.210 serverless-sql-01.westus.azurecontainer.io
Nice!! That was easy right? This information is very important, because at some point we want to connect to this container from SQL Server Management Studio or Azure Data Studio as well.
Stopping an Azure Container Instance (ACI)
Let’s switch gears to pure container administration now, one of the great benefits of creating a container in Azure container instances is financial savings. Because SQL Server is hosted in a container running somewhere in an Azure data center, there will be no OS layer or any other client application consuming resources out of the host machine.
Another great benefit is the start / stop time, just imagine having a SQL Server instance ready to go in matter of seconds.
Let’s put this theory to test by stopping our container. Here is the Azure client command:
[dba mastery]$ az container stop -n serverless-sql-01 -g MSSQLTips
It does not return any kind of output, but we already know how to check the status so let’s find out:
[dba mastery]$ az container show --resource-group MSSQLTips --name serverless-sql-01 --query "{Status:instanceView.state}" --out table
Results
Status -------- Stopped
Nice!! It was just a matter of a single line of code to take down the SQL Server instance running on this container. You can even automate this process to make sure the container in question is still down during the period of time you are not using, so no charges for compute or networking will be added to your Azure billing.
Starting an Azure Container Instance (ACI)
Starting a SQL Server container in Azure container instances is no different than stopping it, it will take longer of course because SQL Server has to initiate, but I assure you it is not that much as booting up a VM on any infrastructure.
Let’s take a look at the start command:
[dba mastery] $ az container start -n serverless-sql-01 -g MSSQLTips
Results
- Running ..
You will probably spend a few seconds starting at the "Running" message blinking in your screen, don’t worry it will finish quickly in matter of 5-10 seconds. Once again there will be no output, however you already know how to check the container status at this point.
Deleting an Azure Container Instance (ACI)
At this point we know how to create, start and stop a container using Azure CLI. Let’s complete this container life cycle by just removing it from our resource group or in other words deleting it from our container group hosted on Azure Container instances.
Before we move forward, there is an important note that I will like to address. The "az container delete" command will wait for your input in order to delete the container. There is no immediate action unless you confirm, which makes a lot of sense for obvious reasons Microsoft doesn’t want users to delete containers by mistake.
Let’s take a look at how it works:
[dba mastery] $ az container delete -n serverless-sql-01 -g MSSQLTips
Results
Are you sure you want to perform this operation? (y/n): y
You will get some output this time, it is pretty much what we got when the same container was created –just metadata.
That’s it, we have completed a SQL Server container life cycle hosted on Azure Container Instances.
Join me in the next part of this tips series where I will explain how to connect to our SQL Server container using SQL Server Management Studio or Azure Data Studio.
Next Steps
- Learn more about Azure Container Instances
- Learn how to deploy SQL Server in ACI with persistent volumes
- Learn more about SQL Server on Docker
- Learn more about SQL Server 2019 containers
- Learn how to use Azure Kubernetes Services to deploy SQL Server services
About the author
Carlos Robles is Data Platform MVP and multi-platform DBA with +10 years of experience in database technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-12-09
