Restoring SQL backups stored in Azure cloud storage

Need for supporting SQL backups stored in Azure cloud storage

Currently, you can restore the SQL backups that are stored locally on the staging host, or on the network path, or on the backup servers with third-party vendors such as Commvault or Netbackup. At the same time, there are users that are having their native SQL backups on Azure Cloud Storage. 

Delphix now supports restoring native SQL backups from Azure Cloud Storage. This enables users who are moving to Azure to use direct backups from the Azure Storage containers instead of third-party vendors.

These backups support the following.

• This is supported by SQL Server 2016 and above

• All backup modes 

  • Full backups

  • Differential

  • Transaction log backups Logsync (point-in-time provisioning) is currently not supported. However, LogSync can still be enabled if Azure backup is being ingested. LogSync for SQL native backups that are present on Disk will work as before.

• Striped backups All backup files are to be entirely present on the Azure Cloud and no part of a backup should be present outside of Azure Cloud.

• Validated Sync

• Azure Storage authentication mode This backup solution uses a shared access signature token authentication method to authorize access to the blob data.

Workflow architecture

Workflow Steps

• User takes backup: You need to take the SQL native backup directly to the Azure Cloud using SQL Backup to URL. Performance can be improved by enabling COMPRESSION while taking the backup. If the backup size is large, it should be striped into multiple files.

  CODE

  BACKUP DATABASE [SourceDB4]
  to URL = 'https://idea1201.blob.core.windows.net/ideal1201container/source.bak'

• Authentication to authorize access to the blob data:
  You must create a SQL Credential on the staging host for the following cases.

  • Create using SAS authentication token as Access Key authentication is currently not supported.

  • Create for each Azure container where the backup files are expected to be stored.

  • Create on each SQL instance where restore has to be performed.

    CODE

    CREATE CREDENTIAL [ https://idea1201.blob.core.windows.net/ideal1201container] (this is the Azure Container URL)
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=2019-12-12&ss=bfqt&srt=c&sp=rwdlacupx&se=2021-01-19T14:18:04Z&st=2021-01-19T06:18:04Z&spr=https&sig=DBlZnu0VQTaXUwY9IgBEqNbSk';
    (provide the SAS token here)

• Run queries on Staging host: Delphix Engine uses this credential to run the following queries Restore, Restore Headeronly, and Restore Filelistonly on the staging host.

• Irrespective of whether auto-discovery is selected or not, if an Azure backup is being ingested, its location will be fetched from the msdb.dbo.backupmediafamily table on the source host and it will be restored on the staging host. Hence, it is necessary that the backup files are present on the same blob URL where the backup was originally taken. This is different from the usual native backups to the disk.

In SQL native disk backup, 

• If auto-discovery is on, the backup path is fetched from msdb.dbo.backupmediafamily table on the source host.

• If auto-discovery is off, Delphix Engine uses the custom path(s) specified by the user.

If any native Azure backup is found, Delphix Engine will always try to restore it. 

• This is similar to the user taking backups using multiple backup vendors. 

• In that case, Delphix Engine tries to restore all backups, irrespective of the backup vendor.

Unsupported features

The following features and functionalities are currently not supported.

• Third-party backup vendors

• Point-in-time provisioning

• Access key authentication method for Azure backups

• Support for SQL Server versions below SQL 2016

• Moving backup files across Azure containers

• Moving a backup from disk to cloud

• Striped backups - Backup files that are partially present on Azure Cloud and partially on another device.

• Managed identity