[Basic] Docker image 🐳 Dockerfile — SQL Server with custom prefill DB scripts

<— Agenda — >

• [Basics] Understand Docker Image Creation and base SQL server image
• Learn basic docker command to build, run, start, stop, exec

Dockerfile

• Setup resource to build Docker image
• Create Dockerfile to setup SQL SERVER 2017 with SQL scripts to fill in random data
• Bring your SQL Server anywhere !!

[Basics] Docker Image Creation and base SQL server image

• Run a normal MSQL Server 2017 image from the official docker image.
• Create + Commit Basic SQL Image

1. Pull SQL Server Image

• Pull an image from official Microsoft registry source
• We will start with this base image to run the SQL server

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

2. Run docker container from the pulled image

• Run docker with the environment -e variables to setup password (should contain a letter, numbers, and a special char)
• -p port binding <host>:<container>
• -d detach mode: run in the background
• — name = specify container name

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=abcDEF123#" \
-p 1433:1433 --name sqlsv \
-d mcr.microsoft.com/mssql/server:2017-latest

Using docker ps — you can see docker container “sqlsv” is running

3. Modifying the database

• Either modifying db using SQL command line through shell inside docker container or use database tool

Connection settings for database tool such as .. SSMS :
host = localhost:1433
user = sa
password = abcDEF123#

Alternatively, you can get inside docker container and use bash shell by:

docker exec -it sqlsv bash

Access SQL command-line :
- Login to local SQL Server using the following credentials
- S server, -U user, -P password

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "abcDEF123#"

On each line prompt: type this to create Database name “TestDb”

> CREATE DATABASE MYDB
> SELECT Name from sys.Databases
> GO

new Database created MYDB

4. Create new Docker Image

• We will save this db change to our new docker image so that whenever we run from that image we will have the database MYDB

docker commit sqlsv

docker images After commit need to specify repo and tag for the created image

Get the image Id and Tag it

docker tag <imageid> youruser/docker-sql:initial

OR you can just do

docker commit <container_id_or_name> <image-name>:<version>

After tagging

5. [Optional] Push your new Docker Image

Push your image to dockerhub — Need to create the account first !

docker login --username=yourusername
$ Password: <Enter you password>
docker push yourusername/docker-sql:initial

If you push to existing tag it will override

Now your image is available on dockerhub.com

If you want to check the image contains the new database run the container on a new image and check using db tool or sqlcmd same as previous steps.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=abcDEF123#" \
-p 1433:1433 --name newsqlsv \
-d yourusername/docker-sql:initial

[Dockerfile]

• Now we will set up the SQL server with database data inside the dockerfile with the scripts instead and Dockerfile will include the instructions of the process.

touch Dockerfile

1. Create Dockerfile

FROM mcr.microsoft.com/mssql/server:2017-latest
ENV ACCEPT_EULA=Y
ENV SA_PASSWORD=abcDEF123#
ENV MSSQL_PID=Developer
ENV MSSQL_TCP_PORT=1433
WORKDIR /src
COPY filldata.sql ./filldata.sql
RUN (/opt/mssql/bin/sqlservr --accept-eula & ) | grep -q "Service Broker manager has started" &&  /opt/mssql-tools/bin/sqlcmd -S127.0.0.1 -Usa -PabcDEF123# -i filldata.sql

See below for what each line does

2. Create SQL File

touch filldata.sql

Copy filldata.sql that will

• Generate Database MYDB1–10 that contains same table and records
• Create Table tblAuthors with 3 columns: Id, name, country
• Insert 10 Dummy Records

Current file structure:

.
├── Dockerfile
└── filldata.sql

3. Build a docker image

docker build -t youruser/sql-gen:initial .

-t = tagging image
docker build <dockerhub_user>/<repo>:<tag> workingdir

4. Run docker image

• Now run the newly created image

docker run -d -p 1433:1433 --name sqlgen youruser/sql-gen:initial

We did not provide the password here because in dockerfile already define it as environment variables.
But you can also override the dockerfile environment by providing them to docker run

Now check to see the data if the script and our setup is working correctly.

$ docker exec -it sqlgen bash
$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "abcDEF123#"
> SELECT name FROM master.dbo.sysdatabases
> SELECT * FROM MYDB10.dbo.tblAuthors
> GO

using db tool

< — Tips — >

You can also setup Dockerfile and sqlcmd to
Recover Database from the Backup file like this…

-- Generate your backup file from somewhere
-- BACKUP DATABASE MYDB
-- TO DISK = '/backups/testDB.bak'

RESTORE DATABASE Restored_MYDB
FROM DISK='/backups/testDB.bak'

Summary of the docker commands

🎉 🎉 🎉

Sourcecode: https://github.com/brightdays/dockersqlgen

References:

Credit: https://www.sqlshack.com/creating-your-own-sql-server-docker-image

Docker: Install containers for SQL Server on Linux - SQL Server