SQL Server 2014 Prod/Dev Refresh with VMware PowerCLI and Pure Storage PowerShell Toolkit

One of the demonstrations that I created for VMworld 2014 was to create a simple orchestration for refreshing a SQL Server 2014 database volume and re-connecting  to a VMware Guest rapidly and then querying the database. In the demonstration VMware PowerCLI, Pure Storage PowerShell Toolkit (REST API) and Windows PowerShell Extensions for SQL Server 2014 are used.

Here are two videos that shows how the orchestration works, first step-by-step and second one shows this running in real time.

How it works (step-by-step)

How it works (real time – 8 seconds)

Sample PowerShell

Import-Module C:\Windows\System32\WindowsPowerShell\v1.0\Modules\PureStorage\PureStorage.PowerShell.Toolkit.psd1 -WarningAction SilentlyContinue
cls

$Station = "DemoPod1"
$FlashArray1 = "0.0.0.1"
$vCenter = "1.1.1.1"
$vCenterUID = "USERNAME"
$vCenterPWD = "PASSWORD"
$vHostname = "VMHOST1"
$VMGuest2 = "GUEST1"
$SQLInstance = "2.2.2.2"

Import-Module SQLPS -DisableNameChecking 
# GET SAMPLE RESULTS
$TSQL = @"
USE [AdventureWorks2014]
GO
SELECT * FROM [Sales].[Currency]
GO
"@ 
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance | Format-Table -Autosize

# INSERT NEW RECORD
$TSQL = @"
USE [AdventureWorks2014]
GO
INSERT INTO [Sales].[Currency]([CurrencyCode],[Name]) VALUES ('PSM', 'PURE STORAGE MOOLA')
GO
SELECT * FROM [Sales].[Currency]
GO
"@ 
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance | Format-Table -Autosize

# DETACH DATABASE
$TSQL = @"
USE [master]
GO
sp_detach_db AdventureWorks2014
GO
"@
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance | Format-Table -Autosize

# OFFLINE DISK
$disks = Invoke-Command -Computername $SQLInstance {Get-Disk}
$i = 0
ForEach ($disk in $disks) {
    If ($disk.FriendlyName -like "PURE FlashArray*") {
        If ($disk.OperationalStatus -ne 0) { 
            $disknumber = $disk.Number
            $cmds = "`"SELECT DISK $disknumber`"",            
                    "`"OFFLINE DISK`""           
            $scriptblock = [string]::Join(",",$cmds)
            $diskpart = $ExecutionContext.InvokeCommand.NewScriptBlock("$scriptblock | DISKPART")         
            Invoke-Command -ComputerName $SQLInstance -ScriptBlock $diskpart
        }
    }
}

$MyToken = Get-PfaAPIToken -FlashArray $FlashArray1 -Username USERNAME -Password PASSWORD
$MySession = Connect-PfaController -FlashArray $FlashArray1 -APIToken $MyToken.api_token

Get-PfaVolume -FlashArray $FlashArray1 -Session $MySession | Where-Object { $_.name -like 'SQL_TEST' } 
# REFRESH VOLUME
Refresh-PfaVolume -FlashArray $FlashArray1 -Name SQL_TEST -Source SQL_MASTER -Session $MySession

# ONLINE DISK
$disks = Invoke-Command -Computername $SQLInstance {Get-Disk}
$i = 0
ForEach ($disk in $disks) {
    If ($disk.FriendlyName -like "PURE FlashArray*") {
        If ($disk.OperationalStatus -ne 1) { 
            $disknumber = $disk.Number
            $cmds = "`"SELECT DISK $disknumber`"",            
                    "`"ONLINE DISK`""           
            $scriptblock = [string]::Join(",",$cmds)
            $diskpart = $ExecutionContext.InvokeCommand.NewScriptBlock("$scriptblock | DISKPART")         
            Invoke-Command -ComputerName $SQLInstance -ScriptBlock $diskpart
        }
    }
}

# ATTACH DATABASE
$TSQL = @"
USE [master]
GO
sp_attach_db @dbname = N'AdventureWorks2014', 
    @filename1 = N'E:\AdvWorks\AdventureWorks.mdf', 
    @filename2 = N'E:\AdvWorks\AdventureWorks_Log.ldf' 
GO
"@ 
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance | Format-Table -Autosize

# ISSUE TEST QUERY TO SHOW NEW RESULTS
$TSQL = @"
USE [AdventureWorks2014]
GO
SELECT * FROM [Sales].[Currency]
GO
"@ 
Invoke-Sqlcmd $TSQL -QueryTimeout 3600 -ServerInstance $SQLInstance | Format-Table -Autosize

There are improvements that can be made with the above PowerShell but this was created as a demonstration to envision what can be accomplished using Pure Storage FlashRecover capabilities and our REST API integration with PowerShell.

The example above assumes a few things.

1. A SQL_MASTER volume existing with the AdventureWorks2014 database. Download a copy of this database from https://msftdbprodsamples.codeplex.com/releases/view/125550.
2. A new volume created named SQL_TEST based on SQL_MASTER source.
3. You have SQL Server PowerShell installed, if you need it download the following Microsoft SQL Server 2014 Feature  Pack files (ENU\x64) and install in this order:

• Microsoft System CLR Types for Microsoft SQL Server 2014 (SQLSysClrTypes.msi)
• Microsoft SQL Server 2014 Shared Management Objects (SharedManagementObjects.msi)
• Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2014 (PowerShellTools.msi)

This demonstration example uses one of the new cmdlet functions Refresh-PfaVolume to refresh a volume, see my post “Pure Storge PowerShell Toolkit Enhancements” for more details.

barkz