Run MS SQL Server in a Docker Container
If you haven’t discovered Docker yet, you’re missing out. Docker is so popular you probably know it allows applications to run in lightweight containers similar to virtual machines but more nimble because the containers don’t have their own copy of the operating system kernel.
(If you like video, check out this YouTube video, which is very nice. Brice Ayers is even bold enough to leave his typos in, so you can see that even mortals can handle Docker stuff. The code here is essentially stolen from the code in the video.)
Docker containers allow applications to be published in configured image files that include all of the needed dependencies. This eliminates the chances that the application won’t run on a server because the environment there is different from the development environment. It also allows applications to be spun up (and down) quickly in order to save system resources. (If you only need Redis for a particular project, you don’t have to have the Redis service running if you’re not working on the project that needs it.)
While applications running in a docker container use their own file systems, you can map folders outside the container (on localhost, if you will) to folders inside the container, so that the docker container will read/write to the outside folder when it thinks it’s writing to the local folder inside the container.
In this example, we use the mapping so that even after the running docker container (or even the image itself!) is deleted, the data remains.
Here, we show how to use docker-compose to download, configure, and run SQL Server in Docker…
How to do it
Once Docker is installed, create a folder and in it a text file called docker-compose.yml with this content:
version: "3.7" services: sql-server-db: container_name: sql-server-db image: mcr.microsoft.com/mssql/server:2019-latest ports: - "1433:1433" volumes: - c:\sqldata:/var/opt/mssql/data/ environment: SA_PASSWORD: "[PASSWORD]" ACCEPT_EULA: "Y"
Note that .yml files use indentation, so make sure your indents are correct.
Open a command prompt and navigate to your new folder. Check your versions of docker and docker-compose:
docker -v
docker-compose -v
See https://docs.docker.com/compose/compose-file/ to see which docker-compose version is compatible with your docker version. For example, 3.7 is compatible with Docker 18.06.0 and above.
To see the images running:
docker-compose ps
To start the docker container defined in docker-compose.yml as a demon (-d) so it continues to run in the background:
docker-compose up -d
To bring it down:
docker-compose down
Note that as long as the running docker container is not deleted, the data will be preserved if you use docker-compose. The running container can be stopped and restarted without destroying the data. However, if you delete the running image, the data will go with it. To prevent this, we’ve used the volumes setting in the docker-compose file, which tells the container to write data to an “outside” folder (c:\sqldata) so it will be there when the image is run the next time.
Opening sqlcmd CLI in the container
Once SQL Server is running in the container, you should be able to connect to it using SSMS or any other client. (The video shows how to hit the database using javascript running in Node.js)
To run sqlcmd from within the docker container, go to a terminal and enter:
docker exec -it [CONTAINERID] /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P [PASSWORD]
Of course, [PASSWORD] needs to be your sql password and [CONTAINERID] is the name of the container, sql-server-db, in our case.
You can also run the CLI from within docker desktop, then just run the command:
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P [PASSWORD]
You can then create a database and tables, etc., using standard sql commands. Don’t forget to use “go” as the next command to execute the commands previously entered. (If you have Microsoft Sql Server Management Studio, you can create databases and tables using it as you would any Sql Server database.)
docker-compose -logs will show you the logs if something goes wrong.