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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# 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