<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=76180&amp;fmt=gif">

RoundTower Blog

SQL Server, PowerShell, and XtremIO Snapshots Part 1


XtremIO is becoming a popular platform for SQL Server. It performs excellent and has a great space benefit for database copies when using XtremIO snapshots. I have had a few customers asking questions about scripting snapshots for SQL Server on XtremIO using PowerShell. Most Windows administrators these days are using PowerShell and many SQL DBA’s are also starting to use it. I decided to setup EMC AppSync with XtremIO in our lab, do a little testing, and create some PowerShell scripts to help our customers get started. My plan was to test two different scenarios, one for crash consistent and one for application consistent snapshots with EMC AppSync, but EMC announced a new native VSS provider with XtremIO 4.0 so now there will also be another way to do application consistent snapshots when 4.0 is released. This post will cover creating a crash consistent copy of a SQL database volume using XtremIO snapshots and mounting the snapshot to create a secondary QA database. This method will use the XtremIO REST API to create a snapshot of the source volume. PowerShell will be used to execute the required steps on XtremIO, VMware, and Windows. 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. Here is the basic logical flow of the process. 063015_2232_SQLServerSn2 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:!PasswordsVCenter01.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 'LABvcuser' -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:!PasswordsXtremIO.txt" -baseuri ""   # Define a few constants for this operation $SnapPrefix = 'SQLVol01_SNAP_' $SourceVolume = 'SQLVol01' $TargetDatabaseName = 'SnapTest01_QA' $TargetHardDiskName = 'Hard Disk 6' $VMName = 'Server01' $MountPath = "E:" $DiskNumber = 6 $PartitionNumber = 1 $TargetSearchString = 'esx*'  $datapath = "E:SnapTest01.mdf" $logpath = "E:SnapTest01_log.ldf" 

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. We will also do a couple of rescans in the process and remove the old 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 -HardDisk (Get-HardDisk -VM $VMName -Name $TargetHardDiskName) -DeletePermanently -Confirm  # 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  # 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 }   # Set permissions 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 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. This completes the database refresh. I hope someone finds this helpful. Next post will introduce AppSync into the mix for application consistency and some additional benefits. MTSXtremIO Module NTFSSecurity Module MTSAuthentication Module

Share this Post:
« What I Really Liked at Citrix Synergy 2015
SQL Server, PowerShell, and XtremIO Snapshots Part 2 (EMC AppSync) »