Create a SQL Server on Linux Container with 5 Lines of Code

By: Aaron Nelson | Comments (12) | Related: > Containers

Problem

Docker can be an easy way to setup SQL Server databases for a demo or test out new features in the next version SQL Server, but where do you start?

Solution

This guide is intended for Data Professionals who want to use their Windows 10 machine to try out "Official images for Microsoft SQL Server on Linux for Docker Engine". However, many of these steps work exactly the same if you're running MacOS. In this article, we'll explore building a SQL-on-Linux Docker Container that uses only Internal Storage.

Prerequisites

Download Docker Desktop for Windows: https://hub.docker.com/editions/community/docker-ce-desktop-windows
Install the latest SqlServer PowerShell module from the PowerShell Gallery (run the code below in your favorite PowerShell editor):

Install-Module SqlServer

Download sample databases from GitHub, specifically the AdventureWorks2016 database: https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak
You can also use the below PowerShell code to download the AdventureWorks2016.bak file:

$BakURL = "https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2016.bak";
$BakFile = "$($Home)/Downloads/AdventureWorks2016.bak";
Invoke-WebRequest -Uri $BakURL -OutFile $BakFile;

Finally, if on Windows, make sure you see this icon in your system tray after following the above steps:

Optional – Just to make your experience match my screenshots

Download Azure Data Studio (ADS): aka.ms/GetAzureDataStudio
Add the PowerShell extension from the Marketplace in Azure Data Studio
Download & install the Docker .VSIX extension into your ADS. Click on "Download Extension" on the right-side of this page: https://marketplace.visualstudio.com/items?itemName=PeterJausovec.vscode-docker

To be clear, using the Docker .VSIX extension is definitely not mandatory, but it will help make it a little more obvious when you have successfully created your Docker image. It will also give you a visual cue if you forget and leave your Docker container running.

Building a SQL-on-Linux Docker Container Using Internal Storage

In this scenario we're going to have all our databases be inside our container. Use this option when you want to save the databases you've added to the container into a new image. This allows you to play around, then blow away your container and fire up a new container that already has all of your customizations already baked into your new image. Note: Building those customizations is outside the scope of this article.

You're not going to believe how easy this is. You can use either the PowerShell ISE or Azure Data Studio for this.

Step 1

