Rajendra Gupta
standard SQL Server on Linux repository

Setting up customized SQL Server images using Azure Container Registry and Azure Container Instances

January 25, 2022 by

This article will walk you through the process of creating, importing a customized SQL Server image into Azure Container Registry and finally deploying an Azure Container instance using it.

Introduction

The Azure Container Instances come in handy for developers to create a SQL Server instance on Azure Container for their development, testing quickly. In the articles (TOC at the bottom), we used the SQL Server on Linux 2019 images in the container instances. These images are standard provided by Microsoft for different SQL versions of 2017 and 2019. Suppose you need a few specific configurations or user databases for your SQL instance. You need to configure each instance or every instance you deploy from the standard images with these standard images. Therefore, it might be a problem for you to repeat this process over many containers.

In this article, we will look at the following points.

  • Create customized SQL Server images from the standard image
  • Import the customized image into the Azure Container registry
  • Deploy customized images for Azure Container Instances

Let’s start with the various steps required to satisfy the specified tasks.

Create customized SQL Server images from the standard image for Azure Container Instances

This step imports the standard SQL Server on Linux image and customizes it with a few configuration changes and a user database.

Install Docker on Ubuntu Linux

To install the docker on Ubuntu Linux, launch the terminal and follow the steps outlined below.

  • Use the apt package to install repository over HTTPS

Download Docker container in Ubuntu

  • Use Curl command to add the Docker’s official GPG key:

Docker’s official GPG key

  • Use the following lsb_release -cs setups the stable repository using the docker download URL

docker download URL

  • Install the docker engine latest version

docker engine latest version

  • Use the standard SQL Server on Linux repository on docker container: We use the SQL Server 2019 CU12 version for this demonstration.

It downloads the repository image from the MCR repository:

standard SQL Server on Linux repository

  • Use the docker run command for SQL Server on Linux instance

In the script, we specify the following parameters.

  • ACCEPT_EULA= Y: Accept the license conditions
  • SA_PASSWORD: Specify the SA password for connecting to the SQL Server instance
  • -p: maps the TCP port on the container with the host instance
  • –name: It is the container name

The docker run command configures the SQL Server with the specified configuration and returns the container GUID. You can check the status of the docker using the command – docker ps -a

run command for SQL Server on Linux instance

You can install the Azure Data Studio on Ubuntu for connecting to SQL Server on Linux. Navigate to URL, download ADS and configure it.

Download Azure Data Studio

In the Azure Data Studio, enter the following details for the connection.

  • Server: localhost
  • Authentication: SQL authentication
  • User id: SA
  • Password: As specified in the docker_run command.

As shown below, we have an active instance of SQL Server on Linux version 15.0.4153.1 and developer edition.

SQL Server dashboard

Customize SQL Server container image

We installed the standard image for SQL Server on Linux in the docker container. Suppose we want to deploy a SQL Server database and objects in it. Later, we will build a customized docker container image with this user database.

The script below creates a database [SQLShackDemo] and a table [Emp] with one record.

Customize SQL Server container image

To build the customized image, we need to stop and commit the container image.

As shown below, the container status is exited.

Stop docker

The docker commit command saves the container image locally. It returns the container GUID as shown below.

Commit docker image

Use the docker images to list down the locally installed images. We have an image with none specified in the repository and TAG. It is the image that we recently committed using docker commit.

List down docker images

Before we move forward, navigate to URL https://hub.docker.com/signup and set up a container account and repository.

For this article, I set up the repository sqlshackcontainerdemo as shown below.

Create a docker login and repositroy

We need to add the tag using the docker tag command using the image id returned by the docker image. It also uses the docker hub repository where we want to save the custom image.

add a docker tag

Export the customized image into the docker hub

We will export and save the customized image into our docker hub account. To export, the first login to docker using the docker login command.

It stores the password in an encrypted form in the /root/.docker/config.json file.

Login to docker

Use the docker push command in the following format.

Therefore, my command to push the customized image is as below

It starts uploading the customized docker container images into the specified docker account.

Docker push image to repositroy

The status changes to pushed once the upload is successful into a docker container.

Status of docker image

Refresh the docker account, and you can view the container image.

Verify in the docker portal

Publish container image to Azure Container Registry for Azure Container Instances

Microsoft has Azure Container Registry for hosting the docker images and other related artefacts. You can use these images for container-based deployment, similar to a regular image.

In the Azure portal, search for Azure Container Registry. It displays the following page for the container registries.

Publish container image to Azure Container Registry

In the container registry, input the following values.

  • Registry name
  • location
  • SKU: You can choose from the basic, standard, or premium service tiers. You can refer to

https://docs.microsoft.com/en-us/azure/container-registry/container-registry-skus for Azure Container Registry service tiers.

Create container registry

The private endpoint and customer-managed SKU are available for the premium SKU. With the standard SKU, we use the public endpoint with the access keys.

Networking components

Encryption keys

Once the Azure container registry instance is deployed, note down the login server. Azure login server

We also require access keys for the container registry. Click on the access keys in the container registry dashboard and enable the admin user. Note down the container registry username and password.

Generate access keys

Validate the access keys using the docker login command. In the docker login command, we need to specify the following values.

  • login server
  • username
  • password

Login to Azure contaienr registry

To push the customized image into the Azure Container registry, we need to tag the container image with the Azure login server name. We require an image tag with a login server that we retrieved a couple of commands ago and the name of the image:

In this command, we have the following values.

  • docker container image: rajendragupta16/sqlshackcontainerdemo:mydemo
  • Azure login server tag: sqlshackdemoreg.azure.io/sqlshackdemoreg:mydemo

Once the image tag is set, use the docker image and validate the repository and TAG values.

Set the image tag to container registry

Now, we can push the customized image stored in the docker account to the Azure container registry.

It starts the image push and returns the repository details and size in the output, as shown below.

Push the image

Push the image

Navigate to the container registry and verify that the customized image exists. The dashboard also returns the repository, tag, platform and docker pull command.

View customized repository in Azure Container Registry

You can also fetch the repository list using the Azure CLI command – az acr repository list.

Verify registry

Deploy an Azure Container Instance using Azure Container registry image

In the articles (TOC at the bottom), we deployed SQL Server 2019 Linux using the standard docker images. As stated earlier, we have the requirement of a customized image so that every time we deploy it, we have the user database and objects.

In the Azure Container Instances configuration page, select the option – Azure Container registry. It automatically populates information for the registry, image, and tag.

Deploy an Azure Container Instance using Azure Container registry image

On the review and validation page, verify the following values.

  • Image registry login server
  • Image
  • Image registry username
  • OS type

Review page

Click on create for deploying the Azure Container instance and connect to SQL Server using the public IP address or FQDN.

IP address, FQDN

As shown below, we have the user database [SQLShackDemo] with the table [Emp].

Verify custom image

The table has one record that we inserted for customizing the SQL Server container image.

Verify records

Conclusion

In this article, we explored customizing the container images and uploading them into the Azure image registry. You can use the customized registry and deploy containers with databases, objects, configurations as per your standards. It can help save your time to deploy database, their best practices, configurations every time you build a new container instance.

Rajendra Gupta
383 Views