dbatools with SQL on Docker and running SQL queries

I had a question from my good friend Andrew Pruski dbafromthecold on twitter or SQL Container Man as I call him 🙂

How do you guys run SQL Commands in dbatools

I will answer that at the bottom of this post, but during our discussion Andrew said he wanted to show the version of the SQL running in the Docker Container.

Thats easy I said. Here’s how to do it

You need to have installed Docker first see this page You can switch to using Windows containers right-clicking on the icon in the taskbar and choosing the command. If you have not already, then pull the SQL 2017 SQL image using

docker pull microsoft/mssql-server-windows-developer:latest

This may take a while to download and extract the image but its worth it, you will be able to spin up new SQL instances in no time

You can create a new SQL Docker container like this

docker run -d -p 15789:1433 --env ACCEPT_EULA=Y --env sa_password=SQL2017Password01 --name SQL2017 microsoft/mssql-server-windows-developer:latest

In only a few seconds you have a SQL 2017 instance up and running (Take a look at Andrews blog at dbafromthecold.com for a great container series with much greater detail)

Now that we have our container we need to connect to it. We need to gather the IPAddress. We can do this using docker command docker inspect but I like to make things a little more programmatical. This works for my Windows 10 machine for Windows SQL Containers. There are some errors with other machines it appears but there is an alternative below

$inspect = docker inspect SQL2017
<#
IPAddress": matches the characters IPAddress": literally (case sensitive)
\s matches any whitespace character (equal to [\r\n\t\f\v ])
" matches the character " literally (case sensitive)
1st Capturing Group (\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})
\d{1,3} matches a digit (equal to [0-9])
. matches any character (except for line terminators)
\d{1,3} matches a digit (equal to [0-9])
. matches any character (except for line terminators)
\d{1,3} matches a digit (equal to [0-9])
. matches any character (except for line terminators)
\d{1,3} matches a digit (equal to [0-9])
#>
$IpAddress = [regex]::matches($inspect,"IPAddress`":\s`"(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})").groups[1].value

Those two lines of code (and several lines of comments) puts the results of the docker inspect command into a variable and then uses regex to pull out the IP Address

If you are getting errors with that you can also use

$IPAddress =docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' containername

Thanks Andrew 🙂

Now we just need our credentials to connect to the instance

$cred = Get-Credential -UserName SA -Message "Enter SA Password Here"

and we can connect to our SQL container

$srv = Connect-DbaInstance -SqlInstance $IpAddress -Credential $cred

and get the version

$srv.Version

and many many other properties, just run

$srv | Get-Member

to see them. At the bottom, you will see a ScriptMethod called Query, which means that you can do things like

$Query = @"
SELECT @@Version
"@

$srv.Query($Query)

$srv.Query($Query).column1

Which looks like

It’s slightly different with a Linux SQL container. Switch Docker to run Linux containers by right-clicking on the icon in the taskbar and choosing the command to switch.
If you haven’t already pull the Linux SQL image
docker pull microsoft/mssql-server-linux:2017-latest

and then create a container

docker run -d -p 15789:1433 --env ACCEPT_EULA=Y --env SA_PASSWORD=SQL2017Password01 --name linuxcontainer microsoft/mssql-server-linux:2017-latest

Now we just need to connect with localhost and the port number which we have specified already and we can connect again

$LinuxSQL = 'Localhost,15789'
$linuxsrv = Connect-DbaInstance -SqlInstance $LinuxSQL -Credential $cred
$linuxsrv.Version
$linuxsrv.HostDistribution
$linuxsrv.Query($query).column1

Of course, this isn’t restricted just Connect-DbaInstance you can do this with any dbatools commands

Get-DbaDatabase -SqlInstance $LinuxSQL -SqlCredential $cred

Go and explore your Docker SQL conatiners with dbatools 🙂

You can get it using

Install-Module dbatools

and find commands with

Find-DbaCommand database

Don’t forget to use Get-Help with the name of the command to get information about how to use it

Get-Help Find-DbaCommand -detailed

Enjoy 🙂

3 thoughts on “dbatools with SQL on Docker and running SQL queries

  1. Pingback: SQL On Docker And Running SQL Queries – Curated SQL

  2. Hi Rob,

    Thanks for this article.

    Sorry for my ignorance but I have very little knowledge about Docker and its capabilities but I wanted to know whether all your SQL instances were kept in containers? Do you do this for all the different SQL versions across 2000-2017? Or are you using Hyper-V/ VMWare in combinations with Docker?

    • Hi Parvinder, You should check out dbafromthecold for the best intro to using SQL on Docker. He has created images for several SQL versions. I use all sorts depending on the requirement

Leave a Reply to SQLDBAwithTheBeard Cancel reply

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