This post shows how to run a SQL Server Instance on about any computer using Docker Containers. Your next step might be to get a sample database into that SQL Server Instance.
Thanks to Microsoft, you can get their sample databases as T-SQL scripts. You can use these to install these databases on whatever server you are connected to, including your "containerized" SQL Server instance.
Let's do the Northwind database. We'll use Azure Data Studio, since it works on Win/Linux/Mac.
Here are the steps:
Click on this link, which will open up the text of the instnwnd.sql file.
Copy all that code - I used ctrl-a to select it all, then ctrl-c to copy all of that into the clipboard.
Open up Azure Data Studio (ADS)
Right-click on your database server, and choose New Query
Paste all the code into the query window - I used ctrl-v to do the paste from the clipboard
Now click Run
The image above shows my docker container instance running on localhost port 1401, which is mapped to my docker container's localhost port 1433 (as explained in the prior post.)
Depending on your machine, this may take several minutes to run, and will give many output messages. Depending on the version of SQL Server that you are running, you are likely to get a few harmless error messages and warnings like "Tokenization is skipped for long lines ...".
Now right-click on your Databases folder and choose Refresh to see the Northwind database, with all its data.
You can do this same procedure with any of the sample databases available here.
I've found it very convenient to have a local instance of SQL Server. I use it
for:
quick tests
performance testing
demonstrations
development
testing
work around limitations of a cloud instance
This has become really easy with containers. Even if you have the ability to install SQL Server natively on the host (Windows or Linux), it is much quicker and easier to use containers.
This means that you can run an instance of SQL Server on Windows, Linux, and Mac.
The first time I did this, I followed Bob Ward's Take the SQL Server Mac challenge, and had a SQL instance running on my Macbook, while in the audience listening to Bob's presentation. Yep, under 5 minutes.
So here is my pared-down How-To. I've tried to do a minimal setup that should work on Win, Linux, Mac, with very little adjustment.
Here's the overview:
Download, install, and run Docker
Get to an (elevated?) command prompt
Run a long-ish docker command
Verify via Docker
Connect from Host with client
Shut it down
1. Docker Installation
Head over to Docker and download Docker Desktop. I recommend the Stable version for whichever platform you are on:
Follow the installation instructions for your platform. This varies slightly for the platform you are on. If you can install a program on your machine, you can do this.
2. Command Prompt
For Mac, use the Terminal Application to get a command line. Here's how.
For Windows, type cmd in the search area, and choose "Run As Administrator". Here's how. Administrator privileges are not required for everything, but use it this first time to make sure you can get it working.
3. Run Docker Command
This is a long-ish command, but all the pieces are necessary. There are various ways to do this, but i'm using a "quick" method found in Microsoft's documentation. This method both pulls the SQL Server 2019 container image, and runs it with the correct parameters. Here's the command that I used:
Either of the above should work via cut-and-paste, but I find it easier to read and visually verify the one-parameter-per-line version.
The basic command (without options, which won't work) is:
docker run mcr.microsoft.com/mssql/server:2019-latest
This creates a process that runs the image mcr.microsoft.com/mssql/server:2019-latest. If that image happens to be on the local machine, great. If not, it will download the image from mcr.microsoft.com.
Note the "tag" at the end of the image url: 2019-latest. Microsoft releases many container images from which you can choose. Find them all on DockerHub. With this tag, are basically saying, get the latest stable release of SQL Server 2019.
That basic command will not create a usable sql server container. You need some additional pieces:
-e "ACCEPT_EULA=Y"
passes "Y" to SQL Server, indicating acceptance of the End User License Agreement (EULA)
-e "SA_PASSWORD=123ABCabc"
passes the "sa" user password to SQL Server
you will log in to this server using SQL Authentication, with "sa" as the username, and "123ABCabc" as the password
I'm using "123ABCabc" because it satisfies the password policy requirements (9 chars, upper and lower, digits"
"sa" is short for system administrator, and is basically the root user on the server instance that you are running
-p 1401:1433
maps the container's port 1433 to your machine's port 1401
SQL Server "listens" for connections on this port by default
So if you connect to your host machine's port 1401, you will be connected to the container's port 1433
in my situation, I am running a SQL instance on the host that is already using the default port 1433.
--name sqlsrvr1
this gives the container your desired name, rather than a docker-generated name
when issuing commands on the host, you can refer to the container by this name
-h sqlsrvr1
this sets the HOSTNAME environment variable inside the container, rather than the docker-generated name
when issuing commands inside the container, you can refer the system inside the container by this name
-d
this runs the container "detached"
this means that the container will run in the background rather than the foreground
4. Verify via Docker
If you've run the command in the previous section, you should have a running container based on the mcr.microsoft.com/mssql/server:2019-latest image. Run this at the command line to see it:
docker ps
This command gives a list of the running containers. You should see results something like this:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d325650811b1 mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 43 minutes ago Up 43 minutes 0.0.0.0:1401->1433/tcp sqlsrvr1
The results indicate that the container is running, with the proper name, and listening on the proper port
Verify via Connection
Now fire up a SQL client to connect to the container, and use it. I'm using Azure Data Studio (ADS), which will run on Windows, MacOS, and Linux. Here's the "new connection" dialog in ADS
Just to be clear:
ADS is running on the host
it is connecting to the host IP address (localhost)
it is connecting to the host Port 1401
Docker has mapped host port 1401 to the container's port 1433, which is the default port for SQL Server
So ADS will be connected to the container's port 1433
Note the Server text box has "url, port" - there's a comma between the url and the port
You should see that your container is shown in the Server Explorer on the left-hand side of the ADS window.
6. Shut it down
You can, of course, leave this container running in the background and use it whenever you need it. But what if you want to shut it down? Or entirely get rid of the container? Here's how to do it:
docker stop sqlsrvr1
This stops the container. It will no longer be running in the background. The container is still there, and can be restarted like this:
docker start sqlsrvr1
To entirely remove the container from the host machine (it cannot be started, and will no longer take up space on your host's drive), you would issue this command on the stopped container:
docker rm sqlsrvr1
Summary
Docker can seem overwhelming at first, especially if you are not comfortable working at the command line. But if you are looking to run a local instance of SQL Server, running a Docker container is the fastest path.
