Refreshing SQL Container automatically with Watchtower
Refreshing SQL Container automatically with Watchtower
Author Recent Posts Andrew PruskiAndrew Pruski is a SQL Server DBA originally from Swansea, Wales but currently living in Dublin, Ireland.
He has been working with relational databases for over 10 years and has been a SQL Server DBA for 6 of those.
Speaker at SQL Saturdays and blogs at dbafromthecold.wordpress.com Latest posts by Andrew Pruski (see all) Refreshing SQL Server Containers automatically with Watchtower - July 25, 2017 Running SQL Server Containers on Windows Server 2016 Core - April 21, 2017
SQLShack
SQL Server training EspañolRefreshing SQL Server Containers automatically with Watchtower
July 25, 2017 by Andrew PruskiIntroduction
I’ve been working with SQL Server in containers for a while now and one of the challenges that I have is ensuring that the databases within them are kept at the correct version. This is important because you don’t want your development teams testing against databases that have an out of date schema (or reference data). The databases that I store in my container image are updated on a weekly basis and currently, the process to update our containers is manual. Once the updated image has been created, the existing running containers are dropped and new ones created from the updated image. But what if we could automatically refresh our containers with the updated image? If we could do that then the only process that’s manual is updating the image. We would no longer have to worry about any containers running SQL instances with databases that are out of date. Luckily, there’s a way to do this and it’s accessible via an image on the Docker Hub called Watchtower. What Watchtower does is monitor the Docker Hub and if there’s an update to an image it will automatically refresh all running containers that are on the same host. This article will guide you through the setup of this process and how to run a simple test.Pre-requisites
What you will need to run through this process is: Two Linux VMs with internet access (referred to as Linux1 & Linux2) Docker CE edition installed on both VMs. Follow the instructions here The SQL command line tools installed on both VMs. Follow the instructions hereProcess Overview
Getting this working involves a few steps. Here is an overview of the process. The initial setup involves (on Linux1): Creating a custom SQL image with a test database Creating a repository in the Docker Hub Pushing the custom image to the Docker Hub Once the setup is complete, we’ll start up Watchtower and run a test. The steps I’ll run through are: On Linux1: Pulling the Watchtower image from the Docker Hub Running Watchtower in a container Creating two SQL containers from the custom image created in the initial setup. On Linux2: Creating an updated image to replace the one in the Docker Hub. Pushing the updated image to the Docker Hub On Linux1: Check to see if containers have been updated automaticallySetup – On Linux 1
Create a SQL container To get started with creating a custom image, the first thing I will do is spin up a container using the docker run command: docker run -d -p 15789:1433 –env ACCEPT_EULA=Y –env SA_PASSWORD=Testing1122 –name testcontainer microsoft/mssql-server-linux N.B. If the microsoft/mssql-server-linux image is not in the local repository it will be pulled down automatically. Next, I need to get the private IP address of that container so that I can connect to the SQL instance. I can do this by running the docker inspect command. The IP address is shown at the bottom of the output: docker inspect testcontainer Creating a test database within the container I’m going to connect using sqlcmd and create a test database using the private IP returned from the inspect command: sqlcmd – S 172.17.0.2 – U sa And then I can verify the databases that are in the SQL instance: select name from sys.databases go Now I’ll create a new database: create database testdatabase1; go select name from sys.databases; go Commit the container as a custom image I now have a container running a SQL instance with a custom user database. I’ll store this as a custom image by first stopping the container: docker stop testcontainer and then executing the docker commit command: docker commit testcontainer testimage Upload image to the Docker Hub The Docker Hub – https://hub.docker.com/ – is an online repository for Docker images. Not only can I pull images down from the hub, I can upload as well. It is free to register an account and there are two types of repositories that you can create, public and private (only 1 private repository for a free account). Only I will be able to see any images that I upload to a private repository whereas anyone will be able to see an image that I upload to a public repository. You need to bear this in mind when uploading anything to a public repository. I’ve create a public repository for this demo called dbafromthecold/testsqlrepository: Now I need to log into the Docker Hub back on the server using the docker login command: docker login In order to push the image to the Docker Hub the custom image needs to be tagged with our repository name first. This is done via the docker tag command which creates a new image from the named source image: docker tag testimage dbafromthecold/testsqlrepository:linux docker images Then it can be pushed to the Docker Hub: docker push dbafromthecold/testsqlrepository:linux Once that’s complete, I can verify that the image is online in the Docker Hub Or I can use the docker search command on the server: docker search dbafromthecold/testsqlrepository:linux Before moving on I’m going to clean-up the existing container and image. No pressing need to do this other than clarity (so I know which containers I’m working with): docker rm testcontainer docker rmi testimageRunning the Watchtower container – On Linux1
Pull the Watchtower image In order to automatically refresh containers, the first thing I need to do is search the Docker Hub for the Watchtower image that I want. To locate the image, use the docker search command: docker search watchtower There are a few images that will allow me to run Watchtower. There doesn’t seem to be any official images so I’m going to play it safe and pull down the image with the most stars down to my local Docker repository using the docker pull command: docker pull centurylink/watchtower When I check my local docker repository I can see that the image is there using the docker images command: docker images Start up the Watchtower container Using the docker run command I will start up the Watchtower container: docker run -d –name watchtower – v /var/run/docker.sock:/var/run/docker.sock centurylink/watchtower –interval 30 The -v/var/run/docker.sock:/var/run/docker.sock switch is required as the Watchtower container needs to interact with the Docker API so that it can monitor running containers. The –interval 30 switch specifies how often Watchtower will poll for new images. I’ve set it to 30 seconds for this demo but in reality, it would be longer as my images would not be updated that often. Running two test SQL containers For this demo, I’m going to spin up two containers from my custom image: docker run -d -p 15789:1433 –env ACCEPT_EULA=Y –env SA_PASSWORD=Testing1122 –name newcontainer dbafromthecold/testsqlrepository:linux docker run -d -p 15799:1433 –env ACCEPT_EULA=Y –env SA_PASSWORD=Testing1122 –name newcontainer2 dbafromthecold/testsqlrepository:linux Now that I have two containers running from my custom image, I’m going to move over to Linux2 in order to create an updated image and push it to the Docker Hub.Updating the custom image – Linux 2
I am now going to create a completely new image on Linux2, tag it with the same custom repository name as the image on Linux1 and push it to the Docker Hub. Once this is done I will check the running containers on Linux1 to see if they have been automatically refreshed. The first thing I will do on Linux2 is create a new container: docker run -d -p 15789:1433 –env ACCEPT_EULA=Y –env SA_PASSWORD=Testing1122 –name testcontainer microsoft/mssql-server-linux Next, I will get the container’s private IP address by running the docker inspect command: docker inspect testcontainer Now I can connect to the SQL instance within the container: sqlcmd -S 172.17.0.2 -U sa And then I can verify the databases that are in the SQL instance: select name from sys.databases go What I’m going to do now is create three new databases within the container: – create database testdatabase2; go create database testdatabase3; go create database testdatabase4; go select name from sys.databases; go The container now has three new databases within it. I’m going to commit that container as an image in the local repository on Linux2: docker stop testcontainer docker commit testcontainer updatedimage docker images The next steps are to tag that image with the repository name and push to the Docker Hub but first I need to log in: docker login Now I can use the docker tag command to create a new image with the repository name: docker tag updatedimage dbafromthecold/testsqlrepository:linux docker images The final step is to push the image to the Docker Hub: docker push dbafromthecold/testsqlrepository:linuxConfirm automatic update of containers – Linux1
The Watchtower container running on Linux1 should have detected the updated image pushed to the Docker Hub from Linux 2. I’m going to confirm by first running the docker ps command to give me the status of the containers: docker ps The uptime of the containers shows that they have only been running for about a minute. What’s happened is that Watchtower has detected that the image on the Docker Hub has changed, stopped the existing containers and restarted them using the updated image. But has Watchtower really refreshed the containers? I’m going to confirm this by logging into one of the SQL instances within the containers and checking the databases. I’ll get the IP address of one of the containers: docker inspect newcontainer Connecting using sqlcmd: sqlcmd -S 172.17.0.4 -U sa And check the databases that are in the SQL instance: select name from sys.databases; go The new databases are there! The container has been automatically refreshed with the updated image. However, note that the original database, testdatabase1, is no longer there. This is because any changes to the container that are not in the updated image will be lost. This means that the refresh of the containers needs to be scheduled carefully to avoid work being lost (and thus making unhappy developers).Conclusion
By using Watchtower running in a container we can automatically refresh our containers when their image is updated on the Docker Hub. If you use containers for development and want to ensure that you’re always testing against databases that are the correct version then this could be an option for you. Also, as far as I’m aware, this is only available on Linux. I haven’t seen any Watchtower images that will run on in Windows containers. If you have any questions, please leave a comment below Thank you for reading.References
Get Docker CE for Ubuntu Install sqlcmd and bcp the SQL Server command-line tools on Linux WatchtowerAuthor Recent Posts Andrew PruskiAndrew Pruski is a SQL Server DBA originally from Swansea, Wales but currently living in Dublin, Ireland.
He has been working with relational databases for over 10 years and has been a SQL Server DBA for 6 of those.
Speaker at SQL Saturdays and blogs at dbafromthecold.wordpress.com Latest posts by Andrew Pruski (see all) Refreshing SQL Server Containers automatically with Watchtower - July 25, 2017 Running SQL Server Containers on Windows Server 2016 Core - April 21, 2017