July | 2015 | David Muegge's Blog

During the writing of this post EMC announced the GA release of XtremIO 4.0 on July 2nd. The new documentation and the native VSS provider are now available on EMC support. This will provide the ability to script application consistent snapshots without using AppSync. Unfortunately we do not have our lab upgraded to 4.0 yet, but that will be coming soon and I will test the script and process soon. In the meantime I will talk about how this will provide another way to do application consistent snapshots for XtremIO. I will show the architecture and a mock script of how I think it will work at this point.

In order to use the XtremIO VSS provider it must be installed on the server where we want to do an application consistent snapshot. The install is downloaded from EMC support, at the time of this post the file name for the latest version is XtremIOVSSProvider-1.0.8.msi. After installation the connection to XtremIO is configured through the control panel using the applet.

The control panel applet is only to configure the connection to the XtremIO XMS.

The VSS Provider installation can be verified by opening a command line as administrator and typing vssadmin list providers. This shows us the XtremIO VSS Provider.

The process to use an XtremIO snapshot for a database copy using the VSS provider is very similar to the process used in part 1 of this blog series. The primary difference is the VSS Provider is called to create the XtremIO snapshot. The following image shows the basic VSS architecture.

The test environment is a SQL Server virtual machine on vSphere. The SnapTest01 volume is on a 50GB RDM on XtremIO and the SnapTest01_QA volume is a snapshot of the SnapTest01 volume.

The example script will show the process to refresh the QA volume with a new snapshot copy. The script is almost identical to the part 1 script. The first step is to load a few PowerShell modules, define some constants, and connect to VCenter and XtremIO. This is done by using the PowerCli and a function from my MTSXtremIO module, read about that here. This function uses the XtremIO REST API to create the snapshot. I also use a couple of other modules with some of my common functions and a NTFS Security Module which I did not write. I will put links to those at the end of the post.

Import-Module MTSXtremIO
Import-Module NTFSSecurity
Import-Module MTSAuthentication 
 
# Setup PowerCli and VCenter Connection
Set-Variable -Name vCenterUserPasswordFile -Value C:\!Passwords\VCenter01.txt
if(-not (get-pssnapin | Where-Object { $_.Name -eq 'VMware.VimAutomation.Core'})){add-pssnapin -Name VMware.VimAutomation.Core}
$vCenterUserPassword = Get-PasswordFromFile -FullPath $vCenterUserPasswordFile -AsSecureString
$VCCred = Set-WindowsAuthenticationCredential -userid 'LAB\vcuser' -password $vCenterUserPassword
$VCConnectResult = Connect-VIServer 'vcenter.lab.com' -Credential $VCCred
 
# Load SQL Server Management Objects
$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 
 
# XtremIO Connection
Disable-CertificateValidation
Set-XIOAPIConnectionInfo -username "admin" -passwordfile "C:\!Passwords\XtremIO.txt" -baseuri "https://192.168.1.100/api/json/types/"
 

# Define a few constants for this operation
$SnapPrefix = 'SQLVol01_SNAP_'
$SourceVolume = 'SQLVol01'
$TargetDatabaseName = 'SnapTest01_QA'
$TargetHardDiskName = 'Hard Disk 7'
$VMName = 'Server01'
$TargetSearchString = 'esx*'
$datapath = 'E:\SnapTest01.mdf'
$logpath = 'E:\SnapTest01_log.ldf'
$DiskNumber = 6
$PartitionNumber  = 1
$SnapSearchString = '_SNAP_*'
$MountPath = 'E:'
$ScriptName = 'SQLDShadow'
$MetadataFile = 'E:\SnapMeta'
$VerboseMode = 'ON'

Import-Module MTSXtremIO

Import-Module NTFSSecurity

Import-Module MTSAuthentication

# Setup PowerCli and VCenter Connection

Set-Variable -Name vCenterUserPasswordFile -Value C:\!Passwords\VCenter01.txt

if(-not (get-pssnapin | Where-Object { $_.Name -eq 'VMware.VimAutomation.Core'})){add-pssnapin -Name VMware.VimAutomation.Core}

$vCenterUserPassword = Get-PasswordFromFile -FullPath $vCenterUserPasswordFile -AsSecureString

$VCCred = Set-WindowsAuthenticationCredential -userid 'LAB\vcuser' -password $vCenterUserPassword

