SQL Server 2017 installation on Ubuntu using Docker

By: Rajendra Gupta   |   Updated: 2018-08-02   |   Comments   |   Related: > SQL Server on Linux

Problem

In this tip, we will learn how to install and run a SQL Server Linux container image using a Docker on the Ubuntu operating system.

Solution

SQL Server on Linux supports the same enterprise security capabilities that customers rely on with SQL Server on Windows. SQL Server 2017 supports the Linux operating system and can be used with the Docker Engine 1.8 and higher on Linux. In previous tips, SQL Server on Linux we covered the following installations of SQL Server:

• Installing SQL Server vNext CTP1 on Red Hat Linux 72
• Installing SQL Server vNext on Suse Linux Server
• Installing SQL Server vNext on Ubuntu
• Run SQL Server vNext CTP 1 as a Docker Container on a Mac

Docker installation on Ubuntu Linux

Docker is an open-source tool to create, deploy and run applications using containers. Containers allow packaging an application with all of the parts it needs such as kernel libraries. You can think of a Docker as a virtual machine. A virtual machine creates a whole operating system, but Docker allows the application to use the underlying operating system kernel libraries.  It is useful for both developers and system administrators. With the help of Docker, we can start many instances of SQL Server on the same host, use it and remove them with ease, if required.

Docker is available in two editions:

• Community Edition (CE): It is useful for individual developers. A small team can start with Docker container-based applications using this edition.
• Enterprise Edition (EE): It is used for enterprise development and large business-critical applications.

For this tip, we will be using the Docker Community Edition (CE).

Connect to a terminal on am Ubuntu server and launch the terminal.

Update the apt package index with below command.

$sudo apt-get update			

Install packages to allow apt to use a repository over HTTPS.

$ sudo apt-get install apt-transport-https ca-certificates curl software-properties-common			

Once the prerequisites are installed, download the GPG key to access the Docker package repository:

$ curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add –			

Verify that you now have the key with the fingerprint 9DC8 5822 9FC7 DD38 854A E2D8 8D81 803C 0EBF CD88, by searching for the last 8 characters of the fingerprint.

$ sudo apt-key fingerprint 0EBFCD88			

Next, add the Docker repository to the list of “known” repositories and update the packages list using apt-get update:

$ sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"

Docker CE has two update channels, stable and edge:

• Stable gives you reliable updates every quarter.
• Edge gives you new features every month.

You always need the stable repository, even if you want to install builds from the edge or test repositories as well. 

Update the apt package index.

$ sudo apt-get update			

You can use apt-cache to verify docker-ce package availability:

$ apt-cache madison docker-ce			

Install Docker’s docker-ce package by using apt-get:

$ sudo apt-get install docker-ce			

Verify that Docker CE is installed correctly by running the hello-world image.

This command downloads a test image and runs it in a container. When the container runs, it prints an informational message and exits. Docker is now ready to deploy the SQL Server 2017 Linux image.

SQL Server on Docker Ubuntu

We will now configure and run SQL Server 2017 Linux image on a Docker container for Ubuntu. Follow the steps mentioned below.

Pull the SQL Server 2017 Linux container image from the Docker Hub.

$ sudo su
# docker pull microsoft/mssql-server-linux:2017-latest			

Once all of the setup parts are downloaded, it extracts the files and shows the message pull is complete.

The pull command downloads the requested image to your local file system. By default, the latest image version is pulled. We can request a specific version by appending the version tag after the image name (for example, microsoft/mssql-server-Linux:latest). It can take several minutes to pull down an image and extract it. We can see the progress of downloads and extractions in the console.

In this step, run the Docker image in a container using the Docker run command with the below parameters:

$ sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=mssql-labs1' -p 1433:1433 --name sql1 -v /home/labuser/dockervolumes/lab01:/var/opt/mssql -d microsoft/mssql-server-linux:2017-latest
			

In this example, we map a directory in the local file system using –V parameter to the container’s data directory, which allows database files to be stored locally so that changes to the database persist after the container is stopped. A data volume is a specially designated directory within one or more containers that bypasses the union file system. Data volumes are designed to persist data, independently of the container’s lifecycle.

That’s it. The SQL Server installation and configuration is now complete within a few clicks. We do not need to wait for the long set up to complete. We can start many fresh instances of SQL Server Linux using the Docker containers.

To see a list of all containers run the below command. If we want to see only active containers, run the command without the  –a parameter.

$ sudo docker ps -a			

The STATUS column shows a status of up that means SQL Server is running in the container and listening on the port specified in the PORTS column.

Testing connectivity with SQL Server

We can use the SQL Server command-line tool, sqlcmd, inside the container to connect to SQL Server.  Once connected to sqlcmd, we can run T-SQL commands as usual.

To connect to SQL Server follow the below steps.

Execute an interactive bash shell on the container with the below command:

$ sudo docker exec -it sql1 "bash"			

This opens up interactive bash session on the container. We used sql1 as container name with parameter --n, so we have passed that name here in the command.

Now we are inside the container, as the prompt has changed to # instead of $. Now use the below command to connect to SQL Server with sqlcmd.

# /opt/mssql-tools/bin/sqlcmd -S localhost -U sa			

We can also specify the password with parameter -p. If you do not specify, it asks for the password as shown.

Notice that the prompt changed to 1>, it shows we are connected to SQL Server using sqlcmd.

We can now run commands as usual. For example, use below command select @@version for the SQL Server version.

Find out the name of the available databases.

Note: We have to type the Go command in the next line in order to execute the code.

You can now use the SQL Server to perform routine tasks in the Docker container for the Ubuntu Linux operating system.

Next Steps

• In the next tip, we will install Visual Studio using a Docker container image connect and run queries from there.
• Read more about Docker in docs.
• Go through the SQL Server on Linux tips and get familiar with SQL Server on Linux.
• Read more about SQL Server 2017 for Linux.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips