Prashanth Jayaram
Azure PowerShell module - SSMS view of Azure Container Instance

Provisioning SQL Server 2019 Azure Container Instance using PowerShell

March 28, 2019 by

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:

  1. You must have .NET Framework 4.7.2 installed
  2. 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:

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

Resource group

az group

az group list

az group show

az group delete

Container

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.

Azure PowerShell module - Az group create command reference

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.

Azure PowerShell module - SSMS view of Azure Container Instance

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:

  1. Evaluation
  2. Developer
  3. Express
  4. Web
  5. Standard
  6. Enterprise

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.

Azure PowerShell module - Az Container create command refering to the status- 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.

Azure PowerShell module - Az Container create command output validation for the state - Running

Next, you’ll learn to examine the logs for the specified container mssql-2019, residing in the container group dockercontainerRG.

Azure PowerShell module - Az contianer logs commands to view the status of MS SQL 2019.

Next, verify its status by connecting to the SQL Server 2019 Azure Container Instance either using sqlcmd or PowerShell.

The output confirms that the connection was successful.

Azure PowerShell module - ACI SQL Server 2019 instance connectiviy test perfomed using sqlcmd

To delete the container, mssql-2019, from the resource group dockercontainerRG, run the following Az command.

Azure PowerShell module - Container cleanup activity using az container delete command

To remove the resource group, run the following command:

Azure PowerShell module - Container cleanup activity using az container delete command

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.

PowerShell section output is added to libarary

Click Deploy.

Azure PowerShell module - Deploy the script to create a container automatically.

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.

Azure PowerShell module - Edit the parameters before the start of the deployment

Click Save.

In the next page, select the appropriate resource group settings, accept the terms and conditions, and click Purchase.

Azure PowerShell module - Update BASICS-SETTINGS - Accept EULA

Clicking on the notification icon will show that the deployment is in progress.

Azure PowerShell module - Summarizing the Deployment script

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:

Azure PowerShell module - Connectivity test to newly created instance

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.

Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

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
Prashanth Jayaram
767 Views