$VCConnectResult = Connect-VIServer 'vcenter.lab.com' -Credential $VCCred

# Load SQL Server Management Objects

$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

# XtremIO Connection

Disable-CertificateValidation

Set-XIOAPIConnectionInfo -username "admin" -passwordfile "C:\!Passwords\XtremIO.txt" -baseuri "https://192.168.1.100/api/json/types/"

# Define a few constants for this operation

$SnapPrefix = 'SQLVol01_SNAP_'

$SourceVolume = 'SQLVol01'

$TargetDatabaseName = 'SnapTest01_QA'

$TargetHardDiskName = 'Hard Disk 7'

$VMName = 'Server01'

$TargetSearchString = 'esx*'

$datapath = 'E:\SnapTest01.mdf'

$logpath = 'E:\SnapTest01_log.ldf'

$DiskNumber = 6

$PartitionNumber = 1

$SnapSearchString = '_SNAP_*'

$MountPath = 'E:'

$ScriptName = 'SQLDShadow'

$MetadataFile = 'E:\SnapMeta'

$VerboseMode = 'ON'

The example above loads module dependencies and connects to VCenter and XtremIO. The SQL Management Objects are loaded to provide SQL Server functionality.

The next step is to detach the current QA database copy, remove the virtual hard disk, and remove snapshots.

# Detach current copy of database
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
$srv.DetachDatabase($dbname,$False,$False)

# Remove Disk From VM
#Remove-HardDisk -DeletePermanently -Confirm $false
Remove-HardDisk -HardDisk (Get-HardDisk -VM $VMName -Name $TargetHardDiskName)

# Rescan Windows disks
Update-HostStorageCache

# Delete LUNMAP for existing snap
$LMResult = Get-XIOLunMap | Where-Object vol-name -like ($SourceVolume + $SnapSearchString) | Foreach-object{Remove-XIOLunMap -Name $_.'mapping-id'[1]}

# Rescan VMware HBA's
$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Remove last snapshots
$SPResult = Get-XIOSnapshot | Where-Object Name -like ($SourceVolume + $SnapSearchString) | ForEach-Object {Remove-XIOSnapshot -VolName $_.Name}

# Detach current copy of database

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)

$srv.DetachDatabase($dbname,$False,$False)

# Remove Disk From VM

#Remove-HardDisk -DeletePermanently -Confirm $false

Remove-HardDisk -HardDisk (Get-HardDisk -VM $VMName -Name $TargetHardDiskName)

# Rescan Windows disks

Update-HostStorageCache

# Delete LUNMAP for existing snap

$LMResult = Get-XIOLunMap | Where-Object vol-name -like ($SourceVolume + $SnapSearchString) | Foreach-object{Remove-XIOLunMap -Name $_.'mapping-id'[1]}

# Rescan VMware HBA's

$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Remove last snapshots

$SPResult = Get-XIOSnapshot | Where-Object Name -like ($SourceVolume + $SnapSearchString) | ForEach-Object {Remove-XIOSnapshot -VolName $_.Name}

The example above uses SQL Management Objects to access SQL and detach the database. It the uses the VMware PowerCli to remove the RDM from the virtual machine. Then connects to the XtremIO via REST API and deletes snapshots

Now we are ready to create a new snapshot, add it to the lunmap, add the disk to the vm, and attach the database.

# Create snapshot of source volume
$Snapname = ($SnapPrefix + (Get-Date -Format yyyyMMdd-HHmmss))
$SNResult = New-XIOSnapshot -VolName $SourceVolume -SnapName $Snapname -FolderID $SnapFolder

# Application Consistent Method - XtremIO VSS Provider
$dsh = "./$ScriptName.dsh"
'RESET',
'SET CONTEXT PERSISTENT',
'SET OPTION TRANSPORTABLE',
"SET METADATA $MetadataFile",
"SET VERBOSE $VerboseMode",
'BEGIN BACKUP',
"ADD VOLUME $Volume ALIAS $ShadowCopyAlias PROVIDER {b57190af-454a-4dd0-8afd-e57facd5d9af}",
'CREATE',
'END BACKUP' | Set-Content $dsh
DISKSHADOW /s $dsh
Remove-Item $dsh

