Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

FREE Webcast - Delivering Microsoft SQL Server High Availability on AWS
 

SQL Server 2017 installation on Ubuntu using Docker


By:   |   Read Comments   |   Related Tips: More > 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:

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.

files

Update the apt package index with below command.

$sudo apt-get update			
docker
labuser

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

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

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 –			
labuser

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			
docker

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

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			
docker

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

$ apt-cache madison docker-ce			
docker

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

$ sudo apt-get install docker-ce			
package
ubuntu

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

docker

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			
docker

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

docker

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
			
Parameter Description
-e 'ACCEPT_EULA=Y' This parameter set the end user license agreement by providing value 'Y' to variable  ACCEPT_EULA variable.
-e 'SA_PASSWORD <password>' Specify the strong password for sa meeting Complex password requirement. The password should be at least 8 characters long and should contain letters, digit and special characters as well.
-p 1433:1433 It Maps a TCP port on the host environment (first value) with a TCP port in the container (second value). For example, -p 1433:1433 maps host port 1433 to container port 1433.
--name Specify a unique name for the container. If we do not specify any name, a random name is assigned.
-V Map the volume by using the -v option in the format -v [local file system directory]:[container directory]
-d Runs the container in detached mode (that is, the container runs in the background until it is manually shut down).
mssql-server-linux:2017-latest SQL Server 2017 Linux container image

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.

docker

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			
docker

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"			
docker

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			
docker

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.

version

Find out the name of the available databases.

master

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.


Last Update:


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools