I recently came across the need for a locally running SQL Server instance so that I could attach a database and deploy to Azure SQL. The windows 10 laptop I am using does not having SQL Server Developer edition installed yet, so I decided to set it up using Docker. What I like about using docker for this work is that I can script out the setup and only keep the container and image around when I need it. Additional benefits are that this setup could easily be done on a Mac or Linux environment. I could even go further with the setup and run this as a production instance by providing license information when starting up the container.

Keep reading for the written tutorial or watch the recording I made which includes more commentary on the steps involved.

References

The database I want to setup comes from a post by Brent Ozar — How to Download the Stack Overflow Database.

The quickstart guide to run SQL Server on Docker was very helpful and I only slightly changed what is provided to get my environment setup. I also referenced this Microsoft documentation on SQL Server docker containers which has some additional context and options to consider when setting up your container.

You will need Docker running. For personal learning you can use Docker desktop. For any licensing questions I recommend seeing their FAQ.

Prerequisites

How to run SQL Server on Docker

To run SQL Server on docker locally, you first need to pull the image down. This command gets the latest 2019 image. Long term you may want to find a version that works for you and use that instead of always taking latest, but for development latest is not a bad option.

sudo docker pull mcr.microsoft.com/mssql/server:2019-latest

I prefer to save the admin password I will use as an environment variable rather than pass it plain text, but really it’s up to you. The documentation recommends changing your password interactively after the container is running. I recommend that also so the command I used is available in the next section.

export sqlserver_password="pwd^Test33"

The next command runs the docker container using the password from the environment variable that was just set. Replace the path on the left with a location on your machine, which will be mapped to path /mnt/sqlserver on the running container. There are many options you can change but for a local development environment this is a great place to get started.

sudo docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=${sqlserver_password}" \
-p 1433:1433 --name sqlkickstart --hostname sqlkickstart \
-v /mnt/c/Users/dvannoy/dev/datakickstart/sqlserver/:/mnt/sqlserver \
-d mcr.microsoft.com/mssql/server:2019-latest

Now you can login to the running docker container and run commands using bash.

sudo docker exec -it sqlkickstart "bash"

The next set of statements will use sqlcmd to connect then prove that you can view databases and add a test database named testdb. Feel free to skip this step and connect with SQL Server Management Studio or Azure Data Studio instead. When prompted for a password, use the same password you set when running the container. If you did not properly set the SA_PASSWORD it may be blank.

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA

SELECT name from sys.databases
GO

create database testdb
GO

SELECT name from sys.databases
GO

EXIT

Change SA Password

The recommendation from the documentation is to change your SA password. It is easy to do that interactively with this command (replace sqlkickstart with your container name if modified it in the previous commands.

sudo docker exec -it sqlkickstart /opt/mssql-tools/bin/sqlcmd \
-S localhost -U SA \
-P "$(read -sp "Enter current SA password: "; echo "${REPLY}")" \
-Q "ALTER LOGIN SA WITH PASSWORD=\"$(read -sp "Enter new SA password: "; echo "${REPLY}")\"

Attach database

Once SQL Server is running locally you can attach the database files. The first step is to copy a database mdf file into the local path that you mapped to your docker container as /mnt/sqlserver. For my example, I use /mnt/c/Users/dvannoy/dev/datakickstart/sqlserver/ plus add an additional folder named data. I also create a log folder and copy the ldf file to it.

The next step is to connect to localhost using SQL Server Management Studio. The password is the same as what you set for SA user when you changed the password (or if you skipped that step, use the password provided when first running the container).

Once connected, right-click on Databases and select Attach.

In the Attach Database window, select Add. Browse to the mapped path and select the mdf file, then select OK.

Next you can change the path for the log file by clicking the action menu (three dots) next to the log file path.

Now your database is attached and ready for use. Because it is stored in a mapped volume the data will remain available even if you stop and restart your docker container. If you are going to create additional databases, be sure to set the location to use a mapped volume so the data persists.

Conclusion

If you followed these steps you should have a working SQL Server developer edition running with a database available. Please be aware that the Docker service and your running container consume resources while in an “Up” state so stop them when not in use. Feel free to leave comments if you hit errors in case I have come across a solution already.