# Create Lun Map for snap volume
$LMResult = Get-XIOInitiatorGroup | Where-Object Name -Like $TargetSearchString | Select-Object name,index | foreach-object{New-XIOLunMap -Name $Snapname -InitiatorGroup $_.index}

# Rescan VMware HBA's
$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Add Disk to VM
$vm = Get-VM -Name $VMName
$vmhost = Get-VMHost -Name $vm.VMHost
$deviceName = ($vmhost | Get-ScsiLun | Where-Object {$_.CanonicalName -match ('naa.' + (Get-XIOVolume | Where-Object name -EQ $Snapname).'naa-name')})[0].ConsoleDeviceName
$HDResult = New-HardDisk -DeviceName $deviceName -VM $vm -DiskType RawPhysical

# Rescan Windows
Update-HostStorageCache

# Set mount and drive path 
Set-Disk -Number $DiskNumber -IsReadOnly $false
Set-Disk -Number $DiskNumber -IsOffline $false
Start-Sleep -Seconds 1
if(( -not (get-partition -DiskNumber $DiskNumber).DriveLetter -eq $MountPath)){
	Add-PartitionAccessPath -DiskNumber $DiskNumber -PartitionNumber $PartitionNumber -AccessPath $MountPath
}
 
# Verify Files and Attach Database
Enable-NTFSAccessInheritance -Path $datapath
Enable-NTFSAccessInheritance -Path $logpath
if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

	# Attach Database
	$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
	$db = New-Object Microsoft.SqlServer.Management.Smo.Database
	$db = $srv.Databases.Item($dbname)
	$sc = new-object System.collections.specialized.stringcollection 
	$sc.Add($datapath)
	$sc.Add($logpath)
	$srv.AttachDatabase($dbname,$sc,[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

}

# Create snapshot of source volume

$Snapname = ($SnapPrefix + (Get-Date -Format yyyyMMdd-HHmmss))

$SNResult = New-XIOSnapshot -VolName $SourceVolume -SnapName $Snapname -FolderID $SnapFolder

# Application Consistent Method - XtremIO VSS Provider

$dsh = "./$ScriptName.dsh"

'RESET',

'SET CONTEXT PERSISTENT',

'SET OPTION TRANSPORTABLE',

"SET METADATA $MetadataFile",

"SET VERBOSE $VerboseMode",

'BEGIN BACKUP',

"ADD VOLUME $Volume ALIAS $ShadowCopyAlias PROVIDER {b57190af-454a-4dd0-8afd-e57facd5d9af}",

'CREATE',

'END BACKUP' | Set-Content $dsh

DISKSHADOW /s $dsh

Remove-Item $dsh

# Create Lun Map for snap volume

$LMResult = Get-XIOInitiatorGroup | Where-Object Name -Like $TargetSearchString | Select-Object name,index | foreach-object{New-XIOLunMap -Name $Snapname -InitiatorGroup $_.index}

# Rescan VMware HBA's

$CLResult = Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHBA

# Add Disk to VM

$vm = Get-VM -Name $VMName

$vmhost = Get-VMHost -Name $vm.VMHost

$deviceName = ($vmhost | Get-ScsiLun | Where-Object {$_.CanonicalName -match ('naa.' + (Get-XIOVolume | Where-Object name -EQ $Snapname).'naa-name')})[0].ConsoleDeviceName

$HDResult = New-HardDisk -DeviceName $deviceName -VM $vm -DiskType RawPhysical

# Rescan Windows

Update-HostStorageCache

# Set mount and drive path

Set-Disk -Number $DiskNumber -IsReadOnly $false

Set-Disk -Number $DiskNumber -IsOffline $false

Start-Sleep -Seconds 1

if(( -not (get-partition -DiskNumber $DiskNumber).DriveLetter -eq $MountPath)){

Add-PartitionAccessPath -DiskNumber $DiskNumber -PartitionNumber $PartitionNumber -AccessPath $MountPath

}

# Verify Files and Attach Database

Enable-NTFSAccessInheritance -Path $datapath

Enable-NTFSAccessInheritance -Path $logpath

if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

# Attach Database

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)

$db = New-Object Microsoft.SqlServer.Management.Smo.Database

$db = $srv.Databases.Item($dbname)

$sc = new-object System.collections.specialized.stringcollection

$sc.Add($datapath)

$sc.Add($logpath)

