Creating a custom SQL Server Docker Image on top of the Official Image

Hello people, so today let's discuss how we can create our own custom SQL Server image on top of the official image.

This can be really useful in multiple scenarios like when we have a new team member joining our team. Instead of giving them a fresh instance of SQL Server we can have an image of the initial setup and they can just pull it and be ready to work on it

Pre-requisite

• Docker Desktop up and running on the machine. Can be download from here https://docs.docker.com/v17.09/docker-for-windows/install/
• Account on Docker Hub so that we can publish and then pull our custom Sql Server docker image
• Running instance of a fresh SQL Server container from the official image available on docker hub mcr.microsoft.com/mssql/server:2017-latest.

docker run -e "ACCEPT_EULA=Y" "SA_PASSWORD=MYPASSWORD123" -p 1433:1433 --name MyContainerName -d mcr.microsoft.com/mssql/server:2017-latest

Setting up the SQL Server 🔥

• Once we have the server up and running, login to the SQL Server using SQL Server Management studio with the IP address of the host
• Username would be SU and password is MYPASSWORD123 as used in the command above to run the container
• Next we can setup our server like the database, tables manually or using any backup file etc
• Now we have our database in place and we want to create an image of this setup so as to when next time someone pulls the image they don't have to manually import the database anymore

Create Custom Docker Image

• Firstly, stop the running container using the command

docker stop MyContainerName

• Next we push our changes on to the container so that we can build an image of it

docker commit MyContainerName

• Then copy the image of our specific container from the list using the command

Docker images

• The newly created image does not have a repository and tag. Execute the following command to tag the image

docker tag <imageID> <docker-hub-username>/<docker-hub-repository-name>:<tag-name>

For example: docker tag a82e969d1395 rajatsrivas/ myownsql:sqlCustomImage

• Now our image is built and we can create a container using the image

docker run -p 1433:1433 --name sqlCustomImage -d rajatsrivas/ myownsql

• If you are logged into docker hub on local Docker Desktop this step will be skipped else login using the command prompt

docker login -username=rajatsrivas

• Enter the password into the next line and finally push the image to the docker hub repository

docker push rajatsrivas/myownsql

• Go to https://hub.docker.com/
• We should have the image which we pushed to the docker hub

Pull and Run our Custom Image 🏃‍♂️

• Pull the image on to any machine using the command

docker pull rajatsrivas/myownsql:latest

• Run the container and access the server on the SSMS. The server should have the database which was imported and setup in the earlier steps

docker run -p 1433:1433 --name <container-name> -d rajatsrivas/myownsql:latest

Conclusion

So there it is we have implemented our custom image on top of an official docker image available.

This is quite a small step in onboarding but one can leverage similar implementations for setting up sandbox environments very quickly and efficiently.

Hope this was useful. Keep learning keep building