I recently helped with a simple proof of concept to enable the ability to create a number of SQL Server databases without the need to have the MDF and LDF created from scratch each time. The use case is for developers on a weekly basis to create a clean database to load data for development and test.
This is a very straight forward test but helps to illustrate a way to alleviate unnecessary I/O to the FlashArray by leveraging FlashRecover Snapshots.
Test Configuration
- Windows Server 2012 R2 Update
- MPIO Best Practices
- SQL Server 2016 CTP2
- PowerShell 3.0
- SQL Server PowerShell
- Pure Storage FA-420 with 2×5.5TB shelves
- QLE2562 HBA, QD=64, 2 8 Gb/s paths
- One 1TB volume
Test Steps
The test script performs several different operations that are outlined below. As mentioned in the test configuration section there is a base volume already created 1TB in size for the data files to be created.
- Create directories
- Get start time for creating database
- Run T-SQL statement to create the FOOBAR database
- Get end time once database created
- Set PSCredential object to login into the FlashArray
- Create a connection to the FlashArray
- Snapshot the FOOBAR volume
- Create 25 new volumes from the snapshot FOOBAR.GOLD
- Pick a random # from 1-25 to select that volume
- Connect the volume from Step 9
- Register the volume from Step 10 on the host.
Test Scripts
Import-Module PureStoragePowerShell
Import-Module SQLPS -DisableNameChecking
$SQLInstance = “HYPERV-NODE2”
New-Item -Path “F:\Data” -ItemType Directory -ErrorAction Ignore
New-Item -Path “F:\Logs” -ItemType Directory -ErrorAction Ignore
$StartDb = Get-Date
$TSQL = @”
DROP DATABASE FOOBAR
GO
CREATE DATABASE FOOBAR
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’foobar’, FILENAME = N’F:\Data\FOOBAR_data.mdf’ , SIZE = 51200000KB , FILEGROWTH = 1048576KB )
LOG ON
( NAME = N’foobar_log’, FILENAME = N’F:\Logs\FOOBAR_logs.ldf’ , SIZE = 102400000KB , FILEGROWTH = 10%)
GO
“@
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance | Format-Table -Autosize
$EndDb = Get-Date
$TS1 = New-TimeSpan –Start $StartDb –End $EndDb
“Database created in {0:G}” -f $TS1
$StartSnaps = Get-Date
$Pwd = ConvertTo-SecureString “pureuser” -AsPlainText -Force
$Creds = New-Object System.Management.Automation.PSCredential (“pureuser”, $pwd)
$FAConnection = Get-PfaApiToken -FlashArray 10.21.8.17 -Credential $Creds | Connect-PfaController
New-PfaSnapshot -Volumes FOOBAR -Suffix GOLD -Session $FAConnection
ForEach($db in 1..25)
{
New-PfaVolume -Name FOOBAR$db -Source FOOBAR.GOLD -Session $FAConnection
}
$rnddb = Get-Random -Maximum 25 -Minimum 1
Connect-PfaHost -Name HYPERV-02 -Volume FOOBAR$rnddb -Session $FAConnection
$EndSnaps = Get-Date
$TS2 = New-TimeSpan –Start $StartSnaps –End $EndSnaps
“Snapshots & Volumes created in {0:G}” -f $TS2
$Return = Register-PfaHostVolumes -Computername HYPERV-NODE2
Results
The database creation time takes ~1m 29s for a 48.8GB data file (MDF) and 97.6GB log file (LDF). Instant File Initialization (IFI) has been enabled on SQL Server, unfortunately that does not work for the log file creation (dang it!). This test workload shows a latency of ~3.8ms while performing 1.1 GB/s writes with an average I/O size of 475KB and 2,300 IOPS. Database created in 0:00:01:29.4242595 and Snapshots & Volumes created in 0:00:00:06.0313964.
In the case of the proof of concept this exact workload needed to be run n times to create a number of SQL Server databases, my example uses 25. Although it is possible to perform that operation it doesn’t make much sense when we have FlashRecover Snapshots that can help by:
- No space consumption
- No unnecessary I/O
- Scale out to any # of volumes easily
- Refresh the volumes quickly using Restore Volume from Snapshot
The moral of the story, there are operations that may need to be approached differently and leverage the capabilities of Purity.
Thanks,
barkz