Phil Factor

13 May 2019

1 Comment

• SQL Change Automation
• Database Builds and Deployments
• Working with Containers

Database Delivery with Docker and SQL Change Automation

Docker containers can, like SQL Clone, provide a useful way of propagating databases for development and testing work. The two approaches are very different, though. Containers represent a ‘pull’ service. The users can pick the container running the right version of SQL Server, and load databases into them. Containers each have their own copy of the database, so if ten developers each work on a containerized database, we have ten copies of the data. SQL Clone, by contrast, distributes database copies (clones) from a central console, and each clone shares the actual database ‘image’, so we still only need one full copy of the data.

SQL Clone is more generally useful for maintaining individual development databases and test cells and keeping all copies of the database at the same level, and properly managed. However, Docker’s canteen-style delivery of a SQL Server instance, pre-configured exactly as required, is often convenient for ad-hoc database development tasks where you need to build, test and teardown a database. An obvious use for Docker images of SQL Server is to run up a working database of a previous or experimental version from a backup, quickly, maybe to test it, and then dispose of it. We’ll be doing that in this article.

I’ll then show how to use SQL Change Automation (SCA) to synchronize an empty copy of a development database, in a Docker container, with the latest build in source control, and fill it with data ready for testing. Finally, we’ll do a backup of the containerized database, so that we can restore it into each developer’s local container. These techniques, combined with ‘glue scripts’, can be used for supporting continuous delivery of databases.

As in most of my previous Product Learning articles, I’ll take an automated approach, because it is much faster and eliminates simple errors. It is also a ‘DevOps’ approach that allows faster, more frequent releases and eliminates much of the tedium of database deployment.

Creating a SQL Server container, stocked with any databases you need

In Running Linux SQL Server as a Container, I explained enough of the principles behind the SQL Server Docker image to get you started. We’ll continue this approach, because this is a technology you need to play with and build confidence slowly. This is a conventional Linux container, and the database will work in the same way as other Linux-based relational databases. The Docker for Windows application manages the Virtual Machine in which the containers run. The SQL Server instance communicates with the outside world in at least four possible ways:

1. By a port address that you specify (for ODBC, SMO, SSMS and other Windows-based SQL Server tools)
2. By running the core Linux SQL Server tools of sqlcmd and bcp (lowercase, please) via Docker Exec.
3. By opening a bash shell and running the Linux SQL Server tools
4. By running mssql-cli, adding it to the SQL Server container to create a new one, and then using Docker Exec into the new container

We’ll just use the second method because it cuts down on dependencies and keeps things neat. I present the script in its stripped-down form to allow you to refine it, check out the parts and walk through it. Once you have it working the way you want, it would best turn into a function. I have introduced one or two checks that are there to show how they can be done but aren’t entirely necessary. The ContainerID, for example is fetched from the container because some routines still require it in Docker. In the script, I scan through to spot errors, but some informational messages are worth checking; it always pays to check the logs.

With Docker containers, tests are rather more complicated because you must use SQL Server security, with ID and password. The SQL Server instance name has a port number applied, to differentiate different containerized instances. If you need anything in the way of features or configuration other than what comes with the standard SQL Server image, you must create a new image from the image of the SQL Server version you choose, but we won’t go into that in this article.

In the final part of the script, I restore one or more backups the new containerized instance. If you don’t specify any, then that’s fine; there is less work for the script to do!

Listing 1: Run a SQL Server instance in a container, copy and restore backup files

If successful, you’ll see output like the following, and you’ll have a running, containerized SQL Server instance that you can access the database through SSMS:

Your container will be running a standard instance of SQL Server, from the chosen image. This will create a container running a standard instance of SQL Server, from the chosen image. If you want features that aren’t in this image, you would need to create a new image, with the features you need using the docker image build. An example of a docker file to do this is here . We’ll tackle this in more detail in a later article! It is also possible to add features by running a BASH session on an existing script.

Building a database into a container from a script directory

Imagine that you have the latest build and you want to test it. You can, of course, use SQL Server 2017 localdb to do this, but then you will be limited to what you can test on it. Instead, we’ll create a Docker container hosting whatever version of SQL Server we want and stock it with the latest database build, from source control.

To get a running container, just use Listing 1 without specifying any backups to restore. If you already have an image with the same name, you’d have to stop and remove the previous reincarnation first. Then, in Listing 2 below, we create an empty database in the container, using SMO, and then use SCA’s Sync-DatabaseSchema cmdlet the empty database with the latest development version of the database, from the Git source code directory. If you are scripting, it is best to use SMO to create the database, because this takes care of generating SQL code for the CREATE DATABASE command that is appropriate for the version of SQL Server.

Having built the database, the script will stock the tables with your test datasets, assuming you specify a data directory. There are two quick ways of adding the data, either using native BCP from outside the container, or alternatively with native Linux BCP from within the container. This method, outside the container isn’t as fast as the ‘internal’ way but it is good enough for our purposes.

Listing 2: Latest database build from source control, in a container

Backing up a containerized database

Building a database and then backing it up will then allow other team members to pull an image of SQL Server and then restore the database into the container. It also makes plenty of test operations possible, particularly if they make changes to the databases.

Backing up a database is an interesting activity because it means writing to the file system within the container. If you make a backup from a container, you will then need to copy it out, whether you are performing this backup by using docker or via SSMS.

The PowerShell script in Listing 3 copies the resulting file into the host directory you specify.

Listing 3: Backup a database in a container

Conclusions

In this article, I’ve shown the basics of how one can use containers to automate a few common database development tasks. I haven’t given you an entire worked script, as these tend to be rather intimidating, but it should be very easy to combine these components to create a scripted build and test process. I would keep a shared backup directory that I’d fill with the latest version of the databases required for the application and build all the test or development containers on the destination machines, taking the backups from the shared directory.

Docker containers are very useful, but they are most useful for simple database applications. I don’t mind about having to use SQL Server Authentication, and can live with the complications of a foreign filesystem in a VM and having to specify a container instance by its port address. It takes more head-scratching, though, to obtain an image with any features out of the ordinary, and I haven’t dared go into that sort of complexity of creating a custom image in this article.

The most pressing concern, though, is that you are duplicating the data rather than sharing it. SQL Clone (the virtual cloning tool in SQL Provision), by contrast, is a database-level ‘push’ service, delivering database copies to every user’s machine, and is and is a much more effective solution than using containers if you need to distribute, and then keep refreshed, multiple copies of a large source database.

A more realistic use for containers during test and development, when working with larger databases, is to use them to distribute each new database build, stocked with modest volumes of synthetic test data.  Containers certainly take more server resources than does SQL Clone, because containers duplicate an entire server rather than reference a shared VHD, as with SQL Clone. However, I reckon that both technologies have their place in speeding the delivery process.