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.
- A SQL_MASTER volume existing with the AdventureWorks2014 database. Download a copy of this database from https://msftdbprodsamples.codeplex.com/releases/view/125550.
- A new volume created named SQL_TEST based on SQL_MASTER source.
- 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
Does this script require the VM to have a direct connection (NPIV) to the LUN(s) on the Pure array? I’m guessing the disks are not VMDKs inside a datastore on a LUN attached to a VMware Host.
The demonstration here is using Raw Device Maps (RDMs) that can either be manually or programmatically connected using PowerCLI.