Get started Bring yourself up to speed with our introductory content.

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:

docker run -d -p 1433:1433 -e SA_PASSWORD=myNewpa55w0rd -e ACCEPT_EULA=Y -v C:/temp/:C:/temp/ -e ATTACH_DBS="[{'dbName':AdventureWorks,'dbFiles':['C:\\temp\\AdventureWorks2014.mdf','C:\\temp\\ AdventureWorks2014_log. ldf']}]" microsoft/mssql-server-windows-express

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

#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"]

This was last published in July 2018

Dig Deeper on Microsoft SQL Server Installation

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

Does your organization plan to put SQL Server databases in containers? Why or why not?
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close