The power struggle between SQL DBAs and the Data Protection team is a tale as old as...well me probably. Let's not get too dramatic. The SQL team wants to protect their own databases to ensure simple and quick recovery, while the backup team wants to ensure those databases are truly being protected. In order for backup admins to take ownership of SQL backups though, two fundamental requirements must be met:
DBAs must be able to easily recover their databases
DBAs must be able to quickly recover their databases
Veeam v10 offers a capability that has been hovering under the radar that we are going to shine a bright light on. This capability provides SQL DBAs the ability to quickly recover databases off of storage snapshots, while the Veeam admin is the one protecting both the image and the databases. Before we go into detail on the magic of v10, it is important to understand what Veeam already offers for SQL protection and recovery.
Restoring SQL databases from storage array snapshots has been in the product for several years. It is as simple as right clicking on the VM and selecting SQL application recovery, but the restores are triggered by the Veeam admin. This functionality serves a purpose for many customers with HPE, Nimble, NetApp, Pure, EMC Unity/VNX, Infinidat and several other supported storage arrays, but for larger customers who require SQL DBAs to control the recoveries this isn't always a perfect match.
Despite not being able to control recoveries from snapshots, it is important to note that SQL DBAs certainly can control recoveries from backups on the disk repository. This gives DBAs control of the recoveries, while the Veeam admin handles SQL protection (including transaction logs at an interval of your choosing). Once the backups are setup, DBAs login to the Veeam web portal, select the server they want to restore from, choose the database and point the restore to the original or new location.
The backup team defines the scope and type of restores available to SQL DBAs by creating a role. The scope can be by vSphere tag, host, cluster or datastore for virtual machines. For physical machines the Veeam admin can define the scope by Protection Group. In the example below, when DBAs login to the web portal they'll be able to restore SQL databases from VMs that are tagged, "MS SQL."
Although this is a great option for many customers, it doesn't meet the restore performance some SQL DBAs are accustomed to. If you ask a DBA how long they can afford to be down they will say, "None," and look at you like you're from Mars for asking the question. The web portal certainly provides an easy way for DBAs to restore their databases, but it is restricted by the performance of the backup repositories which is typically spinning disk. DBAs are used to doing their dumps to all-flash arrays which offer rapid recoveries when you pull a .BAK file from there.
All of this leads to the exciting new capability in Veeam v10! SQL DBAs can now leverage storage array snapshots as rapid recovery points, when they kickoff database restores. This is made possible because not only can the Veeam admin backup the SQL image once a day, but also they can now create additional recovery points on the storage array along with the transaction logs at an interval of your choosing. This functionality works with HPE, Nimble, NetApp, Pure, EMC Unity/VNX, Infinidat and several other storage arrays. Although recovering databases from storage snapshots through the web portal did not make v10, we can provide the next best thing! A PowerShell script! The below script will publish a database to the SQL Server from the most recent storage array snapshot. This will give you a side-by-side comparison of your production database and the most recent point-in-time like in the screenshot below.
# Install remote Veeam console on machine this script will be run
# Add Veeam Snap-in
Add-PSSnapin VeeamPSSnapin
# Connect to Veeam Server
Connect-VBRServer -User "username" -Password "password" -Server "VBR-server-name"
# start the SQL Explorer
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "VM-name" | Sort-Object -Property CreationTime -Descending | Select-Object -First 1
Start-VESQLRestoreSession -RestorePoint $restorepoint
# Publish Database
$session = Get-VESQLRestoreSession
$database = Get-VESQLDatabase -Session $session[0] -Name "database-name"
Publish-VESQLDatabase -Database $database -ServerName "SQL-Server-name" -DatabaseName "new-name-of-published-db"
# Login to SQL Studio to see the published database
# Unpublish Database When Done
$session = Get-VESQLRestoreSession
$database = Get-VESQLPublishedDatabase -Session $session[0] -Name "name-of-published-database"
Unpublish-VESQLDatabase -Database $database
# Shutdown SQL Explorer
$session = Get-VESQLRestoreSession
Stop-VESQLRestoreSession -Session $sessionIn the Veeam console the admin can see an application item restore for SQL taking place. Also, we can confirm this is coming from a NetApp snapshot clone by the log messages, and that the database published successfully!
In addition, the below script will restore the database from the most recent storage snapshot rather than publish it. If the database already exists you will be given the option to overwrite it.
# Add Veeam Snap-in
Add-PSSnapin VeeamPSSnapin
# Connect to Veeam Server
Connect-VBRServer -User "username" -Password "password" -Server "VBR-server-name"
# start the SQL Explorer
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "VM-name" | Sort-Object -Property CreationTime -Descending | Select-Object -First 1
Start-VESQLRestoreSession -RestorePoint $restorepoint
# Restore Database
$session = Get-VESQLRestoreSession
$database = Get-VESQLDatabase -Session $session[0] -Name "database-name"
Restore-VESQLDatabase -Database $database -ServerName "SQL-Server-name"
# Shutdown SQL Explorer
$session = Get-VESQLRestoreSession
Stop-VESQLRestoreSession -Session $session
The restore was successful and we can confirm it came from a NetApp snapshot clone.
Lastly, exporting a .BAK file is a great option for SQL DBAs to have whether it is from the snapshot or backup file. Below is a script to do just that.
# Add Veeam Snap-in
Add-PSSnapin VeeamPSSnapin
# Connect to Veeam Server
Connect-VBRServer -User "username" -Password "password" -Server "VBR-server-name"
# start the SQL Explorer
$restorepoint = Get-VBRApplicationRestorePoint -SQL -Name "VM-name" | Sort-Object -Property CreationTime -Descending | Select-Object -First 1
Start-VESQLRestoreSession -RestorePoint $restorepoint
# Export .BAK File
$session = Get-VESQLRestoreSession
$database = Get-VESQLDatabase -Session $session[0] -Name "db-name"
Export-VESQLDatabase -Database $database -Path "C:\export\Export.bak" -Server "SQL Server" -ToBackupFile
# Shutdown SQL Explorer
$session = Get-VESQLRestoreSession
Stop-VESQLRestoreSession -Session $session
The struggle between SQL DBAs and the data protection team may continue for years, but hopefully this post provides guidance on how to have the best of both worlds. This enables the data protection team to ensure databases are protected, while enabling DBAs to perform quick and simple recoveries.