dbatools and docker

TSQL2SDAY-150x150
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.

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.

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

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.

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.

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.

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.

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.

Resources

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

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

4 thoughts on “dbatools and docker

  1. Pingback: A Docker-Based Sandbox For dbatools – Curated SQL

  2. Dan Reply

    Hi Chrissy, i followed your guide regarding docker, i have installed docker for windows and is now on the step “Time to Play” but how do you connect via SSMS?

    • Chrissy LeMaire Post authorReply

      Hey Dan, I just updated the wording under Time to Play that’ll hopefully clarify this: the server name is localhost (or localhost,14333 for the second instance), the username is sqladmin and the password is dbatools.IO

      Hope that helps!

  3. Dan Axstelius Reply

    Is it possible to fix this problem?
    Backup failed. Verify service account access to /sharedpath.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.