SQL server backup and recovery
Common SQL Server backup and recovery challenges
Azure NetApp Files storage-based snapshot technology
SQL Server 2022 snapshot technology
SQL Server snapshot backup and recovery workflow
Restoring and cloning SQL databases
Links to additional information
This article provides guidance on how to use T-SQL snapshots to backup Microsoft SQL Server 2022 leveraging Azure NetApp Files snapshots on Azure virtual machines.
Co-author(s): Pat Sinthusan, NetApp
This blog provides an overview of the configuration of an Azure environment to run SQL Server with SMB file share storage using Azure NetApp Files with the highest levels of resiliency.
This article is intended for Azure NetApp Files and/or SQL Server database administrators who are responsible for deploying and protecting SQL Server in Azure. It is assumed that the reader is familiar with the various components of the solution.
Managing a very large database (VLDB) is a demanding task for a database (DB) administrator. Azure NetApp Files dramatically simplifies the management of SQL Server VLDBs in Azure environments. SQL Server has supported Server Message Block (SMB) since version 2012. Azure NetApp Files offers access to storage through the multichannel-enabled SMB protocol, providing low-latency file storage to SQL Server combined with snapshot capabilities.
Businesses have been increasingly migrating on-premises workloads to Azure for a number of reasons, including datacenter consolidation and cost effectiveness. For VLDB Migrate and Modernize scenarios with DB sizes in the double-digit TB range, virtual machine (VM)-based, Infrastructure as a Service (IaaS) architectures are a viable option. Memory-optimized Azure VMs like the E-series and M-series offer excellent compute capabilities for demanding DB workloads like SQL VLDBs or SAP HANA. Azure NetApp Files is an Azure-native NAS storage solution for running high performance SQL workloads in combination with Azure VMs.
Some SQL Server installations with DB sizes of over 50TB up to around 200TB are becoming more and more commonplace. Although there is no precise definition for a VLDB, a multi-terabyte DB – especially when growing into the 10s of TiBs – can add additional complexity for high availability and backup and restore operations.
The following summarizes the SQL Server backup and recovery challenges:
Azure NetApp Files Snapshot technology can be used to create online database backups within minutes. Because a snapshot does not move any physical data blocks on the storage platform, the time needed to create a snapshot is independent of the size of the database. The use of snapshot technology also has no performance impact on the live database. That is because the Azure NetApp Files snapshots do not move or copy data blocks when the snapshot is created or when data in the active file system is changed. Therefore, the creation of snapshots can be scheduled without having to consider peak dialog or batch activity periods. SQL Server customers leveraging Azure NetApp Files typically schedule multiple online snapshots during the day; for example, scheduling snapshots every four or six hours is common. These snapshots are typically kept for three to five days. For long-term retention older snapshots are then typically vaulted using Azure NetApp Files backup to Azure storage account.
Snapshots also provide key advantages for the restore and recovery operation. Azure NetApp Files ‘Revert Volume’ functionality allows restoration of the entire database to any point in time based on the available snapshots. This restore process is performed near-instantaneously, independent of the size of the database. Because several online snapshots are created during the day, the actual time needed for the recovery process is dramatically reduced, as opposed to a traditional backup approach. A restore operation can be performed using a snapshot that is only a few hours old (rather than up to 24 hours old); therefore, fewer transaction logs need to be applied. As a result, the mean time to recover or RTO, which is the time needed for restore and recovery operations, is reduced to just several minutes compared to multiple hours with conventional single-cycle tape backups.
Azure NetApp Files snapshots are stored on the same volume as the active online data. Therefore, it is recommended to use Azure NetApp Files backup or Azure NetApp Files cross region replication (CRR) to a secondary Azure NetApp Files region to safeguard the data from accidental deletions.
This article describes how to backup and restore a SQL Server 2022 database when using Azure NetApp Files using T-SQL snapshots to protect the database.
Database consistent storage snapshots offer great advantages over the traditional built-in SQL Server backup functionality in terms of speed and added business value. Snapshots can, for example, be utilized for quickly syncing dev/test-systems or a fast recovery from failed operations or database corruptions. Transact SQL (T-SQL) snapshot backup is now available with SQL Server 2022. However, the backup itself happens at the storage level, this is not a SQL Server-only solution. SQL Server must first prepare the data and log files for the snapshot so that the files are guaranteed to be in a state that can later be restored. Once this is done, I/O is frozen on SQL Server and control is handed over to the backup application to complete the snapshot. Once the snapshot has successfully completed, the application must return control back to SQL Server where I/O is then resumed.
With T-SQL snapshot backups, the SQL Server side of the orchestration can be handled with a series of T-SQL commands. This allows users to create their own simple backup applications that can run on either Windows or Linux, or even scripted solutions as the underlying storage supports a scripting interface to initiate a snapshot.
The following summarizes the backup workflow:
Since SQL Server use UNC path on Azure NetApp Files volumes (SMB protocol) it is easy to point to the database files and log location by using UNC path. There is no need to mount or remount the volumes.
The following is a sample PowerShell script to create SQL Server snapshot backup with Azure NetApp Files snapshots. This PowerShell use service principal name (SPN) to login to Azure. For more information on SPN, please visit Use the portal to create an Azure AD application and service principal that can access resources.
In this scenario, Contoso’s ‘SeattleRetail’ database has 2 database files with 1 TiB each residing on Azure NetApp Files volumes:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser
Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force
Install-Module -Name Az.NetAppFiles -AllowClobber -Force
#Azure variable
$azvar = @{
plainPassword = '<Application Secret>'
ApplicationId = '<Your Application ID>'
TenantId = '<Your Tanent ID>'
resourcegroup = '<Your resource group>'
location = '<Your region>'
}
$SecuredPassword = $azvar.plainPassword | ConvertTo-SecureString -AsPlainText -Force
#ANF variable
$anfvar = @{
accountname = 'ntap-acct-contoso-central'
poolname = 'cap-pool-contoso'
vols = @('SQL2022Prod-data1', 'SQL2022Prod-data2', 'SQL2022Prod-log')
ss = "SQL2022Prod-ss"
}
#SQL Server variable
$sqlvar = @{
SQLServer = 'SQL2022Prod'
db = "[SeattleRetail]"
bkm = "'\\contoso-900e.contoso.io\snapinfo\SeattleRetail.bkm'"
}
$SQLConn = New-Object System.Data.SQLClient.SQLConnection
$Command = New-Object System.Data.SQLClient.SQLCommand
$SQLConn.ConnectionString = "server='"+ $sqlvar.SQLServer +"';database='master';Integrated Security=True;"
$Suspenddb = "ALTER DATABASE " + $sqlvar.db + " SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;"
#Unsuspenddb
$Suspenddb = "ALTER DATABASE " + $sqlvar.db + " SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF;"
$Backupmetadata = "BACKUP DATABASE " + $sqlvar.db + " TO DISK = " + $sqlvar.bkm + " WITH METADATA_ONLY, FORMAT, MEDIANAME = '" + $anfvar.ss + "' , MEDIADESCRIPTION = '" + $anfvar.ss + "';"
#For restore the database back to the same server
$InplaceRestore = "RESTORE DATABASE " + $sqlvar.db + " FROM DISK = " + $sqlvar.bkm + " WITH METADATA_ONLY;"
#For creating a clone database to the same or different server
$Clonedb = "RESTORE DATABASE [SeattleRetail-Clone] FROM DISK = " + $sqlvar.bkm + " WITH METADATA_ONLY `, MOVE 'SeattleRetail1' TO '\\contoso-900e.contoso.io\SQL2022Prod-data1-clone\SeattleRetail1.mdf' `, MOVE 'SeattleRetail2' TO '\\contoso-900e.contoso.io\SQL2022Prod-data2-clone\SeattleRetail2.mdf' `, MOVE 'SeattleRetail_log' TO '\\contoso-900e.contoso.io\SQL2022Prod-log-clone\SeattleRetail_log.ldf'"
#Connect to Azure
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $azvar.ApplicationId, $SecuredPassword
Connect-AzAccount -ServicePrincipal -TenantId $azvar.TenantId -Credential $Credential
The result of the above command should display as follows:
#Connect to SQL Server
$SQLConn.Open()
$Command.Connection = $sqlConn
#Suspend DB
$Command.CommandText = $suspenddb
$Result = $Command.ExecuteNonQuery()
This is similar to executing the T-SQL command in SQL Server Management Studio:
#Create Snapshot
foreach ($vol in $anfvar.vols){
New-AzNetAppFilesSnapshot -ResourceGroupName ($azvar.resourcegroup) -Location ($azvar.location) -AccountName ($anfvar.accountname) -PoolName ($anfvar.poolname) -VolumeName $vol -name ($anfvar.ss)
}
This command is similar to using Azure Portal:
$Command.CommandText = $Backupmetadata
$Result = $Command.ExecuteNonQuery()
This is similar to execute T-SQL with SQL Server Manage Studio as follow:
Foreach ($vol in $anfvar.vols) {
$volobj = Get-AzNetAppFilesVolume -ResourceGroupName $azvar.resourcegroup -AccountName $anfvar.accountname -PoolName $anfvar.poolname -Name $vol
$ssid = Get-AzNetAppFilesSnapshot -ResourceGroupName $azvar.resourcegroup -AccountName $anfvar.accountname -PoolName $anfvar.poolname -VolumeName $vol -Name $anfvar.ss | select -ExpandProperty SnapshotId
Restore-AzNetAppFilesVolume -ResourceGroupName $volobj.ResourceGroupName -AccountName $anfvar.accountname -PoolName $anfvar.poolname -VolumeName $vol -SnapshotId $ssid
}
$Command.CommandText = $InplaceRestore
$Result = $Command.ExecuteNonQuery()
#Create Clone Volume
foreach ($vol in $anfvar.vols){
$volobj = Get-AzNetAppFilesVolume -ResourceGroupName $azvar.resourcegroup -AccountName $anfvar.accountname -PoolName $anfvar.poolname -Name $vol
$volclone = $vol + '-clone'
$ssid = Get-AzNetAppFilesSnapshot -ResourceGroupName $azvar.resourcegroup -AccountName $anfvar.accountname -PoolName $anfvar.poolname -VolumeName $vol -Name $anfvar.ss | select -ExpandProperty SnapshotId
New-AzNetAppFilesVolume -ResourceGroupName $azvar.resourcegroup -Location $azvar.location -AccountName $anfvar.accountname -PoolName $anfvar.poolname -VolumeName ` $volclone -SnapshotId $ssid -UsageThreshold $volobj.UsageThreshold -SubnetId $volobj.SubnetId ` -CreationToken $volclone -ServiceLevel $volobj.ServiceLevel -SecurityStyle ntfs -ProtocolType CIFS
}
$Command.CommandText = $Clonedb
$Result = $Command.ExecuteNonQuery()
For more details information on T-SQL snapshot backup, please refer to Create a Transact-SQL snapshot backup.
Using SQL Server 2022 T-SQL snapshot backup in conjunction with Azure NetApp Files provides unique data management abilities for SQL customers only found in Azure. The combination of SQL Server snapshots with Azure NetApp Files storage snapshots give teams the insurance of an application consistent backup of the SQL Server database without the time and load on the system. The orchestration of this workflow can be handled by scripting.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.