Andrew Pruski

Refreshing SQL Server Containers automatically with Watchtower

July 25, 2017 by

Introduction

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 here

Process 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 automatically

Setup – 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:

  1. docker run -d -p 15789:1433 –env ACCEPT_EULA=Y –env SA_PASSWORD=Testing1122
  2. –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:

  1. 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:

  1. sqlcmd – S 172.17.0.2 – U sa  

And then I can verify the databases that are in the SQL instance:

  1. select name from sys.databases  
  2. go

Now I’ll create a new database:

  1. create  database  testdatabase1;    
  2. go  
  3. select  name  from  sys.databases;    
  4. 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:

  1. docker stop testcontainer  

and then executing the docker commit command:

  1. 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:

  1. 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:

  1. docker tag testimage dbafromthecold/testsqlrepository:linux
  3. docker images

Then it can be pushed to the Docker Hub:

  1. 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:

  1. 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):

  1. docker rm testcontainer  
  3. docker rmi testimage  

Running 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:

  1. 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:

  1. docker pull centurylink/watchtower  

When I check my local docker repository I can see that the image is there using the docker images command:

  1. docker images  

Start up the Watchtower container

Using the docker run command I will start up the Watchtower container:

  1. 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:

  1. docker run -d -p 15789:1433 –env ACCEPT_EULA=Y –env SA_PASSWORD=Testing1122
  2. –name newcontainer dbafromthecold/testsqlrepository:linux  
  1. docker run -d -p 15799:1433 –env ACCEPT_EULA=Y –env SA_PASSWORD=Testing1122
  2. –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:

  1. 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:

  1. docker inspect testcontainer

Now I can connect to the SQL instance within the container:

  1. sqlcmd -S 172.17.0.2 -U sa

And then I can verify the databases that are in the SQL instance:

  1. select name from sys.databases   
  2. go  

What I’m going to do now is create three new databases within the container: –

  1. create database testdatabase2;
  2. go 
  3. create database testdatabase3;
  4. go 
  5. create database testdatabase4;
  6. go
  7. select name from sys.databases;
  8. 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:

  1. docker stop testcontainer 
  3. docker commit testcontainer updatedimage  
  5. 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:

  1. docker login  

Now I can use the docker tag command to create a new image with the repository name:

  1. docker tag updatedimage dbafromthecold/testsqlrepository:linux
  3. docker images  

The final step is to push the image to the Docker Hub:

  1. docker push dbafromthecold/testsqlrepository:linux  

Confirm 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:

  1. 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:

  1. docker inspect newcontainer  

Connecting using sqlcmd:

  1. sqlcmd -S 172.17.0.4 -U sa  

And check the databases that are in the SQL instance:

  1. select name from sys.databases;  
  2. 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


Andrew Pruski
Latest posts by Andrew Pruski (see all)

Related posts:

  1. Running SQL Server Containers on Windows Server 2016 Core
  2. Creating your own SQL Server docker image
  3. Automate Delivery of SQL Server Production Data Environments Using Containers
  4. Understanding Backup and Restore operations in SQL Server Docker Containers
  5. How to use Windows hosted file shares to support SQL Server containers
Containers

About Andrew Pruski

Andrew 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

168 Views