Use Microsoft SQL server docker container with Azure Data Studio

The need for Docker Containers

Docker containers evolved from the need to massively scale applications. To understand this consider that an application might be single threaded and CPU bound (meaning it can’t take advantage of more than one CPU core) and require a decent amount of memory. To scale this application, you’ll need to run more instances of it. You can do this by two ways:

• Run more instances of the application on each hardware instance (vertical scaling).
• Add more hardware resources to your pool of resources in order to grow the number of application instances that you can run at any time (horizontal scaling).

Each docker container runs as a separate process that shares the resources of the underlying operating system. This enables containers to start and stop quickly. And this helps it to scale at any size. Using Docker Containers for databases

With containers, you can approach the database as an on-demand utility, which means that each application can have its own dedicated database that can be spun up as needed.

Getting Started

Pulling the image

(Note: You can skip this step and directly start from the second step. Before running the containers, Docker will see if the image exists on your local machine. If the image does not exists, Docker will pull it from the docker hub. I've just included this step to show you how you can pull an image from the docker hub.)

You can find images for Microsoft SQL server on the docker hub. For this tutorial I've used the image with the tag 2019-CU3-ubuntu-18.04. You can pull the image by running the command:

docker pull mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04

Here the image is mcr.microsoft.com/mssql/server and tag is 2019-CU3-ubuntu-18.04. Docker tags convey useful information about a specific image version/variant and are a way of referencing your image.

Run a container

After you have pulled the docker image, you have to run and start the container. You can run and start a docker container by using the command:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<Password>" -p 1433:1433 --name <NAME> -d <CONTAINER_IMAGE>

Let's break this command down and look at the different parameters we've specified:

• -e ‘ACCEPT_EULA=Y’ With the -e option you set an environment variable, on which SQL Server is dependent on. In our case we have to accept the EULA to be able to use SQL Server.

• -e ‘SA_PASSWORD=helloWorld!‘ With the SA_PASSWORD environment variable we set the password for the SA login.

• -p 1433:1433 With the -p option we bind a port on our host machine to a port in the Container. The port on the left side of the colon is the port on the host machine, and the port on the right side of the colon is the port in the Container. Here I bind the default SQL Server port of 1433 within the Container to the port 1433 on my machine. You can also just specify a single port here and docker will map the same port of the Container to the host machine (eg: just -p 1433 and 1433 of the container will be mapped to 1433 of the host machine).

• –name : With the –name option we assign a custom name to our Docker Container.

• -d :And with the -d option we specify the Docker Image that we have pulled previously, and that you want to run the Docker Container detached from the Terminal. This just means that you can close your Terminal, and your Docker Container is still running in the background.

After running the command, you will notice Docker will return an unique id. This is the container id for the container that docker has just started. You can reference the container in any other commands either by using the name that you've set (in our case mssql-server) or first 4 characters of the container id (7ca7). Connect Azure Studio with Docker Container

We can connect azure data studio with running container here directly to localhost, because in the last step we have exposed the port 1433 of the Docker Container to our host machine.

Once you open the azure data studio window, click on new connection.

Enter the connection details as follows:

• Connection Type: Microsoft SQL Server

• Server: localhost

• Authentication Type: SQL Login

• Username: sa

• Password: whatever password you entered while running the container.

Verify the connection details:

Once connection is established successfully, you can see server details on home pane where you can verify that container id is coming as computer name on Azure Data Studio which means you have successfully connected to running docker container.

Try out some commands to check the installation:

Once database is connected, you might want to try out some commands to check if everything is running properly.

Here I am just creating a new database called new_application and a table Persons in that database.

## Check if the commands are being executed in your Docker container:

In a terminal run the command:

docker exec -it <CONTAINER_NAME> bash

• docker exec allows you to run commands inside containers.

• The -it option does two things. i stands for interactive and keeps STDIN open even if the container is not attached and t allocates a pseudo-TTY (tty is the name of linux text only console).

• The bash option specifies that you want to run a bash session inside the container.

Once a bash session is opened inside the container, we need to connect to the Microsoft SQL database instance running inside the container to run SQL commands. We do this by running the command:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YOUR_PASSWORD>"

In this command the various options we have specified are:

• /opt/mssql-tools/bin/sqlcmd is the path where the process is located.

• -S specifies the server we want to connect to. In our case this is localhost as we are running the database locally in our container.

• -U is the user with which we want to connect to the container.

• -P is the password we set previously while creating the container.

Once you have connected to the database, you can run SQL queries to crosscheck if the commands we ran previously in Azure Data Studio worked.

Here we can see that a table Persons is created in the database new_application.