Open Azure Data Studio and open the Command Palette by hit Ctrl + Shift + P > choose Terminal: focus terminal. (Obviously, if you're using the ISE you can just copy the code straight in.)

Step 2

Run the code below to create your first SQL Server on Linux container. Make sure the code is all on one line.

docker run -d -p 10001:1433 -e ACCEPT_EULA=Y -e SA_PASSWORD=Test1234 --name testcontainer01 microsoft/mssql-server-linux

Poof, you have a container! Don't believe me? Just click on MobyDock icon inside of ADS and see for yourself:

You can verify it's up and running and has only the system databases by running the following code. You will be prompted to either enter the sa password from the script above, or whatever you decided to use for your sa password.

#Requires -Modules SqlServer 
Invoke-Sqlcmd -ServerInstance 'localhost,10001' -Credential (Get-Credential sa) -Query "SELECT name from sys.databases"

Step 3

Next, run this code to copy the Adventureworks2016.bak file inside your container. You'll need to adjust this code to wherever you downloaded your Adventureworks2016.bak file.

docker cp "$($Home)/Downloads/AdventureWorks2016.bak" testcontainer01:'var/opt/mssql/data/'

Verifying the file was copied: If you're in the ISE, you will need to click on the little PowerShell icon in the toolbar which will open a new PowerShell condole window. If you're in ADS, just use the Terminal you're already in.

Step 4

Run this code to go inside the container and start a Bash session:

docker exec -it testcontainer01 bash

Step 5

Now, technically were in a bash session, and we're already inside the file system of the container. Next, let's verify the file was successfully copied. Search for the Adventureworks2016.bak file using the code below, it should be easy to spot if you're in the ISE because it's in green.

ls var/opt/mssql/data/

If you have spotted the file like in the image above, just type Exit and leave that session.

This is how the above steps should look in the ADS Terminal.

Most SQL Server professionals are used to SSMS doing a really nice job of changing the file path for the Data & Log files by default. You could still use SSMS to do that for you, but that would add manual steps & time to the process of building out your containers. With wanting repeatability & speed being part of the reason for using containers in the first place, we will cover two options for automating database restores into your containers.

In this first example we'll cover how to accomplish the relocating of the data files using PowerShell and the Restore-SqlDatabase cmdlet from the SqlServer module. If you're not used to working with SQL Server in PowerShell, loading up the $RelocateData & $RelocateLog variables with the directory path and file names of the Adventureworks2016 database might seem like extra work. However, you're probably going to restore more than one database, in which case PowerShell is going to be very easy to extend.

Before you move on to these next steps, make sure you have exited out of the bash session. The next code examples need to be run from PowerShell.

Step 6

Run one of the following blocks of code, but not both, to restore the Adventureworks2016 database to your new container instance.

Restore-SqlDatabase option:

#Requires -Modules SqlServer
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('AdventureWorks2016_Data', '/var/opt/mssql/data/AdventureWorks2016_Data.mdf');
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile('AdventureWorks2016_Log', '/var/opt/mssql/data/AdventureWorks2016_Log.ldf');
Restore-SqlDatabase -ServerInstance 'localhost,10001' -Credential (Get-Credential sa) -BackupFile 'AdventureWorks2016.bak' -Database 'AdventureWorks2016'  -RelocateFile @($RelocateData,$RelocateLog);

Invoke-SQLcmd option:

#Requires -Modules SqlServer 
Invoke-Sqlcmd -ServerInstance 'localhost,10001' -Username sa -Password Test1234 -Query "
RESTORE DATABASE [AdventureWorks2016] FROM  DISK = N'/var/opt/mssql/data/AdventureWorks2016.bak' WITH  FILE = 1,
MOVE N'AdventureWorks2016_Data' TO N'/var/opt/mssql/data/AdventureWorks2016_Data.mdf',
MOVE N'AdventureWorks2016_Log' TO N'/var/opt/mssql/data/AdventureWorks2016_Log.ldf',
NOUNLOAD,  STATS = 5"

And now just to prove that you really have restored that database, run this code:

Invoke-Sqlcmd -ServerInstance 'localhost,10001' -Credential (Get-Credential sa) -Query "SELECT name from sys.databases"

There you have it, a SQL Server on Linux container ready for you to connect to and run your demo queries against. Next, restore some more databases and use the same connection information to connect to this instance in ADS or SSMS.

Connecting to a SQL Server Instance

To connect to a SQL Server instance, click on the Data Explorer icon in the upper-left of Azure Data Studio (or hit Ctrl + G) and you will be presented with a Connection dialog. Fill in the necessary information. It is important to note that in the dialog next to Server you should put "localhost,10001" and in the dialog next to Name you can put any name you want, I chose to use "TestContainer01".

Once you have successfully connected, you will be able to navigate the SQL Server instance similar to how you can via Object Explorer in SSMS.

Once connected you can start writing T-SQL queries to run against your new SQL-on-Linux container. You can also right-click on the instance and choose Manage to bring up a dashboard experience for working with that instance.

Next Steps

Read more tips on Containers.
Get the code for all the steps in this article in a single Gist.
Docker Commands with examples for SQL Server DBAs.

About the author

Aaron Nelson is a Senior SQL Server Architect with over 10 years experience in architecture, BI, development and tuning.

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

View all my tips

Thursday, April 4, 2019 - 4:30:36 PM - Jeff Hicks	Back To Top (79472)
I can't seem to keep the container running. Even after creating it if I try to start with with -i. I get a message "sqlservr: This program requires a machine with at least 2000 megabytes of memory." That shouldn't be an issue.

Thursday, April 4, 2019 - 1:37:12 PM - Henrov	Back To Top (79465)
No powershell extension to be found in the Azure Data Studio Marketplace?