How to attach databases to custom SQL Server containers
Deploying SQL Server in Docker containers for production applications typically requires custom containers. Here are guidelines on how to attach databases to them.
Docker containers are revolutionizing the way applications are built. Increasingly, organizations are using them as a foundation for moving from legacy monolithic applications to compartmentalized service-based ones. That includes applications running on relational databases like SQL Server.
However, you need to use custom SQL Server containers for most production scenarios because the prebuilt container images that Microsoft makes available on the Docker Hub repository are only for the free SQL Server Express and Developer editions of the database software. You can build a custom container image using a Dockerfile along with the Windows Server Core image and SQL Server's unattended installation process. Once you've done that, the next step is to attach a database in SQL Server to the container so you can start running applications.
The following code uses the Docker Run command to attach the AdventureWorks2014 sample database to a container created with Microsoft's SQL Server Express image:
The -e switch in the command supplies values to two environment variables: ACCEPT_EULA, which governs whether Microsoft's software licensing terms should be accepted, and ATTACH_DBS. The latter includes a JSON string that specifies which databases will be attached to the Docker container, along with the paths to their data and log files.
Custom containers complicate things
Using the ATTACH_DBS variable works well for the prebuilt Microsoft container images. However, if you try to use it with your own images to attach a database in SQL Server to a container, you'll quickly find that the technique doesn't work because there's nothing in the custom containers to make it happen.
Microsoft injects a PowerShell script called start.ps1 into its SQL Server for Windows container images to enable them to start the SQL Server service and attach databases located in external volumes, which should be used to store and persist the data files separately from the SQL Server containers as part of stateful database applications.
Dockerfiles enable you to control and customize the creation of your own Docker images for SQL Server containers. They provide the ability to trigger the execution of commands like start.ps1 when a container starts by using the CMD directive.
Dockerfiles enable you to control and customize the creation of your own Docker images for SQL Server containers.
First, it's important to understand the difference between the Dockerfile RUN and CMD directives. RUN executes a command as the Dockerfile is building a container image and is commonly used to copy files into the image; there can be more than one RUN instruction in a Dockerfile. The CMD directive isn't executed during the build process. Instead, it supplies a command that will run automatically when a Docker container is started.
The following section of a sample Dockerfile illustrates how to use a CMD instruction to automatically run the Microsoft start.ps1 script when a SQL Server Docker container starts:
# Use CMD to run Microsoft's start.ps1 PowerShell script
The JSON string applied to the CMD directive essentially duplicates the one that Microsoft uses in its SQL Server Express and Developer container images for Windows. This same functionality can be used to customize the startup actions in your own SQL Server containers.
DIY approach to attach databases
While you could just use the start.ps1 script as is, it has several sections of code that aren't needed for production containers running SQL Server Standard or Enterprise. For example, the ACCEPT_EULA check isn't necessary because the Microsoft software license agreement is accepted as part of the installation process for the Standard and Enterprise editions.
The Dockerfile code sample that follows shows an updated and streamlined version of start.ps1 that I've customized to run SQL Server Standard.
# Modified start.ps1 script for SQL Server Standard edition
# The script optionally sets the sa password and starts the SQL Service
# It also optionally attaches databases from the disk
param(
[Parameter(Mandatory=$false)]
[string]$sa_password,
[Parameter(Mandatory=$false)]
[string]$attach_dbs
)
# start the service
Write-Verbose "Starting SQL Server"
start-service MSSQLSERVER
if($sa_password -ne "")
{
Write-Verbose "Changing SA login credentials"
$sqlcmd = "ALTER LOGIN sa with password=" +"'" + $sa_password + "'" + ";ALTER LOGIN sa ENABLE;"
$sqlcmd = "IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = '" + $($db.dbName) + "') BEGIN EXEC sp_detach_db [$($db.dbName)] END;CREATE DATABASE [$($db.dbName)] ON $($files) FOR ATTACH;"
Like Microsoft's original script, this version of it is capable of changing the login password for the default "sa" user account and attaching multiple SQL Server databases to a container. I eliminated the unnecessary ACCEPT_EULA check and added a couple Write-Verbose statements to assist with feedback and debugging.
Here's how to use a CMD directive to run this script in order to attach databases to your custom SQL Server containers and perform other actions during the container startup process.
# Use CMD to run custom start.ps1 PowerShell script
