[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
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-latestENV ACCEPT_EULA=Y
ENV SA_PASSWORD=abcDEF123#
ENV MSSQL_PID=Developer
ENV MSSQL_TCP_PORT=1433WORKDIR /srcCOPY filldata.sql ./filldata.sqlRUN (/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'
🎉 🎉 🎉
Sourcecode: https://github.com/brightdays/dockersqlgen
References:
Credit: https://www.sqlshack.com/creating-your-own-sql-server-docker-image