[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 !! 

Source code: https://github.com/brightdays/dockersqlgen

Medium post: https://medium.com/bright-days/basic-docker-image-dockerfile-sql-server-with-custom-prefill-db-script-8f12f197867a

[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

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

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

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>

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

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

< — 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'