This guide is all about provisioning SQL Server 2019 using Azure Container Instance (ACI), including the installation and configuration. In this article, we talk about the Azure Container Instance (ACI), the Azure PowerShell module, installation and configuration of SQL Server using the Azure PowerShell module, and automation of installation and deployment using templates.
Introduction
Azure Container Instance (ACI) is a service offering for managing and running containers on the Azure cloud. In this article you’ll see in detail, how to setup and automate SQL Server 2019 installation on Azure Container Instance using the Azure PowerShell module. Also, I will show you how to create a template for the same. The template is a JSON file, which gives you the ability to modify the input parameters as you would edit plain text, and helps perform a successful deployment in a relatively simpler way.
Containers are in great demand because they are lightweight in nature and provides an agile method to test the code before it gets deployed into production. Using Azure Container Instance (ACI) provides an interface to provision deploy containers with very a few simple steps.
Note: Microsoft Azure Container Instances (ACI) provides hypervisor isolation — it ensures that the containers run without sharing a kernel.
The Azure PowerShell module
The new Azure (Az) PowerShell module is built to leverage PowerShell Core and the Cloud Shell. The module is compatible with PowerShell 5.1 as well as PowerShell Core. Az provides cross-platform capabilities and ensures that PowerShell and PowerShell Core users get the latest Azure interface tooling platform. By default, Azure Cloud Shell ships with Az; the module is also available for download and install from the PowerShell Gallery.
Note: Az is a replacement for AzureRM and AzureRM.Netcore modules. Az runs on PowerShell 5.1 and PowerShell Core. All cmdlets use Az as their noun prefix. Data plane and management plane cmdlets for all services have now been combined in the Azure PowerShell module. Az ships with new cmdlets to enable script compatibility with AzureRM (Enable-/Disable-AzureRmAlias).
How to install Azure PowerShell module using PowerShellGet
This section contains the instructions to install the Azure PowerShell module. Here are the prerequisites:
- You must have .NET Framework 4.7.2 installed
- You can have both AzureRM and Azure PowerShell modules but it is recommended to remove all Azure RM modules
Install the module in a single step, with the following command:
|
1 |
Install-Module -Name Az |
Note: If your script uses AzureRm or Azure in cmdlet names, then it’s time to start getting used to Az instead. The process is simple and requires relatively less effort to redesign entire scripts. For example, if you’re using New-AzureRMVm in your script, it becomes New-AzVm.
Setting up SQL Server 2019 ACI
In this section we discuss the Az cmdlets used to setup and install SQL Server 2019 container instance using Azure Container Instance (ACI).
The following table lists the Az commands that are used to configure SQL 2019:
Resource type |
Command |
|---|---|
az group list | |
az group show | |
az group delete | |
az container create | |
Container logs | az container logs |
Container delete | az container delete |
Container list | az container list |
Now, let us discuss the steps that are required to configure SQL Server 2019 Azure Container Instance (ACI).
First, create a new resource group dockercontainerRG using the az create group cmdlets. In this case, dockercontainerRG is configured in the location, eastUS.
|
1 |
PS Azure:\> az group create --name dockercontainerRG --location eastUS |
Next, create a container in the container group, dockercontainerRG that runs Linux, with 1 CPU, 3.5 GB of memory, and mapped to a public IP address, with port 1433 opened, followed by the SQL Server configuration parameters.
|
1 |
PS Azure:\> az container create --image mcr.microsoft.com/mssql/server:2019-CTP2.2-ubuntu --name mssql-2019 --resource-group dbmigrationRG --cpu 1 --memory 3.5 --port 1433 --ip-address public -e ACCEPT_EULA=Y MSSQL_SA_PASSWORD=thanVitha@2019 MSSQL_PID=Developer MSSQL_COLLATION=Latin1_General_CI_AS MSSQL_ENABLE_HADR=Y --location eastus --dns-name-label sqldemo |
Parameter | Description |
|---|---|
-e ‘ACCEPT_EULA=Y’ | Set the variable ACCEPT_EULA=Y to confirm the acceptance of the End-User Licensing Agreement (EULA). This is a mandatory setting for the SQL Server installation. |
-e ‘SA_PASSWORD= | Specify a strong password as it must meet the SQL Server password requirements. It is also a mandatory setting for the SQL Server installation and configuration. |
-p 1433 | Map a TCP port. In this case, SQL Server set to listen on port 1433 of the container. |
–name | Specify a custom name for the container. In this example, the custom name mssql-2019 is used. The name must be unique. |
–image mcr.microsoft.com/mssql/server:2019-CTP2.2-ubuntu | This is the image that corresponds to SQL Server 2019. You can also change it to microsoft/mssql-server-linux:latest for SQL Server 2017 installation. In this case, the image is referring to the SQL Server 2019 CTP 2.2 Linux container image. |
MSSQL_PID= Developer | Specify the SQL Server edition. The following are the possible values:
|
MSSQL_COLLATION= Latin1_General_CI_AS | Specify the SQL Server collation. In this case, we use Latin1_General_CI_AS |
MSSQL_ENABLE_HADR=Y | This option enables an Availability Group (AG). |
–dns-name-label | The filed dns-name- label is for identifying the container with the public IP. |
In the output, you can see the container creation process status. the following screenshot shows it as Running.
In about a minute or two, the container will be up and ready to use. You can scroll the displayed log details and check for the state Running in the output.
Next, you’ll learn to examine the logs for the specified container mssql-2019, residing in the container group dockercontainerRG.
|
1 |
PS Azure:\> az container logs --resource-group dockercontainerRG --name mssql-2019 |
Next, verify its status by connecting to the SQL Server 2019 Azure Container Instance either using sqlcmd or PowerShell.
|
1 |
PS Azure:\> sqlcmd -S 104.45.186.59 -U SA -P thanVitha@2019 -Q "select @@version" |
The output confirms that the connection was successful.
To delete the container, mssql-2019, from the resource group dockercontainerRG, run the following Az command.
|
1 |
PS Azure:\> az container delete --name mssql-2019 --resource-group dockercontainerRG |
To remove the resource group, run the following command:
|
1 |
PS Azure:\> az group delete --name dockercontainerRG |
Automation
Let us now see how to automate the installation and configuration of SQL Server using Azure Container Instance (ACI).
In the Azure portal blade, click on Automation script and then click Add to library. This option will add the template to the Azure library.
Click Deploy.
Now, you should see an option to edit the template. Click Edit template and change the values to the appropriate ones. In this case, defaultValue is set to mssql-2019-1.
Click Save.
In the next page, select the appropriate resource group settings, accept the terms and conditions, and click Purchase.
Clicking on the notification icon will show that the deployment is in progress.
In about two minutes, the SQL Server 2019 instance will be up and running with the name, mssql-2019-1. You can connect to the instance using sqlcmd as shown below:
Summary
In this article, we covered the installation and configuration of a SQL Server 2019 Ubuntu Azure Container Instance. We also discussed the steps that are required to automate the deployment of SQL Server 2019 using templates and the Azure PowerShell module. I hope you found this article useful. Feel free to start a conversation in the comments below.
Table of contents
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021