Update: This article was from my old website. I am curating what I had posted there and will be reposting items here with any new content that is appropriate, and removing anything that is outdate. This post was originally from 2016. For this update - I am using a Mac Mini system I have, with 32GB of RAM, but limited hard drive space.
A good portion of my professional career has been spent working with Microsoft SQL Server and a lot of my spare time is spent experimenting with Linux and Open Source applications. The publishing of a Linux version of SQL Server was hugely exciting to me and the ability to run SQL Server in a Docker container was amazing as well!
There are some caveats though:
The standard SQL Server Management Studio GUI tool is only available for Windows systems. If you are connecting with a Mac or Linux system, you'll need to use Azure Data Studio which is being retired in February 2026 or Visual Studio Code.
SQL Server out of the box only uses local authentication. This means User IDs/Passwords are stored in the database system. To have SQL Server on Linux containers use Active Directory Authentication check out this Microsoft tutorial. With AD, you can use the same user account across multiple machines, and don’t need to login to each one, and your credentials are maintained in a central location.
The Linux version does not include Reporting Services or Analysis Services, two other components of the Microsoft BI Stack. While not essential for many organizations, these tools are included as part of a fully licensed version of SQL Server on a Windows platform. You can host the Reporting Services database on SQL Server Linux, you just can't run the services.
Running SQL Server Linux on Docker
For now, lets look at how to get a SQL Server Docker container running. Connect to your Docker host, and create a folder where you’d like your database files to persist. This way, when your container shuts down for whatever reason, you will stay have you data files. In my case, I created it under my user folder on the Mac. The path was /Users/edpflager/Documents/mssql
docker run -e ACCEPT_EULA=Y -e MSSQL_PID=Evaluation -e SA_PASSWORD=string1password -p 1433:1433 -v /home/me/mssql:/var/opt/mssql -d microsoft/mssql-server-linux
Keep in mind this is for a quick and dirty setup! For a production system, you'll want to use a more secure method.
Now, click the Connect button, and if all went well, your screen will refresh, and you'll see your server information:
Notice that the version is Developer Edition. On the left if you click the Databases icon, you'll see the four databases that are necessary for SQL Server to operate.
I’ll walk through restoring a copy of AdventureWorks to your Docker container in a future post but for now, go ahead and try out ADS and play with your new server.
Then enter the following command to start the container. You’ll need to include a strong system administrator password as part of the command using the enviroment setting -e MSSQL_SA_PASSWORD. The default port that SQL Server uses is 1433. If you want to change that, you can alter the -p option. The PID environment variable tells Docker this is an Development version of SQL Server. No production use allowed! Substitute the path to your data folder after the -v flag. For my case, the command to start my container looks like this:
docker run -e ACCEPT_EULA=Y -e MSSQL_PID=Evaluation -e SA_PASSWORD=string1password -p 1433:1433 -v /home/me/mssql:/var/opt/mssql -d microsoft/mssql-server-linux
Keep in mind this is for a quick and dirty setup! For a production system, you'll want to use a more secure method.
On your host machine in your data folder, you’ll now see several new folders created: data, etc, log, and secrets. The data folder containers the four databases that SQL Server needs to run: master, model, msdbdata and tempdb. The etc folder is probably empty. The log folder is where logs are written to by the SQL Server engine, and the secrets folder contains a file identifying your machine.
Connecting Azure Data Studio to SQL Server Linux on Docker
At this point you should have Docker running with a SQL Server Linux container. Download Azure Data Studio (ADS) from the link above, for your operating system and install it. For my purposes, I am running it on my Mac Mini which is also where I am running Docker. Once you start ADS, you should see a screen similar to this one:
Click the box labeled Create a Connection at the bottom left. You'll be prompted to enter your connection info.
- Connection Type is Microsoft SQL Server
- Input type can be left as Parameters
- In the Server box, enter localhost if you are running on the same machine. If not, enter the IP address where you are running SQL Server, or the server name if you have DNS enabled.
- Change the login to SQL login.
- For the user name, enter sa (the default admin account name).
- Enter the password you used in your startup line above.
- Check the remember password box if you want (again not for Production setup!)
- Leave the database field set to default.
- Finally change the Trust Server certificate option to True.
The connection window should look like this:
Notice that the version is Developer Edition. On the left if you click the Databases icon, you'll see the four databases that are necessary for SQL Server to operate.
I’ll walk through restoring a copy of AdventureWorks to your Docker container in a future post but for now, go ahead and try out ADS and play with your new server.
Comments
Post a Comment