Rapid Provisioning of SQL Server Test Environments with Nutanix

I’ve been doing some work recently with SQL Server and Nutanix Acropolis Hypervisor on a older Nutanix NX3400 platform in my lab, as my other clusters are busy on other things. The testing involves cloning a number of SQL Server databases, running performance and functionality tests, and then destroying the VM’s and starting over again. The idea is to make one change each iteration and then be able to do comparisons between iterations (keeping a consistent control / baseline). Always being able to return to a consistent state for the Databases is important to have valid test data. To do this repeatedly and quickly using multiple VM’s would not be practical without automation. Similar sorts of scenarios could be used where multiple test environments need to be created rapidly and returned to a consistent state after each test run, such as with a large application project. With all of this being able to do as many tests as possible in a limited period of time is important. The more tests, the more results, the more conclusions, and in a project environment the more defects found earlier that get fixed and prevented from going into production and ending up as support tickets. So in this article I will provide the scripts I used to automate this process, and demonstrate just how rapid it is to clone 12 x 300GB+ databases for testing.

This NX3400 platform I’m using has 4 nodes, each node has 2 x SandyBridge 2.2GHz 8 Core Processors, 256GB RAM, a single 300GB SSD and 5 x 1TB HDD’s. It’s a couple of generations old now, and has long since been replaced by newer and more powerful platforms.  But it’s good enough for the tests I’m doing. Here is a screenshot of the cluster:

As you can see the cluster is running Nutanix Acropolis Hypervisor (KVM), and at the time of the test High Availability was not enabled, hence it is in yellow. The cluster is running Nutanix OS 4.1.4.

The Nutanix Acropolis Hypervisor is based on KVM, but has been customized to meet the unique requirements of a distributed linearly scaleable hyper-converged infrastructure platform, and to be as simple as possible to use. One of the major benefits for everyone from Nutanix Acropolis Hypervisor is that you don’t need to be a rocket scientists or computer science PhD to use it, whereas with standard KVM, that’s how it feels. Standard KVM has way to many options, knobs and ways to get things wrong. In my opinion that is why KVM has never been as successful as other hypervisors. Nutanix Acropolis Hypervisor changes all that, itis so easy my 8yr old Son can use it and create his own VM’s. Nutanix Acropolis Hypervisor is available in both the commercial Nutanix platforms from Starter Edition Licenses, as well as the free Community Supported Nutanix Community Edition, which can run on just about any hardware, even nested virtualization. While it doesn’t yet support all of the features customers may have in other hypervisors, it is good for many use cases, and especially for applications where they have built in availability and are natively written to scale out and be highly available (including the likes of SQL Server, Exchange, Oracle and Oracle RAC).

Here is the script I used to rapidly provision 12 SQL Server Databases, which is executed from one of the Nutanix Controller VM’s:

sql_clone.sh:

#!/bin/bash

