dbatools and docker

[one_third][/one_third][two_third_last]Today's article is part of T-SQL Tuesday. T-SQL Tuesday is the brainchild of Adam Machanic. It is a blog party on the second Tuesday of each month. Everyone is welcome to participate.

This month’s T-SQL Tuesday, hosted by dbatools Major Contributor, Garry Bargsley ([b]|[t]), is all about automation.[/two_third_last]

Docker Hub

In his invitation, Garry asks "what automation are you proud of completing?" My answer is that I finally created a couple dbatools images and made them available on Docker Hub.

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 do this to help dbatools users easily create 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

First, install Docker.

• Windows
• Mac OS

Then grab two images from the dbatools repo. Note that these are Linux images.

1# get the base images
2docker pull dbatools/sqlinstance
3docker 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! Neat.

The first instance is stacked with a bunch of objects, and the second one has a few basics to enable Availability Groups. Both dbatools images are based off of Microsoft's SQL Server 2017 docker image.

I also added the following to make test migrations more interesting and Availability Groups possible:

• Databases
• Logins
• Jobs
• Endpoints
• 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. The first one sets up a shared network and the second one sets up the SQL Servers and exposes the required database engine and endpoint ports. It also names them dockersql1 and dockersql2 and gives them a hostname with the same name. I left in "docker" so that it doesn't conflict with any potential servers named sql1 on the network.

1# create a shared network
2docker network create localnet
3
4# setup two containers and expose ports
5docker run -p 1433:1433 -p 5022:5022 --network localnet --hostname dockersql1 --name dockersql1 -d dbatools/sqlinstance
6docker run -p 14333:1433 -p 5023:5023  --network localnet --hostname dockersql2 --name dockersql2 -d dbatools/sqlinstance2

Generally, you don't have to map the ports to exactly what they are running locally, but Availability Groups do a bit of port detection that require one-to-one mapping.

By the way, if you sometimes prefer a GUI like I do, check out Kitematic. It's not ultra-useful but it'll do.

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 (or 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 localhost if you're running Windows.

Set up an Availability Group

Next, we'll setup a sample availability groups. 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.

 1# the password is dbatools.IO
 2$cred = Get-Credential -UserName sqladmin
 3
 4# setup a powershell splat
 5$params = @{
 6    Primary = "localhost"
 7    PrimarySqlCredential = $cred
 8    Secondary = "localhost:14333"
 9    SecondarySqlCredential = $cred
10    Name = "test-ag"
11    Database = "pubs"
12    ClusterType = "None"
13    SeedingMode = "Automatic"
14    FailoverMode = "Manual"
15    Confirm = $false
16 }
17
18# execute the command
19 New-DbaAvailabilityGroup @params

PowerShell output

SQL Server Management Studio

Beautiful 😍!

Performing an export

Again, from the machine running the Docker containers, run the code below. You may note that linked servers, credentials and central management server are excluded from the export. This is because they aren't currently supported for various Windows-centric reasons.

1# the password is dbatools.IO
2$cred = Get-Credential -UserName sqladmin
3
4# First, backup the databases because backup/restore t-sql is what's exported
5Backup-DbaDatabase -SqlInstance localhost:1433 -SqlCredential $cred -BackupDirectory /tmp
6
7# Next, perform export (not currently supported on Core)
8Export-DbaInstance -SqlInstance localhost:1433 -SqlCredential $cred -Exclude LinkedServers, Credentials, CentralManagementServer, BackupDevices

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

This command requires a shared directory. Check out Shared Drives and Configuring Docker for Windows Volumes for more information. You may notice that this command does not support linked servers, credentials, central management server or backup devices.

1# the password is dbatools.IO
2$cred = Get-Credential -UserName sqladmin
3
4# perform the migration from one container to another
5Start-DbaMigration -Source localhost:1433 -Destination localhost:14333 -SourceSqlCredential $cred -DestinationSqlCredential $cred -BackupRestore -SharedPath /sharedpath -Exclude LinkedServers, Credentials, CentralManagementServer, BackupDevices -Force

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 or use Kitematic's GUI. This does not delete the actual images, just their resulting containers.

1docker stop dockersql1 dockersql2
2docker rm dockersql1 dockersql2

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.

Thanks for reading! Sorry about any typos or mistakes, I hastily wrote this while traveling back from vacation; I had to make Garry's T-SQL Tuesday!

- Chrissy