上QQ阅读APP看书，第一时间看更新

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 this 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 schema, 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 writeable 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 somewhere reliable, like 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 stored in the image, and if you mount a volume over the directory these files will be hidden.

The SQL Server images from Microsoft deal with this by letting you attach a 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 – it means you need to create the database on a different SQL Server instance first, and then attach it when you run the container. That 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 model. If the database files already exist when the container starts, then attach the database files first and upgrade the database using the Dacpac model.

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. 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, so the image supports many different scenarios.

The logic to do this 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 script looks complex, but actually, it's just building a CREATE DATABASE...FOR ATTACH statement, filling in the paths of the MDF data file and LDF log files if they exist. Then it invokes the SQL statement, which attaches the database files from the external volume as a new database in the SQL Server container.

This covers the scenario where a user runs a container with a volume mount, and the host directory already contains 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 packaged into my image from the builder stage:

$SqlPackagePath = 'C:\Program Files\Microsoft SQL Server\140\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 be 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"...
Invoke-Sqlcmd -InputFile deploy.sql -Variable $SqlCmdVars -Verbose

After the SQL script runs, the database exists in the container with the schema modeled 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.