$srv.AttachDatabase($dbname,$sc,[Microsoft.SqlServer.Management.Smo.AttachOptions]::None)

}

The above example creates a snapshot via the VSS provider and maps the volume to the host using the XtremIO REST API. It also rescans the disks and then adds the RDM to the virtual machine. Then the database is attached using SQL SMO.

Although I have not been able to test this code it as we need to upgrade XtremIO first. I had hoped the VSS provider would work with 3.0 but unfortunately I received the following message when I tried it.

“The registered provider does not support shadow copy of this volume”

Hopefully this example should be pretty close.

Regards,

Dave

MTSXtremIO Module
NTFSSecurity Module
MTSAuthentication Module

In my last post I talked about using PowerShell to do crash consistent snapshots on XtremIO. Then mount the snapshot and use for a secondary SQL Server database copy.

In this post I will talk about scripting application consistent XtremIO snapshots using EMC AppSync and EMC Storage Integrator for Windows (ESI). I will also talk about how the process and architecture differs from part 1 where we just did crash consistent snapshots. EMC AppSync is a server application that can create and manage snapshots for application and virtualization environments using EMC storage arrays. It uses a Host plug-in which allows the AppSync server to control host operations. ESI is a windows application and set of tools which provides EMC management capabilities from a windows machine. One of the features of ESI is a PowerShell toolkit that allows controlling EMC arrays and EMC AppSync using PowerShell CmdLets.

We’ll use the same basic scenario as the last time, but utilizing EMC AppSync. The test environment is a SQL Server virtual machine on vSphere. The SnapTest01 volume is on a 50GB RDM on XtremIO and the SnapTest01_QA volume is a snapshot of the SnapTest01 volume.

With our previous test the script would generally run on the SQL Server to perform the snapshot operations. With the AppSync application the script does not have a requirement to run locally as the agent performs local operations. Here is a diagram of the architecture.

In this scenario the script would run on the ESI host using a windows account that has SQL admin rights on the SQL server. This is required to attach the database. The latest version of appsync as of this writing (2.2) does not have the ability to attach a copy from an XtremIO volume. Here is the statement from the admin guide on page 105.

“This version of Appsync does not support automated restore of copies on XtremIO. Learn

to use AppSync and XtremIO to restore SQL Server databases to a point in time with a

semi-manual restore process.”

However, this is easily worked around using PowerShell and SQL SMO to fill in the gap. The end script is still simpler than in part 1 because AppSync does a lot of the tedious work for us and we get application consistent snapshots to boot. The other benefit of the architecture is it allows you to use a central host to control all of your database refresh operations. Here is the first part of the script which loads the ESI module, SQL SMO, and defines some constants.

Import-Module ESIPSToolkit

# Load SQL Server Management Objects
$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") 

$SourceVolume = 'SQLVol01'
$SourceDatabase = 'SnapTest01'
$TargetDatabaseName = 'SnapTest01_QA'
$VMName = 'Server01'
$AppSYncServicePlan = 'sql bronze'
$MountPath = 'C:\AppSyncMounts\Server01'
$datapath = 'C:\AppSyncMounts\Server01\FDrive\SnapTest01.mdf'
$logpath = 'C:\AppSyncMounts\Server01\FDrive\SnapTest01_log.ldf'

Import-Module ESIPSToolkit

# Load SQL Server Management Objects

$LoadResult = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$SourceVolume = 'SQLVol01'

$SourceDatabase = 'SnapTest01'

$TargetDatabaseName = 'SnapTest01_QA'

$VMName = 'Server01'

$AppSYncServicePlan = 'sql bronze'

$MountPath = 'C:\AppSyncMounts\Server01'

$datapath = 'C:\AppSyncMounts\Server01\FDrive\SnapTest01.mdf'

$logpath = 'C:\AppSyncMounts\Server01\FDrive\SnapTest01_log.ldf'

The next section detaches the database, initializes the AppSync Objects, gets and dismounts the current snapshot.

# Detach current copy of database
$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($VMName)
$srv.DetachDatabase($TargetDatabaseName,$False,$False)