template="SQLW2K8R2-Template"
# To use batch provisioning uncomment startnum and endnum arrays and modify the values
#startnum=("11" "101" "201" "301")
#endnum=("100" "200" "300" "410")
startnum=1
endnum=12
vm_name="SQLW2K8R2AHV"
batches=${#startnum[@]}
startt=`date +%Y%m%d%H%M%S`

if [ -f vmclone.log ] ; then
	rm vmclone.log
fi

for i in `seq 0 $((batches - 1))`
do
echo "Starting batch $((i + 1)) from ${startnum[$i]} to ${endnum[$i]}..."
  DATETIME[0]=`date +%Y%m%d%H%M%S`
  echo "Creating $vm_name from clone..."
  echo "Cloning $template batch $((i + 1)) of $batches started at ${DATETIME[0]}..."
  echo "Cloning $template batch $((i + 1)) of $batches started at ${DATETIME[0]}..." >> vmclone.log
  completedtasks=`acli vm.clone $(seq -w ${startnum[$i]} ${endnum[$i]} | sed "s/^/$vm_name/" | xargs echo | tr " " ,) clone_from_vm=$template | grep complete | wc -l`
  echo "Number of provisioning tasks completed = $completedtasks" >> vmclone.log
  echo "Number of provisioning tasks completed = $completedtasks"
  DATETIME[1]=`date +%Y%m%d%H%M%S`
  echo "Cloning $template batch $((i + 1)) of $batches finished at ${DATETIME[1]}..."
  echo "Cloning $template batch $((i + 1)) of $batches finished at ${DATETIME[1]}..." >> vmclone.log
  echo "Cloning $template batch $((i + 1)) of $batches took $(( ${DATETIME[1]} - ${DATETIME[0]} )) seconds..." >> vmclone.log
  echo "Cloning $template batch $((i + 1)) of $batches took $(( ${DATETIME[1]} - ${DATETIME[0]} )) seconds..."
  echo "Powering on $vm_name batch $((i + 1)) of $batches..."
  completedtasks=`acli vm.on $(seq -w ${startnum[$i]} ${endnum[$i]} | sed "s/^/$vm_name/" | xargs echo | tr " " ,) | grep complete | wc -l`
  echo "Number of Power on tasks completed = $completedtasks" >> vmclone.log
  echo "Number of Power on tasks completed = $completedtasks"
  DATETIME[2]=`date +%Y%m%d%H%M%S`
  echo "Powering on $vm_name batch $((i + 1)) of $batches finished at ${DATETIME[2]}..."
  echo "Powering on $vm_name batch $((i + 1)) of $batches finished at ${DATETIME[2]}..." >> vmclone.log
  echo "Powering on $vm_name batch $((i + 1)) of $batches took $(( ${DATETIME[2]} - ${DATETIME[1]} )) seconds..."
  echo "Powering on $vm_name batch $((i + 1)) of $batches took $(( ${DATETIME[2]} - ${DATETIME[1]} )) seconds..." >> vmclone.log
done
endt=`date +%Y%m%d%H%M%S`
echo "Provisioning operations complete!"
echo "Total time to provisioning and power on all batches took $(( $endt - $startt )) seconds..."
echo "Total time to provisioning and power on all batches took $(( $endt - $startt )) seconds..." >> vmclone.log

To remove the test databases after the test is complete I used this script and executed it from one of the Nutanix Controller VM’s:

remove_sqlvms.sh:

#!/bin/bash

  vm_name=SQLW2K8R2AHV
  DATETIME=`date +%Y%m%d%H%M%S`
  echo "Removing $vm_name started at $DATETIME..."
  acli vm.off $vm_name\*
  DATETIME=`date +%Y%m%d%H%M%S`
  echo "Deleting $vm_name..."
  echo yes | acli vm.delete $vm_name\*
  DATETIME=`date +%Y%m%d%H%M%S`
  echo "Remove $vm_name finished at $DATETIME..."

Here is a video demonstrating what it looks like when I run the script. You can see the timing in the terminal window where I am connected into one of the Nutanix Controller VM’s running the sql_clone.sh script:

The exact same result can be achieved using the Nutanix REST API’s. In this example I have made use of the Acropolis CLI. The functions are supported equally across CLI, REST API, and within the Nutanix PRISM user interface (which leverages the Nutanix REST API’s). The entire process of provisioning the SQL Databases from template, powering them on, having them ready to run tests, from end to end, is completed in less than 90 seconds. As you can see from the video the provisioning and power on process is completed in less than 20 seconds.

It’s important to note that creating these 12 databases from template did not consume any additional storage. This is because the process leveraged the Nutanix smart clone and data avoidance features (no point duplicating data unnecessarily). Storage would only be consumed when new data is written. Storage consumption is further reduced by using Inline Compression (which also improves performance).

Each of the cloned databases is an exact replica of the master template. In these examples I didn’t customize the Windows OS for each clone, other than apply some tuning using a first boot script. Using PowerShell as part of the first boot script you could quite easily customize the OS and register with AD. For the tests I was performing I had set up the template to use a workgroup and not be part of an AD domain. Given I am rapidly creating and destroying these environments I didn’t want to have to register the VM’s and then delete the Computer Objects from AD afterwards. I wanted to treat these more as isolated test instances.  This entire process can also be automated as part of a service management or cloud management platform using the Nutanix REST API.

If you want to run Microsoft apps for development and testing, or for production, no problem. The Nutanix Acropolis Hypervisor is supported by Microsoft under the SVVP program. Other application vendors have their own support statements and requirements, so please check with your application vendor or get in touch with anyone at Nutanix to check. Nutanix has many different customers running many different applications on Acropolis Hypervisor, including Oracle RAC, Oracle EBS, OBIEE, Weblogic, SQL Server, Custom Java Apps, Hadoop, Splunk etc. Nutanix has a single customer with a total of > 1500 nodes running Acropolis Hypervisor.

https://twitter.com/andreleibovici/status/614512636936503298

Final Word

By using similar methods you could create rapid test and development environments for multiple projects to use in parallel. Complete end to end application stacks can be created in seconds to minutes, all without consuming any additional space. You can destroy environments and get back to a consistent state every single time. This allows projects to rapidly iterate and provide higher quality business outcomes in much less time. Every developer or tester could have their own environment of a completely integrated end to end application stack, that they can create, test, and then destroy and recreate on demand. On large projects this could literally save months of effort and millions of dollars. All of the infrastructure management is completely integrated into the solution and provides a distributed management framework without any single points of failure. The Nutanix PRISM user interface is the Nutanix Acropolis Hypervisor management interface, and REST API endpoint. The Nutanix solution is so easy to deploy, manage, and use, that you wouldn’t even know it’s based on KVM.

—

This post first appeared on the Long White Virtual Clouds blog at longwhiteclouds.com. By Michael Webster. Copyright © 2012 – 2015 – IT Solutions 2000 Ltd and Michael Webster. All rights reserved. Not to be reproduced for commercial purposes without written permission.