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


By:   |   Updated: 2019-04-04   |   Comments (12)   |   Related: More > 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

Install-Module SqlServer
$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:
docker image

Optional – Just to make your experience match my screenshots

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:

docker

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"
query system database

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/
powershell window

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

powershell window

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"
query 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".

connect to container

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.

connect to container

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.

connect to container
Next Steps


Last Updated: 2019-04-04


get scripts

next tip button



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

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.





Friday, April 05, 2019 - 11:55:21 AM - Anne Cao Back To Top

 My Azure data studio is up to date. Still have the error.

I downloaded the other one Henrov used - docker explorer, formulahendry.docker-explorer it works.

Also use powershell separately it works too.


Friday, April 05, 2019 - 11:31:04 AM - Jeff Hicks Back To Top

I figured out my issue. It wasn't clear from your article but I'm guessing you are using Linux containers. I am running Docker for Windows, originally configured for Windows containers. I eventually found out that the memory error message can be corrected by adjusting  memory resources under Settings - Advanced. However you can only get this menu when using Linux containers. I switched and was able to configure memory to 8GB. Then all of your code ran just fine.


Friday, April 05, 2019 - 11:11:06 AM - Henrov Back To Top

@Aaron Nelson,

I reset my machine again and redownloaded and reinstalled all.

Then I rebooted, waited for docker to be running and started Azure Data Studio.

I left ADS running for 20 minutes. Played a bit with Explorer (created a file through source control (git)) and left it a bit.

Went to the docker logo and the familiar warning + error after refreshing were back.

Removed docker extension and installed the other one I mentioned in a post before. I can see my containers running, can start them, stop them etc.

Reinstalled docker extension; nada!

Installed both side by side and through Explorer, choosing Docker containers I can see my containers running, can start them, stop them etc.

But the microsoft extension keeps coming up with the warning and error.

Opened a issue at guthub: https://github.com/Microsoft/vscode-docker/issues/881

grtz H


Friday, April 05, 2019 - 10:28:37 AM - Aaron Nelson Back To Top

@Anne & @Henrov

I did a little research and most everything I could find said that receiving the message "there is no data provider registered that can provide view data" is an old issue that has already been resolved. However one thing I did notice is that the issue comes up when its 'taking a while' to load the information from Docker Desktop. So as long as you have the latest version of Azure Data Studio, as well as the latest version of the Docker extension; my recommendation would be this:

  • Make sure Dockewr Desktop is completely started before you open Azure Data Studio.
  • When you open Azure Data Studio, don't go directly to Docker Explorer; go to something like Data Explorer first.
  • You can run the "docker ps" command in a PowerShell terminal to make sure your containers are up and running.
  • Once you're sure you containers are up & running, then try opening Docker Explorer to see if that makes a difference?

But honestly, you shouldn't even have to go through all of those steps. If you continue receiving that error message, I really hope that you'll go to the GitHub page for the vscode-docker extension and open a new issue. I have a feeling that it might be more than two people who run into this issue. Here's the link to get you there: https://github.com/Microsoft/vscode-docker/issues

In case it helps, here is the most relevant bug I was able to find so far on the issue: https://github.com/Microsoft/vscode/issues/61650

Please report back if you're still having the issue or if you end up opening an issue? I really do want to help get this solved if I can.


Thursday, April 04, 2019 - 4:30:36 PM - Jeff Hicks Back To Top

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 04, 2019 - 3:56:14 PM - Anne Cao Back To Top

@Aaron Nelson

I can see the container using powershell IDE. But I cannot see it in Azure data studio, in the docker: explorer it shows message There is no data provider registered that can provide view data.


Thursday, April 04, 2019 - 3:23:49 PM - Aaron Nelson Back To Top

@Anne

If you're using the Docker extension in Azure Data Studio, does it show the container as up & running?

You can also run "docker ps -a" in PowerShell to get a list of your containers and their status.


Thursday, April 04, 2019 - 3:15:11 PM - Henrov Back To Top

Aaron Nelson

Thank You. Got that working. I installed the latest Powershell Core and after thatr I saw the extension in the marketplace...

Bizar? Yes... But it works flawless now. I just downloaded ADS since I reinstalled my machine yesterday :)

I got the same problem with the docker extension as anne cao has. No data provider and a eror when I try to use the extension
I solved it by downloading a different Docker extension : https://marketplace.visualstudio.com/search?term=docker&target=VSCode&category=All%20categories&sortBy=Relevance (2nd on the list).

Thanx for the tutorial, this is exactly what I needed. This is so far the fastest way I have seen to get a SQL running in Docker.

I tip my hat to you :)


Thursday, April 04, 2019 - 2:28:09 PM - Anne Cao Back To Top

Also tried to connect to the server, got below error:

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

Windows PowerShell credential request

Enter your credentials.

Password for user sa: ********

Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The

server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to

allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.)

At line:1 char:1

+ Invoke-Sqlcmd -ServerInstance 'localhost,10001' -Credential (Get-Cred ...


Thursday, April 04, 2019 - 2:06:55 PM - Aaron Nelson Back To Top

 @Henrov

Make sure that your Azure Data Studio is up to date.  The PowerShell extension has only been available since the March release came out ~17 days ago.  If that doesn't work, just download the .VSIX file from here and add it to ADS manually: https://marketplace.visualstudio.com/_apis/public/gallery/publishers/ms-vscode/vsextensions/PowerShell/1.12.0/vspackage


Thursday, April 04, 2019 - 1:52:27 PM - Anne Cao Back To Top

Thanks for the tip!

I follow the article, and at the step after installing the container:

Run the code below to create your first SQL Server on Linux container.

I did not see it show up in the docker explorer in the Azure data studio.

It shows there is no data provider registered that can provide view data.

I installed the docker for visual studio code after I did step to install. Does it make the difference?

Thanks


Thursday, April 04, 2019 - 1:37:12 PM - Henrov Back To Top

No powershell extension to be found in the Azure Data Studio Marketplace?



download

























get free sql tips

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