dbatools and docker (updated!)

[potatoqualitee]

Years ago, I wrote an article about dbatools and Docker at dbatools.io/docker and enough has changed with our toolset to update not only the article but our images on Docker Hub as well! Nowadays, we have far more support for Linux and Microsoft even released SQL Server Edge which works on ARM64 (Apple M1/Raspberry Pi).

Docker Hub is a cloud-based repository in which Docker users and partners create, test, store and distribute container images.

I’ve long wanted to create containers help dbatools users easily access a non-production environment to test commands and safely explore our toolset. I finally made it a priority because I needed to ensure some Availability Group commands I was creating worked on Docker, too, and having some clean images permanently available was required. Also, in general, Docker is a just a good thing to know for both automation and career opportunities 😁

Getting started

To get started, first you have to install Docker, which is available for Windows, Linux and macOS.

Windows
Linux
macOS

Then grab two images from the dbatools repo.

# get the base images
docker pull dbatools/sqlinstance
docker pull dbatools/sqlinstance2

The first image will take a bit to download, but the second one will be faster because it’s based on the first!

The first instance is stacked with a bunch of objects, and the second one has a few basics to enable Availability Groups. The images are based off of Microsoft’s SQL Server 2019 Docker image if you're using x64 or SQL Edge if you're using ARM.

SQL Server for ARM does not support HA, so if you are pulling this image on an Apple M1 or a Pi, you
won't be able to test the New-DbaAvailabilityGroup command, though Start-DbaMigration still works.

I also added the following to make test migrations more interesting:

• Certificates
• Databases
• Endpoints
• Jobs
• Logins
• Server Roles
• And more

Here’s a visible sampling:

Nice and familiar! You may also notice that sa is disabled. Within the image, I disabled the sa account and created another account with sysadmin called sqladmin. The password, as noted below, is dbatools.IO

Creating containers

To setup the containers, just copy and paste the commands below into a PowerShell console. The first one sets up a shared network and the second one sets up the SQL Servers and exposes the required database engine. It also names them mssql1 and mssql2.

# create a shared network
docker network create localnet

# Expose engines and setup shared path for migrations
docker run -p 1433:1433  --volume shared:/shared:z --name mssql1 --hostname mssql1 --network localnet -d dbatools/sqlinstance
docker run -p 14333:1433 --volume shared:/shared:z --name mssql2 --hostname mssql2 --network localnet -d dbatools/sqlinstance2

Then it should appear two containers in Docker Destkop.

You can now manage each container by using the container's controls in the GUI or using the docker command line.

Time to play 🎉

Now we are setup to test commands against your two containers! You can login via SQL Server Management Studio or Azure Data Studio if you’d like to take a look first. The server name is localhost for the first instance and localhost,14333 for the second instance), the username is sqladmin and the password is dbatools.IO

Note that Windows-based commands (and commands relating to SQL Configuration Manager) will not work because the image is based on SQL Server for Linux. If you’d like to test Windows-based commands such as Get-DbaDiskSpace, consider testing them on your workstation if you’re running Windows.

Set up an Availability Group

Next, we’ll setup a sample availability group. Note that since it’s referring to “localhost”, you’ll want to execute this on the computer running Docker. If you’d like to run Docker on one machine and execute the code on another machine, that is possible but out of scope for this post.

# the password is dbatools.IO
$cred = Get-Credential sqladmin

# setup a powershell splat
$params = @{
    Primary = "localhost"
    PrimarySqlCredential = $cred
    Secondary = "localhost:14333"
    SecondarySqlCredential = $cred
    Name = "test-ag"
    Database = "pubs"
    ClusterType = "None"
    SeedingMode = "Automatic"
    FailoverMode = "Manual"
    Confirm = $false
 }

# execute the command
 New-DbaAvailabilityGroup @params

PowerShell output

SQL Server Management Studio

Beautiful 😍!

Performing an export

From the machine running the Docker containers, run the code below. You may note that linked servers and credentials are excluded from the export. This is because we use Windows-only functionality to export the passwords.

# the password is dbatools.IO
$cred = Get-Credential sqladmin

# First, backup the databases because backup/restore T-SQL code is what's exported
Backup-DbaDatabase -SqlInstance localhost -SqlCredential $cred -BackupDirectory /tmp

# Next, perform export 
Export-DbaInstance -SqlInstance localhost -SqlCredential $cred -Exclude LinkedServers, Credentials

And this is what it'll look like connecting from your Windows machine running dbatools, to your container running SQL Server on Linux.

Whaaaat! Now imagine doing this for all of your servers in your entire estate. Want to know more? Check out simplifying disaster recovery using dbatools which covers this topic in-depth.

Performing a migration

Amazingly, you can migrate an entire instance between two Linux servers. Pretty much everything is supported except for linked servers, credentials and backup devices, as all of them use Windows functionality under the hood for the migrations. In general, however, Linked Servers, Credentials and Backup Devices are supported by SQL Server on Linux, we just can't migrate them.

# if you haven't set your credential already, set it
if (-not $cred) {
    $cred = Get-Credential sqladmin
}

$params = @{
    Source = "localhost"
    SourceSqlCredential = $cred
    Destination = "localhost:14333"
    DestinationSqlCredential = $cred
    BackupRestore = $true
    SharedPath = "/shared"
    Exclude = "LinkedServers", "Credentials", "BackupDevices"
    Force = $true
}

Start-DbaMigration @params | Out-GridView

Cleaning up

To stop and remove a container (and start over if you’d like! I do tons of times per day), run the following commands.

docker rm -vf mssql1 mssql2

This does not delete the actual images, just their resulting containers. The -v removes the shared volume that was created (supposedly, sometimes it does not) and the -f forces the containers to stop.

Our Docker repo

If you've read this far, you'll probably be interested to know that we've made the docker compose files available on GitHub. This repo will help understand how we built our images so that you can build your own if you are interested.

Resources

If you’d like to know more, the posts below are fantastic resources.

Andrew Pruski’s container series
Quickstart: Run SQL Server container images with Docker

If you’d like to understand how containers work with the CI/CD process, check out this video by Eric Kang, Senior Product Manager for SQL Server: Use SQL Server 2017 in Docker containers for your CI/CD process