How to attach databases to custom SQL Server containers

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

#CMD without shell to run the start.ps1 script

CMD ["powershell -Command $ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue'; .\\start -sa_password $env:sa_password -ACCEPT_EULA $env:ACCEPT_EULA -attach_dbs $env:attach_dbs -Verbose"]

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 -Q $sqlcmd

}

else

{

    Write-Verbose "Not changing SA password"

}

if ($attach_dbs -ne "")

{ 

    $attach_dbs_cleaned = $attach_dbs.TrimStart('\\').TrimEnd('\\')

    $dbs = $attach_dbs_cleaned | ConvertFrom-Json

    if ($null -ne $dbs -And $dbs.Length -gt 0)

    {

        Foreach($db in $dbs)

        {

            Write-Verbose "Attaching: $($db.dbName)"

            $files = @();

            Foreach($file in $db.dbFiles)

            {

                $files += "(FILENAME = N'$($file)')";

            }

            $files = $files -join ","

            $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;"

            Write-Verbose "Invoke-Sqlcmd -Query $($sqlcmd)"

            & sqlcmd -Q $sqlcmd

            }

}

else

{

    Write-Verbose "Not attaching databases"

}

}

Write-Verbose "Started SQL Server."

$lastCheck = (Get-Date).AddSeconds(-2)

while ($true)

{

    Get-EventLog -LogName Application -Source "MSSQL*" -After $lastCheck | Select-Object TimeGenerated, EntryType, Message

    $lastCheck = Get-Date

    Start-Sleep -Seconds 2

}

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

#CMD without shell to run the start.ps1 script

CMD ["powershell -Command $ErrorActionPreference = 'Stop'; $ProgressPreference = 'SilentlyContinue'; .\\start -sa_password $env:sa_password -attach_dbs $env:attach_dbs -Verbose"]