Let's go nuts! Creating a custom SQL Server image from scratch with a database ready to go!

Ok, so let's put everything we've gone through in the last few wiki pages and build a custom SQL Server image, built from scratch, with a database ready to go!

Here's the dockerfile: -

FROM ubuntu:18.04

LABEL maintainer="dbafromthecold@gmail.com"

RUN useradd -u 10001 mssql

RUN apt-get update && apt-get install -y wget software-properties-common apt-transport-https
RUN wget -qO- https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
RUN apt-get update && apt-get install -y mssql-server

RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/prod.list)"
RUN apt-get update
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17 mssql-tools

RUN mkdir /var/opt/sqlserver
RUN mkdir /var/opt/sqlserver/data
RUN mkdir /var/opt/sqlserver/log
RUN mkdir /var/opt/sqlserver/backups

COPY testdatabase.mdf /var/opt/sqlserver/data
COPY testdatabase_log.ldf /var/opt/sqlserver/log
COPY attach-db.sh /var/opt/sqlserver

RUN chown -R mssql:mssql /var/opt/mssql
RUN chown -R mssql:mssql /var/opt/sqlserver

RUN chmod +x /var/opt/sqlserver/attach-db.sh

ENV ACCEPT_EULA="Y"
ENV MSSQL_PID="Developer"
ENV MSSQL_AGENT_ENABLED=True
ENV MSSQL_DATA_DIR=/var/opt/sqlserver/sqldata
ENV MSSQL_LOG_DIR=/var/opt/sqlserver/sqllog
ENV MSSQL_BACKUP_DIR=/var/opt/sqlserver/sqlbackups

USER mssql

CMD /var/opt/sqlserver/attach-db.sh & /opt/mssql/bin/sqlservr

Here's what each step does: -

1. Building from the ubuntu:18.04 image
2. Adding a label to the metadata of the image, specifying the "maintainer" (me)
3. Creating the mssql user
4. Installing SQL Server
5. Installing SQL Server tools (sqlcmd so that we can add the database later)
6. Creating custom directories for our database
7. Copying the database files and the attach-db.sh script into image
8. Changing permissions on our custom directory and the /var/opt/mssql directory (default directory for SQL Server)
9. Making the attach-db.sh script executable
10. Setting a whole bunch of environment variables (so that we don't have to specify them at container runtime)
11. Switching to the mssql user
12. Running the attach-db.sh script and then starting SQL Server

Once we have the dockerfile, we can build the image as usual: -

docker build -t customsqlimage1 .

And then check that the image is there: -

docker image ls

So now we can run a container from that image: -

docker container run -d `
-p 15789:1433 `
--env ACCEPT_EULA=Y `
--env MSSQL_SA_PASSWORD=Testing1122 `
--name sqlcontainer1 `
customsqlimage1

Wait for 30 seconds...and our database is there!

mssql-cli -S localhost,15789 -U sa -P Testing1122 -Q "SELECT [name] FROM sys.databases;"

So that's a custom SQL Server image, with sqlcmd installed, built from scratch (well, the ubuntu image), a bunch of environment variables set, and a database ready to go!

You can do some cool stuff with dockerfiles, eh? :-)