Managing database files for SQL Server containers

A database container is like any other Docker container, but with a focus on statefulness. You'll want to ensure your database files are stored outside of the container, so you can replace the database container without losing any data. You can easily do that with volumes, as we saw in the last chapter, but there is a catch.

If you build a custom SQL Server image with a deployed database, your database files will be inside the image in a known location. You can run a container from that image without mounting a volume and it will just work, but the data will be stored in the container's writable layer. If you replace the container, when you have a database upgrade to perform, then you'll lose all your data.

Instead, you can run the container with a volume mounted from the host, mapping the expected SQL Server data directory from a host directory so your files live outside of the container in a known location on the host. This way, you can ensure your data files are stored in a RAID array on your server. But that means you can't deploy the database in the Dockerfile because the data directory will have data files from the image and you can't mount a directory that isn't empty.

The SQL Server images from Microsoft deal with this by letting you attach database and log files when it runs, so it works on the basis that you already have your database files on the host. In this case, you can use the image directly, mount your data folder, and run a SQL Server container with arguments telling it which database(s) to attach. This is a very limited approach, though it means you need to create the database on a different SQL Server instance first and then attach it when you run the container. This doesn't fit with an automated release process.

For my custom image, I want to do something different. The image contains the Dacpac, so it has everything it needs to deploy the database. When the container starts, I want it to check the data directory, and if it's empty, then I create a new database by deploying the Dacpac. If the database files already exist when the container starts, then attach the database files first and upgrade the database using the Dacpac.

This approach means you can use the same image to run a fresh database container for a new environment or upgrade an existing database container without losing any of its data. And this works just as well whether you mount the database directory from the host or not, so you can let the user choose how to manage the container storage, and my image supports many different scenarios.

The logic to do that is all in the Initialize-Database.ps1 PowerShell script, which the Dockerfile sets as the entry point for containers. In the Dockerfile, I pass the data directory to the PowerShell script in the data_path variable, and the script checks whether the NerdDinner data (mdf) and log (ldf) files are in that directory:

$mdfPath = "$data_path\NerdDinner_Primary.mdf"
$ldfPath = "$data_path\NerdDinner_Primary.ldf"

# attach data files if they exist: 
if ((Test-Path $mdfPath) -eq $true) {
 $sqlcmd = "IF DB_ID('NerdDinner') IS NULL BEGIN CREATE DATABASE NerdDinner ON (FILENAME = N'$mdfPath')"   
 if ((Test-Path $ldfPath) -eq $true) {
   $sqlcmd = "$sqlcmd, (FILENAME = N'$ldfPath')"
 }
 $sqlcmd = "$sqlcmd FOR ATTACH; END"
 Invoke-Sqlcmd -Query $sqlcmd -ServerInstance ".\SQLEXPRESS"
}

This covers the scenario where a user runs a container with a volume mount, where the host directory has data files from a previous container. These files are attached, and the database is available in the new container. Next, the script uses the SqlPackage tool to generate a deployment script from the Dacpac. I know the SqlPackage tool exists and I know the path to it because it's built into the SQL Server Express base image:

$SqlPackagePath = 'C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\SqlPackage.exe'
& $SqlPackagePath `
 /sf:NerdDinner.Database.dacpac `
 /a:Script /op:deploy.sql /p:CommentOutSetVarDeclarations=true `
 /tsn:.\SQLEXPRESS /tdn:NerdDinner /tu:sa /tp:$sa_password

If the database directory was empty when the container started, there's no NerdDinner database on the container, and SqlPackage will generate a script with a set of CREATE statements to deploy the new database. If the database directory did contain files, then the existing database would have been attached. In that case, SqlPackage would generate a script with a set of ALTER and CREATE statements to bring the database in line with the Dacpac.

The deploy.sql script generated in this step will create the new schema or apply changes to the old schema to upgrade it. The final database schema will be the same in both cases.

Lastly, the PowerShell script executes the SQL script, passing in variables for the database name, file prefixes, and data paths:

$SqlCmdVars = "DatabaseName=NerdDinner", "DefaultFilePrefix=NerdDinner", "DefaultDataPath=$data_path", "DefaultLogPath=$data_path" 

Invoke-Sqlcmd -InputFile deploy.sql -Variable $SqlCmdVars -Verbose

After the SQL script runs, the database exists in the container with the schema modelled in the Dacpac, which was built from the SQL project in the builder stage of the Dockerfile. The database files are in the expected location with the expected names, so if this container is replaced with another one from the same image, the new container will find the existing database and attach it.