In this article, we will review how to create custom SQL Server docker images and run the containers from the custom images, upload the custom images to the docker hub.

Deploying SQL Server on docker really is quick and easy. Deploying SQL Server on docker includes pulling the SQL Server image from the repository and creating the container from the image. But the base SQL Server image comes with only the system databases and default configuration settings. In some cases, you may want to run a SQL Server container with your databases in place that are required for your application or you may want to run SQL Server containers with your own configuration already in place. To achieve this, we must build your own SQL Server image on top of the base image from Microsoft.

Let us go through the few examples of building custom SQL Server docker images. In this demo, I used Docker on windows.

Download docker on Windows from https://docs.docker.com/v17.09/docker-for-windows/install/

download the package from the stable channel and install it.

Once the docker is installed, open the command prompt and verify the installation by running docker –version command. Please refer to the below image.

Pull SQL Server Linux container image from the Microsoft container registry using the below command. In this case, I used SQL Server 2017 version developer edition.

Execute the following command to see the downloaded image.

Execute the following command to run the SQL Server container from the image mcr.microsoft.com/mssql/server:2017-latest.

Execute the below command to see if the container created from the SQL Server docker image is up and running.

Now login to the SQL Server using SQL Server Management studio with the IP address of the host.

By default, The Agent XP is disabled so I am enabling it by using sp_configure.

For demo purposes, I am making a few configuration changes using sp_configure. In this case, I am enabling Agent XP and the configuration option called “remote admin connections”.

If you do not have SQL Server management studio use SQLCMD to modify the configuration. To run commands against the container, execute the following command. Replace the name of the container with yours.

Connect to the SQL Server using SQLCMD.

Enter the password of “sa” login.

Once your login is successful, execute sp_configure to change the configuration options.

Now we have changed the configuration to required values. Exit the bash.

Now stop the container and build the custom SQL Server docker image from the container. Execute the following command to stop the container.

Check the status of the container using the below command.

Once the container is stopped we must build an image of the container so that we can run a container based on this image.

Once you commit the container, a new image is created. We can check the newly created image using the following command.

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

Breakout of the above command.

• Image ID: a82e969d1395
• Docker hub username: rangach99
• Docker hub repository: my-own98
• TAG name: sqlcust

Now I am removing the previously created container by executing docker rm command. Please refer to the below image.

Now let us create the container from the new custom SQL Server docker image we built earlier. Execute the following command to create a container from the new image.

Login to the SQL Server using SQL Server management studio or using SQLCMD and check for the configuration changes we made. Please refer to the below images which shows the new container built from the custom image has Agent XP enabled and the configuration option “remote admin connections” enabled.

We can create multiple instances with the same image. All the instances are identical with the same password for “sa” login.

Upload the custom image to docker hub

We can upload the custom SQL Server docker image we built to the docker hub so that we can download it from anywhere and run containers from the image.

Docker push command is used to push the local image to the docker hub. Before pushing the image to docker hub we must have a docker hub account and a repository created.

In my case, rangach99 is the docker hub account and my-own98 is the repository. the image should be properly tagged as shown above.

We need to login to the docker hub from the command prompt using docker command. Execute the below command.

Enter the password of your login.

Execute the following command to push the custom SQL Server docker image to the docker hub.

Go to https://hub.docker.com/ and login to the docker hub using your docker hub user id and password. We can see the image which we pushed to the docker hub.

We can now pull the image from the docker hub from anywhere and run the container from the downloaded image. Make sure you log in to the docker hub with an account that has privileges to pull the image.

Execute the following command to pull the image from the docker hub repository.

Build images with databases in place.

We can build custom SQL Server docker images that have your databases already in place so that when you run the container from the image it deploys SQL server along with your database that is required for your application. Let us see step by step to build a custom image that has the database called “MyProductDB”.

Pull the base image from the Microsoft repository and start the container.

Start the container from the image you downloaded.

Check the name of the container by executing the below command.

Now we need to copy the backup of the database to container and restore the backup. Execute the following command to copy the backup file MyProductDB.bak from D drive of your system to the path in a container called “RBC”

Now verify if the backup file is successfully copied to a specified path in the container. To run commands against the container, execute the following command.

Please refer to the below image that shows that backup file in the container.

Login to the SQL Server using SQL Server management studio and restore the backup.

Exit the bash and stop the container.

Now commit the container to build an image out of it.

Now tag the image you created.

Now you can push this image to your docker hub repository and pull it the from docker hub in another system and create a container from the image. This container will have SQL Server with the database you restored.

Creating custom SQL Server docker images using Docker file

We can also create custom images using docker file. First, we should create a docker file and build an image from it.

As I am using windows for demo purpose, I created a folder called “dockerfiles” under “C:\Users\yourusername” and copied the backup file to the folder “dockerfiles”.

Now open notepad from start and paste the following code and save it as “Dockerfile” without any extension under the folder “dockerfiles”.

Open the command prompt and navigate to the folder “dockerfiles”. execute the following command to build an image from the docker file.

Once the build is successful without any errors or permission issues, it creates a custom SQL Server docker image with the repository name as “rangach99/my-own98” and the tag name as “SQLImagefromDockerFile”.

Execute the below command to check the images.

Now let us create a container from the image we built above using the docker file. Execute the following command to create and start a container from the above-created image.

Let us connect to the SQL Server using SQL Server management studio and see if we have the required database.

We can see the database MyProductDB is created in the SQL Server.

Conclusion

In this article, we explored how to create custom SQL Server docker images using docker commit command and building images from the docker file. In case you have any questions, please feel free to ask in the comment section below.

To continue your learning about Docker, please refer to the Docker articles on SQL Shack.