HPE Nimble Storage Tech Blog
cancel
Showing results for 
Search instead for 
Did you mean: 

NOS4 Use Case: Rapid Deployment of SQL Developer Containers with Nimble Storage

dgonzalez59

Towards the end of February, Microsoft announced the general availability of SQL Server 2016 Developer Edition in Windows Containers. For most people, this announcement went unnoticed. For me, it was a moment of great excitement, and I feverishly set about the task of developing a demo environment. Gone were the database size limits placed upon me by the SQL Server Express images. I could now test workflows with larger datasets (greater than 10GB). Even more exciting, Nimble Storage had the tools, a combination of Nimble Windows Toolkit version 4 (NWT4) for Nimble OS4, and the Docker Volume Plugin for Windows Containers, available for me to get SQL Developer containers up and running in no time.

Building the Environment:

Getting up and running on Windows Containers is pretty well documented. I began by enabling the Containers feature on Server 2016, installing the Docker Volume Plugin for Windows Containers, and installing NWT4, as I knew I would leverage the new cmdlets and cloning enhancements for the workflows ahead. The next step was installing Docker and Docker-Compose, and I was off an running.

Installing Docker and Docker-Compose

# Install Docker #

Install-Module -Name DockerMsftProvider -Repository PSGallery -Force

Install-Package -Name docker -ProviderName DockerMsftProvider

Restart-Computer -Force   

 

# Install Docker-Compose #

Invoke-WebRequest -UseBasicParsing -Outfile $Env:ProgramFiles\docker\docker-compose.exe https://github.com/docker/compose/releases/download/1.11.2/docker-compose-Windows-x86_64.exe

 

Next, I installed the Docker PowerShell Module, and then I downloaded the SQL Server 2016 Developer container image. With that, I was ready to deploy my first Docker SQL Server Container.

Installing Docker PowerShell Module and Pulling SQL Server Developer Image

# Install Docker PowerShell #

Register-PSRepository -Name DockerPS-Dev -SourceLocation https://ci.appveyor.com/nuget/docker-powershell-dev

Install-Module Docker -Repository DockerPS-Dev

 

# Pull container image. Can use native commands "docker image pull <image name>" to download image, or "docker run <image name>" to download and install image. #

Pull-ContainerImage microsoft/mssql-server-windows-developer -Verbose

Humble Beginnings:

Before doing anything fancy, I decided to deploy my first container using a manual, and simple process. First, I used the Docker Volume Plugin to clone and import the volumes for an existing database I already had running, and then I created my SQL Server Developer container using the "docker run" command syntax. In order to pass the appropriate information to the Docker Volume Plugin, I leveraged NWT4 cmdlets to query available snapshot collections for my existing database volumes. By using NWT4 as part of my toolset, I was able to build my query based on application metadata, that is stored as part of the application consistent snapshot of my SQL database.

The whole process from start to finish took less than two minutes!!! That's right, in less than two minutes, I had an entire SQL instance up and running, with a complete clone of the production database attached and ready for operations. Creating another one took another hot minute, and before you knew it, I had 10 SQL instances running on my Windows Server, each with their own version of the cloned production database. Excessive? Maybe, but I just couldn't help myself.

Creating the SQL Server Developer Container

# Using NWT4 to get SnapColl information #

Get-NimSnapshotCollection -AppObject "\SQL03\virtdb1" -MaxObjects 1 | select -ExpandProperty Snapshots | select SnapshotCollectionName

 

SnapshotCollectionName

InitialSnap

InitialSnap

 

# Leverage Docker Volume Plugin to clone volumes from last snapshot collection and import into Docker #

docker volume create svr3s-udb-iscsi-array1 -d nimble -o importVolAsClone=exch2016-svr2s-udb-iscsi -o snapshot=InitialSnap -o forceImport 

docker volume create svr3s-tl-iscsi-array1 -d nimble -o importVolAsClone=exch2016-svr3s-tl-iscsi -o snapshot=InitialSnap -o forceImport

 

# Option 1: Create Docker Container, bind Nimble Volumes #

docker run --name sql40 -it --rm -v svr3s-tl-iscsi-array1:c:\sqldata\tl\svr3s-tl-iscsi-array1 -v svr3s-udb-iscsi-array1:c:\sqldata\udb\svr3s-udb-iscsi-array1 --network=BridgeNetwork --ip=10.10.10.10 -e sa_password=XXXXXXXX -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

# Option 2: Create Docker Container, bind Nimble Volumes, and automatically attach SQL Database using image attach_dbs option #

docker run --name sql40 -d -v svr3s-udb-iscsi-array1:c:\sqldata\udb\svr3s-udb-iscsi-array1 -v svr3s-tl-iscsi-array1:c:\sqldata\tl\svr3s-tl-iscsi-array1 -e attach_dbs="[{'dbName':'virtdb1_clone','dbFiles':['c:\\sqldata\\udb\\svr3s-udb-iscsi-array1\\MSSQL\\DATA\\virtdb1.mdf','c:\\sqldata\\tl\\svr3s-tl-iscsi-array1\\MSSQL\\DATA\\virtdb1.ldf']}]" --network=BridgeNetwork --ip=10.10.10.10 -e sa_password=XXXXXXX -e ACCEPT_EULA=Y microsoft/mssql-server-windows-developer

 

I Feel Some Automation Coming:

So now that I had my first SQL Server container, I started thinking, there has to be a way to automate this, right? What if I needed to create multiple copies of a production SQL database for a team of developers? What if developers needed to refresh the dataset periodically? Is there any way to make the cloning/refreshing of data into more of a self service workflow? All of these things are possible by leveraging NWT4 with the Docker Volume Plugin.

I knew I could get the latest snapshot based on application metadata. But, for this workflow to be complete, I needed to be able to get path information for SQL Server database files, so that I could construct the appropriate "attach_dbs" string and automatically mount my cloned SQL Server database to the container as part of the build. So I broke the workflow down into four parts:

(1) Leverage SQL Management Objects (SMO) to query SQL Server for database files and paths;

(2) Match the paths to the appropriate Nimble Storage Volume leveraging NWT4 cmdlets;

(3) Normalize the paths into the appropriate format for the "attach_dbs" option;

(4) Use the information gathered in the previous 3 steps to automatically build my SQL container.

I also wrapped the script in a function, with user supplied variables.

To make the entire workflow more appealing to the Docker crowd, with some advice from Michael Mattson (check out Michael Mattsson's Blog) I also had the script create a Docker-Compose file, and the whole project could then be built upon to make multi-container applications. I created a video to show three workflows with the new Docker-Compose automation:

(1) Cloning a SQL database to a container using a self-service workflow;

(2) Refreshing SQL container data;

(3) Lift and shift of a SQL Server database to a container.

We demoed the video a few weeks ago at DockerCon 2017 in Austin, TX, and it was quite a hit!  Here is a link to the video on YouTube: YouTube

Useful Links:
Tech Preview: Windows Containers Docker Volume Plugin: Tech Preview: Windows Containers Docker Volume plugin

The "Power" of PowerShell Cmdlets in Nimble Windows Toolkit 4: The “Power” of PowerShell cmdlets in Nimble Windows Toolkit 4.0

Nimble Connect: Docker : Docker

About the Author

dgonzalez59

Comments
fgrahn80

If I were your manager, I'd give you a promotion or raise...Well written document!