I am working on my dbatools and dbachecks presentations for SQL Saturday Finland, SQLDays, SQL Saturday Cork and SQLGrillen I want to show the two modules running against a number of SQL Versions so I have installed
- 2 Domain Controllers
- 2 SQL 2017 instances on Windows 2016 with an Availability Group and WideWorldImporters database
- 1 Windows 2016 jump box with all the programmes I need
- 1 Windows 2016 with containers
using a VSTS build and this set of ARM templates and scripts
I wanted to create containers running SQL2017, SQL2016, SQL2014 and SQL2012 and restore versions of the AdventureWorks database onto each one.
Move Docker Location
I redirected my docker location from my C:\
drive to my E:\
drive so I didnt run out of space. I did this by creating a daemon.json
file in C:\ProgramData\docker\config
and adding
{"data-root": "E:\containers"}
and restarting the docker service which created folders like this
Then I ran
docker volume create SQLBackups
to create a volume to hold the backups that I could mount on the containers
AdventureWorks Backups
I downloaded all the AdventureWorks backups from GitHub and copied them to E:\containers\volumes\sqlbackups\_data
Get-ChildItem $Home\Downloads\AdventureWorks* | Copy-Item -Destination E:\containers\volumes\sqlbackups\_data
Getting the Images
To download the SQL 2017 image from the DockerHub I ran
docker pull microsoft/mssql-server-windows-developer:latest
and waited for it to download and extract
I also needed the images for other versions. My good friend Andrew Pruski b | t has versions available for us to use on his Docker Hub  so it is just a case of running
|
|
and waiting for those to download and extract (This can take a while!)
Create the containers
Creating the containers is as easy as
docker run -d -p ExposedPort:InternalPort --name NAME -v VolumeName:LocalFolder -e sa\_password=THEPASSWORD -e ACCEPT\_EULA=Y IMAGENAME
so all I needed to run to create 4 SQL containers one of each version was
|
|
and just a shade over 12 seconds later I have 4 SQL instances ready for me 🙂
Storing Credentials
This is not something I would do in a Production environment but I save my credentials using this method that Jaap Brasser b | t shared here
Get-Credential | Export-Clixml -Path $HOME\Documents\sa.cred
which means that I can get the credentials in my PowerShell session (as long as it is the same user that created the file) using
$cred = Import-Clixml $HOME\Documents\sa.cred
Restoring the databases
I restored all of the AdventureWorks databases that each instance will support onto each instance, so 2017 has all of them whilst 2012 only has the 2012 versions.
First I needed to get the filenames of the backup files into a variable
$filenames = (Get-ChildItem '\bearddockerhost\e$\containers\volumes\sqlbackups\_data').Name
and the container connection strings, which are the hostname and the port number
$containers = 'bearddockerhost,15789', 'bearddockerhost,15788', 'bearddockerhost,15787', 'bearddockerhost,15786'
then I can restore the databases using dbatools using a switch statement on the version which I get with the NameLevel property of Get-DbaSqlBuildReference
|
|
I need to create the file paths for each backup file by getting the correct backups and appending the names to C:\SQLBackups
which is where the volume is mounted inside the container
As Get-DbaDatabase gives the container ID as the Computer Name I have highlighted each container below
That is how easy it is to create a number of SQL containers of differing versions for your presentations or exploring needs
Happy Automating!