# Initialize AppSync Objects
$ReplService = Get-EmcReplicationService -ID emcappsync
$SvrInstance = Get-EmcAppSyncSqlServerInstance -AppSyncSystem $ReplService | Where-Object Name -eq $VMName
$DbInstance = Get-EmcAppSyncSqlServerDatabase -AppSyncSystem $ReplService -UserDatabase $SOurceDatabase
$SPlan = Get-EmcAppSyncServicePlan -AppSyncSystem $ReplService | Where-Object Name -eq $AppSYncServicePlan
$SQLServerHost = Get-EmcSqlServer -HostNamesOrIPAddresses $VMName
$AppSYncHost = Get-EmcAppSyncRegisteredHost -ID $VMName -AppSyncSystem $ReplService

# Unmount Current Copy
$DBCopy = Get-EmcAppSyncCopy -SqlServerDatabase $DbInstance | Where {(($_.CopyMountStatus -eq 'Mounted') -and ($_.Name -eq $SOurceDatabase))} | Sort -Desc | Select -First 1
Dismount-EmcAppSyncCopy -SqlServerDatabaseCopy $DBCopy -Silent -Confirm -Force

# Detach current copy of database

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server($VMName)

$srv.DetachDatabase($TargetDatabaseName,$False,$False)

# Initialize AppSync Objects

$ReplService = Get-EmcReplicationService -ID emcappsync

$SvrInstance = Get-EmcAppSyncSqlServerInstance -AppSyncSystem $ReplService | Where-Object Name -eq $VMName

$DbInstance = Get-EmcAppSyncSqlServerDatabase -AppSyncSystem $ReplService -UserDatabase $SOurceDatabase

$SPlan = Get-EmcAppSyncServicePlan -AppSyncSystem $ReplService | Where-Object Name -eq $AppSYncServicePlan

$SQLServerHost = Get-EmcSqlServer -HostNamesOrIPAddresses $VMName

$AppSYncHost = Get-EmcAppSyncRegisteredHost -ID $VMName -AppSyncSystem $ReplService

# Unmount Current Copy

$DBCopy = Get-EmcAppSyncCopy -SqlServerDatabase $DbInstance | Where {(($_.CopyMountStatus -eq 'Mounted') -and ($_.Name -eq $SOurceDatabase))} | Sort -Desc | Select -First 1

Dismount-EmcAppSyncCopy -SqlServerDatabaseCopy $DBCopy -Silent -Confirm -Force

The detach database operation uses SQL Server Management Objects and the AppSync objects are all created using the ESI PowerShell Toolkit CmdLets.

The next section creates a new snapshot mounts it to the host and attaches the database.

# Create snapshot of source volume/database
$DBCopy = New-EmcAppSyncSqlServerDatabaseCopy -SqlServerDatabase $DbInstance -ServicePlan $SPlan

# Mount snapshot of source volume/database
Mount-EmcAppSyncSqlServerDatabaseCopy -SqlServerDatabaseCopy $DBCopy -TargetHost $AppSYncHost -MountPath $MountPath

# Verify Files and attach database
if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

	# Attach Database
	$DBAttachResult = Set-DBAttach -dbname $TargetDatabaseName -datapath $datapath -logpath $logpath

}

# Create snapshot of source volume/database

$DBCopy = New-EmcAppSyncSqlServerDatabaseCopy -SqlServerDatabase $DbInstance -ServicePlan $SPlan

# Mount snapshot of source volume/database

Mount-EmcAppSyncSqlServerDatabaseCopy -SqlServerDatabaseCopy $DBCopy -TargetHost $AppSYncHost -MountPath $MountPath

# Verify Files and attach database

if ((Test-Path -Path $datapath) -and (Test-path -Path $logpath)){

# Attach Database

$DBAttachResult = Set-DBAttach -dbname $TargetDatabaseName -datapath $datapath -logpath $logpath

}

This also uses ESI for the AppSync operations and SQL SMO for the database attach.

An important point to note is we did not have to create steps for all of the individual disk operations and LUN mapping. The AppSync server and host agent took care of the details for us.

That is it for application consistent snapshots with AppSync. Next time I will talk about the method we will be able to use when XtremIO 4.0 gets here and the native VSS Provider is available.

Regards,

Dave

Monthly Archives for July 2015

SQL Server, PowerShell, and XtremIO Snapshots Part 3 (VSS Native)

SQL Server, PowerShell, and XtremIO Snapshots Part 2 (EMC AppSync)

Share this:

Share this: