Install Sql Server In Docker

-->
  • Connect to an instance of SQL Server in a container; save you local changes to a new docker image; push and share docker image with Docker Hub As you could see, docker is nothing to be afraid of. It is a great way to host apps regardless of the operating system. It is also a great way to work with SQL Server without the need to install.
  • Connect to SQL Server. The following steps use the SQL Server command-line tool, sqlcmd, inside the container to connect to SQL Server. Use the docker exec -it command to start an interactive bash shell inside your running container.

Applies to: SQL Server 2019 (15.x) - Linux

To view your Docker containers, run the docker ps command: sudo docker ps -a If the STATUS column shows a status of Up, SQL Server is running in the container and listening on the port specified in the PORTS column. If the STATUS column for your SQL Server container shows Exited, see the Troubleshooting section of the configuration guide.

This article explains how to install SQL Server Machine Learning Services on Docker. You can use Machine Learning Services to execute Python and R scripts in-database. We do not provide pre-built containers with Machine Learning Services. You can create one from the SQL Server containers using an example template available on GitHub.

Install sql server in docker tutorial

Prerequisites

  • Git command-line interface.

  • Docker Engine 1.8+ on any supported Linux distribution, or Docker for Mac/Windows. For more information, see Get Docker.

  • See also the system requirements for SQL Server on Linux.

Clone the mssql-docker repository

The following command clones the mssql-docker git repository to a local directory.

  1. Open a Bash terminal on Linux or Mac.

  2. Create a directory to hold a local copy of the mssql-docker repository.

  3. Run the git clone command to clone the mssql-docker repository:

Build a SQL Server Linux container image

Complete the following steps to build the docker image:

  1. Change the directory to the mssql-mlservices directory:

  2. In the same directory, run the following command:

  3. Run the command:

    Note

    Any of the following values can be used for MSSQL_PID: Developer (free), Express (free), Enteprise (paid), Standard (paid). If you are using a paid edition, please ensure that you have purchased a license. Replace (password) with your actual password. Volume mounting using -v is optional. Replace (directory on the host OS) with an actual directory where you want to mount the database data and log files.

  4. Confirm by running the following command:

    Note

    To build the Docker image, you must install packages that are several GBs in size. The script may take some time to finish running, depending on network bandwidth.

Run the SQL Server Linux container image

  1. Set your environment variables before running the container. Set the PATH_TO_MSSQL environment variable to a host directory:

    Note

    The process for running production SQL Server editions in containers is slightly different. For more information, see Configure SQL Server container images on Docker. If you use the same container names and ports, the rest of this walkthrough still works with production containers.

  2. To view your Docker containers, run the docker ps command:

  3. If the STATUS column shows a status of Up, SQL Server is running in the container and listening on the port specified in the PORTS column. If the STATUS column for your SQL Server container shows Exited, see the Troubleshooting section of the configuration guide.

    Output:

Enable Machine Learning Services

To enable Machine Learning Services, connect to your SQL Server instance and run the following T-SQL statement:

Next steps

Python developers can learn how to use Python with SQL Server by following these tutorials:

R developers can get started with some simple examples, and learn the basics of how R works with SQL Server. For your next step, see the following links:

In October 2017 Microsoft announced the general availability of SQL Server 2017 for Windows, Linux and Docker. And since I started to play around with Docker I thought it’s a good idea to setup my next development environment for SQL Server on Docker?

Why? Because Docker provides the ability to package and run an application in a loosely isolated environment called a container. And a Docker container is much more lightweight than a Virtual Machine, because it doesn’t need an extra Operating System, it runs directly within the host machine’s kernel.

If you want to learn more about Docker itself, please have a look here: https://docs.docker.com/engine/docker-overview/

Let’s get started

Install sql server express in docker container

Since I run Windows 10 on my developer machine it need to setup SQL Server in a Windows Docker container and I figured out, that there some pitfalls that I came along. So I though it’s maybe benefitial to write a Step by step setup guide.

Step 1: Download and Setup Docker for Windows

To get started we need to download and install Docker first on our machine. You can find the Windows setup here: https://www.docker.com/docker-windows

And there is also a very nice setup guide you can find here: https://docs.docker.com/docker-for-windows/install/. After your setup I would recommend to test if everything works fine. Just follow these examples and run your “Hello World” on Docker.

Additionally I would recommend to install Kitematic, a simple and powerful graphical user interface to manage your Docker environment.

Step 2: Setup Docker for Windows containers

Install Sql Server In Docker

After installing Docker on your machine Docker will start automatically in the background. As per default, Docker runs with Linux containers and you have to switch to Windows containers first. This is very is, just right click on your Docker icon in the taskbar and select “Switch to Windows containers”.

Step 3: Get your SQL Server container image

After we performed the basic setup steps, we now need to get the SQL Server docker image. There are two sources available to get preconfigured images:

  • Docker Store: The store is for publishers, that want to sell and distribute docker content (more details).
  • Docker Hub: The hub is a cloud-based image registry to link to publishers own code repositories (more details).

Microsoft made SQL Server available in the Store and in the Hub:

  • SQL Server in Docker Store:
    • Linux Developer Edition: https://store.docker.com/images/mssql-server-linux
    • Windows Express Edition: https://store.docker.com/images/mssql-server-windows-express
  • SQL Server in Docker Hub:
    • Linux Edition: https://hub.docker.com/r/microsoft/mssql-server-linux/
    • Windows Developer Edition: https://hub.docker.com/r/microsoft/mssql-server-windows-developer/
    • Windows Express Edition: https://hub.docker.com/r/microsoft/mssql-server-windows-express/

I will continue with the SQL Server 2017 Developer Edition for Windows. In order to download the image run the following command in Powershell oder the Commandline.

This will download the latest version of SQL Server 2017. This step can take a while, depending on your internet connection.

Install Sql Server Odbc Driver Docker

Step 4: Start your SQL Server image

After your image download has been completed you can start your SQL Server image with the following command:

For example you can run it like this:

In this case, the image will have a friendly name “sql1” and SQL Server will run on port 1433.

For more details please have a look here: https://hub.docker.com/r/microsoft/mssql-server-windows-developer/

Step 5: Test your connection with sqlcmd

In order to test if the image works fine, connect with sqlcmd and run a short sql query. To connect with sqlcmd you can run the following command in your command line:

In order to figure the IP address of your Docker image you can run:

For example

Within sqlcmd run a small SQL statement like:

Step 6: Create a SQL Server Login

Install Sql Server In Docker Linux

Now that you can connect with your SQL Server I would recommend to create a login instead of using the SA account. You can do this also with sqlcmd:

Step 7: Connect with SQL Server Management Studio

When you can connect with sqlcmd you can of course also use SQL Server Management Studio to connect to your SQL instance. You can find the latest version of SQL Server Management Studio here: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Start SSMS and then connect to SQL Server as you would also do it with a normal local machine:

Step 8: Download and install sample Database AdventureWorks

Very good now we are able to use our SQL Server environement as we are normally used to it. But it makes for fun, if we would have at least some data to play with. Let’s get AdventureWorks and install it on our Docker image.

AdventureWorks is a sample database that you can download here.

In my case I downloaded the AdventureWorks Data Warehouse backup file.

Next we need to get the backup into our image. To do so you have to first stop your image and then run the following command:

In my case:

Start your image again, get your new IP-Address and connect with SQL Server Management Studio. Then restore your database from your backup file and you are good to go.

Step 9: Query some data and have fun

The last step is the easiest one. Just fire some SQL queries in your Management Studio.

Install Sql Server In Docker

Sources: