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 sometimes, it is difficult to get multiple operation teams clear of 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 connect and monitor 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.
Then, in the second tip we learned how to check our SQL Server container metadata and how to manage it through the container life cycle:
- Creation
- Stop
- Start
- Delete
In this third and final part, I will show how to deal with two very important aspects of Azure Container instances:
- Connectivity
- Monitoring
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 out the first tip – Create Azure Container Instances for SQL Server)
- Have all the SQL Server container details as FQDN and IP address (Check out the second tip – Start, Stop, Delete and Monitor Azure Container Instances for SQL Server)
- Have SQLCMD utility installed
- Have Azure Data Studio installed
- Have SQL Server Management Studio installed
Connecting and monitoring SQL Server containers on Azure Container instances (ACI)
Connectivity and monitoring are two very important aspects of any infrastructure. Regardless how easy Azure Container instances enables us to create containers, we always need to keep track of how things are going with our deployments – containers are not the exception.
Let’s learn how to connect and monitor SQL Server container using different tools like SQLCMD, SSMS, Azure Data Studio and the Azure portal.
Connecting within a running container
Before I move forward connecting to the SQL Server container through a database client tool, I would like to show you how to connect within the actual (Linux) container.
The az container exec command allows us to execute arbitrary commands within a live running container. If you’re familiar with Docker this is exactly the same as “docker exec“, great for debugging and performing operating system tasks.
As we learned in the previous tip, all the ACI commands in AZCLI requires to include the resource group name and container name parameters, however the “az container exec” requires an additional parameter called “--exec-command“.
The instruction you want to use for Linux container is “/bin/bash“, this will bring us into a live interactive bash session with my running container.
Here is how the command should look:
[dba mastery] $ az container exec -g MSSQLTips --name serverless-sql-01 > --container-name serverless-sql-01 --exec-command "/bin/bash"
root@wk-caas-9b1e4822868d476c95692b4c77d96089-f8e6299aba3e0744939c40:/#
Please note a couple of things, I have included an additional parameter called “--name“. This is not the container name as we have been using so far, it happens to be a “container group“. Yes, Azure Container Instances also allows us to create a group of containers within a resource group. For example, we can create two different container groups within a single resource group and each group having its own set of independent containers.
Getting back to the “az container exec” command, we can see that there is no output returned again. Rather than getting an output of any kind of response, I was simply connected to my container bash terminal as the “root” user.
The highlighted text in the output from the example above, is just the container ID. If you are familiar to Docker, you know this platform always assigns a unique ID to any container hosted in such platform. The same happens with Azure Container instances, is just the container ID within the container group.
Connect to SQL Server container using SQLCMD
SQLCMD is a command line-based tool we can use to connect to any SQL Server instance, what is really cool about this tool is that works the same on any platform (Windows, Linux, macOS).
The information required to connect using SQLCMD is just the server name or IP address, user name and password. Of course there more options like database name, input file, output file, etc. in case you are interested to learn more about this utility you can check out this tip series under the SQLCMD subheading.
I can leverage one of the examples from the previous tip to get the IP Address and FQDN of this container:
[dba mastery]$ az container show --resource-group MSSQLTips --name serverless-sql-01 --query "{IP_Adress:ipAddress.ip,FQDN:ipAddress.fqdn}" --out table
IP_Address FQDN
------------- ------------------------------------------
10.80.140.210 serverless-sql-01.westus.azurecontainer.io
The user name and password information came from the time I created this container, you can take a look at the first tip as reference. Putting all these pieces of information together, I have the following connection details:
| Parameter name | Value |
|---|---|
| Server name (-S) | serverless-sql-01.westus.azurecontainer.io |
| IP Address | 10.80.140.210 |
| User (-U) | SA |
| Password (-S) | SqLr0ck$ |
Let’s use these details to connect to our SQL Server container as follows:
[dba mastery] $ sqlcmd -S serverless-sql-01.westus.azurecontainer.io / -U SA -P 'SqLr0ck$' 1> select @@servername; 2> go -------------------------------------------------------------------------------------- wk-caas-9b1e482 (1 rows affected) 1>
* Note I didn’t use the IP address, I used server name instead. Just in case the public IP changes my connection will always work using the DNS name.
Great! As you can see from the output the connection was successfully, I was even able to run a query to get the SQL Server instance name.
Connect to SQL Server container using Azure Data Studio
This example does not differ much from the previous one, by the way I’m even using macOS for both examples. Azure Data Studio is a powerful tool that runs in multiple platforms, kind of what SQLCMD does, but with a better user experience (a GUI).
Let’s get this started. First you need to open Azure Data Studio, then go to your welcome page and click on “New connection”:
Don’t worry if you don't see the Welcome page, just go to the upper menu click select Help > Welcome.
A new panel called “Connection Details” will open in the right side, here is where you have to specify the connection details as follow:
Now just complete the textbox with the information we already know:
- server name,
- user name and
- password
The last two textboxes (Server group and Name) are optional, in my case I have an existing server group called “MSSQL Tips” and of course I want this new SQL Server instance to be part of this group. Then the “Name” is simply the way you want your SQL Server connection to be labeled, for example “ACI-SQL-01“.
Connect to SQL Server container using SSSMS
Most of SQL Server data professionals are very familiar with SQL Server Management Studio (SSMS). This last example should be easy though, let’s use the information from the previous two examples to connect to our SQL Server container:
* The penguin (tux) icon at the top confirms my container is running SQL Server on Linux
Nice! We are able to connect and ready to start doing any kind of SQL Server development on this container.
Monitoring SQL Server container in Azure portal
Azure container instances leverage “Azure monitor” to provide insight about the compute resources of your container group.
At the time of writing this tip, these are the metrics available for containers:
- CPU
- Memory
- Network
It is important to note, all these metrics are only available for Linux based containers.
In order to get access to the monitoring metrics, it is just a matter to find the Azure resource. There are many ways you can find the resource to check, however I will recommend to use the “search” box at the top of the Azure portal:
As you can see, I found the “serverless-sql-01” container with a simple search. Let’s move on to check learn more about this container resource from the dashboard:
As you can see the Azure portal provides a summary of this container properties as:
- Resource group
- Location
- OS type
- FQDN
Also provides a set of buttons at the top that will allow you to start, restart, stop and delete this container. Sounds familiar, right? Well, it is pretty much what we learn to do using AZCLI in the previous tip. In case you don’t like to interact with command line instructions, you always have the option to use the Azure portal to manage your container.
Scrolling down you will see some kind of “performance dashboard” where the CPU, memory and network metrics are available:
In case you want to know more details about a specific resource, you can to use the metrics section. Just selecting it from the left side of the blade:
This new section will enable you to create custom charts, compare resources, setup alerts and more.
Conclusion
In this tip series we learned about how to connect and monitor SQL Server containers on Azure Container instances. ACI, is by far the easiest way for you to run containers in the cloud. A really good solution when you need built something, and I want to try it out really fast.
These are ACI, principal characteristics:
- Allows you to run containers without managing servers
- Containers become a compute primitive billed per second
- Allows you to secure your applications with hypervisor isolation
We also learned about how to use tools AZCLI to manage our container life cycle. SQLCMD, SSMS and ADS to manage and connect to your SQL Server instance.
As a final thought, please remember: SQL Server is the same regardless is running on a Linux based container in the cloud. You are able to connect and do the same kind of things you are used to do in a SQL Server instance hosted on a Windows machine on prem.
Next Steps
- 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
- Read Part 1 and Part 2
