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
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 🙂
Pingback: SQL On Docker And Running SQL Queries – Curated SQL
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