If you are – as I am – mainly a .NET developer then SQL SERVER is probably the most used database system on your repertoire.
Not so long ago, SQL SERVER was running on Windows-only platforms, but now it runs also on Linux based systems. SQL SERVER is a first class citizen on the cloud, e.g. Azure SQL. Furthermore, SQL SERVER can be hosted also on Docker.
In this blog post I will quickly show how to easily and painless prepare and run SQL SERVER inside Linux Docker container.
Running SQL SERVER Docker container from prefabricated Docker image from Docker Hub.
If you run Docker engine on you environment, then you can start playing with Docker and SQL SERVER containerization. How to install and to run Docker engine is outside of scope of this blog post – but there are tons of material on the internet on how to start with Docker.
Anyway, starting with SQL SERVER on Docker is rather straightforward – you can start SQL SERVER container with one liner, e.g.
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Mypassword12!"
-p 1433:1433 --name ms-sql-server-from-hub
Docker CLI sends run command to Docker engine with environmental variables -e to accept EULA and set up administrator password (pay attention to password complexity else SQL SERVER will not start!!). Parameter -p redirects port from docker container to host system. Name flag sets name of created container and -d parameter defines image name which is used for running Docker container.
After I execute run command, docker pulls image from docker host (if not already cached on local docker host) and starts new docker container from that image.
Let’s quickly check if container is up and running:
All looks just fine. Created container exposes port (from port 1433 inside container to port 1433 on my host machine) on which SQL SERVER is listening on. Next, I open my Sql Server Management Studio (SSMS), type-in credentials and connect to SQ SERVER hosting on docker.
In this example, I used prefabricated Docker images from Docker Hub, i.e. https://hub.docker.com/_/microsoft-mssql-server. As you can see, this is very easy and quick way howto start and run SQL SERVER in docker container.
Of course, for serious work there is a lot more to the subject then just simple run command to run SQL SERVER in a docker. You can tune up SQL Server (e.g. with mssql-conf tool), mount volumes so you can create persisting storage for your user database (between docker container session restarts), running SQL SERVER docker container in private network, etc…
Creating and running SQL SERVER from Docker Hub prefabricated images is easy, painless and fast. But what about If I want to create custom image to run containers with SQL SERVER.
Let’s take a look how to do this.
Custom SQL SERVER docker container.
As usual, I start with Dockerfile from which I will build an image for my docker container. Let’s say I will use Ubuntu 18.04 as a base image, then apt-get to download and to install required packages. At the end of Dockerfile I will start SQL SERVER service. Let’s take a look at my Dockerfile:
# ubuntu 18.04 as host OS/image
LABEL maintainer "firstname.lastname@example.org"
# container will expose port 1433 -> default port on which SQL SERVER is listening on.
# this command is just as documentation, not as actually port publising
# apt-get is already on defined Ubuntu image, apt-get update to download package info from defined sources...
RUN apt-get update
# list of apps/tools needed to install SQL server on Ubuntu image
RUN apt-get install -y \
# download Microsoft GPG key for Linux products & add pakages key to apt-get store
RUN wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
# add apt repository list for SQL SERVER for Ubuntu 18.04 linux
RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
# add apt repository list for SQL SERVER for Ubuntu 18.04 linux
RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/prod.list)"
# install SQL Server tools (e.g. SQLCMD tool for later in-docker querying)
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y mssql-tools
# install sql server 2019 for Linux
RUN apt-get install -y mssql-server
# additional candy
# setup sql server instance with mssql-conf tool
# in this case sql agent enabled/disabled by default when container is started
# more configs on https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver15
RUN /opt/mssql/bin/mssql-conf set sqlagent.enabled true
# startup CMD docker command - start sql server service on container startup
CMD [ "/opt/mssql/bin/sqlservr" ]
Let’s build it:
Blue section on top is interesting. Docker is trying to optimize everything for performance, so in my case, if docker layers already exist on local system, docker reuses them. If docker layers are missing, Docker will pull and collect apps/software from external sources.
Let’s quickly take a look what steps I performed:
- After image build, I have one docker image available taged jenx/my-sql-server:1.0
- There is no running containers.
- I start new docker container from my image.
- My SQL SERVER docker container is running.
I will once again use SSMS to connect to my SQL SERVER hosted on docker container. This time, if I execute version query, I can see that SQL SERVER host is Ubuntu 18.04, as defined in Dockerfile. Furthermore – as defined in Dockerfile – SQL Agent is enabled on my system.
Using SQLCMD inside docker to query SQL SERVER
As you can see on my Dockerfile, I included also SQLCMD tool to be installed on my docker image. Let’s quickly check how to execute SQL queries with this tool:
- Check for running docker containers. Found one running container running our Ubuntu 18.04 and SQL SERVER.
- Execute command inside container (exec) and attach terminal and inputs/outputs (-it) to host console.
- Inside bash shell inside container, listing folders in docker container (ls).
- Use SQLCMD tool to connect to running SQL SERVER in the same container (-S localhost).
- I am inside SQLCMD tool where I can execute SQL queries
- Query output.
In this blog post I presented how to start with SQL SERVER and Docker. I showed how to use SQL SERVER image from Docker Hub and how to connect to SQL SERVER docker container with SSMS.
Furthermore, I presented how to build and run custom SQL SERVER docker image and how to use it.
There is a lot more to this subject, but I think this is a good starting point how to start with SQL SERVER and Docker.