Full Architecture for SQL Backup to Blob Storage in Cloud

One of the tasks that any DBA should know How to take backup to URL or restore a backup from URL and this’s the easiest part very simple steps and command but this is not our main objective, we should understand all of the components how it is working, what is the different options, what is the changes happened before SQL Server 2016 and After SQL Server 2016 to take the maximum benefits from this service or feature here in my article I will try to conclude all of this things as much as I can.

• Introduction.
• Benefits from taking backup to URL.
• What is Azure Storage Blob?
• What are the Pre-requisites to do SQL backup on the URL?
• What is the Difference between SAS Token and access key Azure Storage
• When I Should use SAS Token or access key Azure Storage
• When the Backup files will be saved as Block Blob or Page Blob
• How to Generate and configure Access Policy, Access key, and Shared Access Signature
• Create SQL Server Credential using access key or SHARED ACCESS SIGNATURE
• Backup to Azure Storage and restore from Azure Storage.
• Validate the Backup File.
• Notes and Limitation for SQL backup to URL.
• References and Resources.

Introduction

Most of us taking our SQL backup from on-Premises servers on dedicated backup storage Using Custom T-SQL or Maintenance plan, but Microsoft provided us also another feature called backup to URL to take your backup outside all of your on-Premises servers and this feature introduced starting from SQL Server 2012 CU1 and SQL Server 2016 Microsoft enhanced this feature to use Shared Access Signatures (SAS) that’s mean high level of security, taking backup to Azure Storage Cloud with accessing the Storage account by SQL Server Credential that is point your Azure storage account using Access Key or SAS token and this feature gives us a lot of benefits.

Benefits from taking backup to URL

1. Security: Backup Files on Azure Storage will be more secure instead of keeping it on your local servers
2. Cost: You can reduce the cost of the on-Premises backup by hosting your archived backup on Azure Storage.
3. Geo-Replication: Backup will be accessed and available in multiple regions because of the Geo-replication feature in Azure storage.
4. Backup availability: Backup Files will be available in case of any disaster can happen to the on-premises environment; backup files will be available at any time from any place.

What is Azure Storage Blob

This part is a big topic and it needs many of articles to explain it But as small definition about Azure Storage it is the container for your data that you can save on it the Hot data and Cool data and it contains 5 types (Blob, Tables, Queue, File Share, Disks) our main focus here in Blob Storage that contains (Block Blobs, Page Blobs and Append Blobs) and in our scenario, we will use the Block blob and Page blob and I will explain when each one will be generated in for our backup file. IF you need to learn more about Azure storage as concept check these two articles (Article one, Article Two) and for other features and services and use cases for Azure storage check this link

What are the Pre-requisites to do SQL backup on URL

• Azure account: and you can use a free account with 200$ from here
• Azure Resource group: it is Container you can host on multiple azure services and it is the first step required for creating any azure services if you already have one you can use it and you can create a new one if you need from Here or during the creation of Azure Storage account you have the option to create a new resource group.
• Azure Storage account: and this service you can create it using Azure Portal, Azure CLI, Azure PowerShell Check this Post to know How you can create and remove Azure Storage.
• Azure blob container: and this like folder name that you will save your data on it so, for example, you can create 3 Container (Full Backup, DIFF Backup, Log Backup) Check this post to learn How you can do it with 4 steps  
• Azure Storage Policy: this policy created on the Azure blob container to determine the time of the access and the access privileges (read, write, delete, create,) and this policy will be used when we need to generate SAS token (Shared Access Signatures)
• Azure Storage access: to access your azure storage container you need to use one of two options (account identity and access key or SHARED ACCESS SIGNATURE) I will explain this more in the next part
• Azure Storage Explorer tool: this is a free tool you can download it from here and install it on your local on-premises server to manage your azure storage (This step is optional you can use Azure Storage Explorer on Azure portal) but I prefer to download it to take a look about How you can use it.
• Microsoft SQL Server tool: You need SQL Server management studio to be able to Execute your backup download, or you can use Azure Studio more powerful download

What is the Difference between SAS Token and access key Azure Storage

After Creating Azure Storage, you need to access it through your preferred tool or application to do this Microsoft provided to options

1. Access key: For any Azure storage account we have two keys you can use any one of them but this keys anyone or any application will access the storage account by this key will have full access on all of the Azure storage account, why we have two keys in case of your application accessing the azure storage account by Key1 and you need to remove this key for security purpose you can change this by using Key2 then you can do generate for Key1. Yes, you can do generate for Key1 or Key2 at any time to generate a new key that’s why we have two keys.
2. Shared Access Signature: Microsoft Provide use very secure solution to access your storage based on the time from – To and based on which type (container, table, queue, file share) and based on privileges, it is very helpful security role to allow access without sharing the account keys because using the account keys will allow administrative access and for this security feature, we can custom it by start and end date as an expiry date

When you take a Backup Created by SAS as block blob and you try to do restore for it using Access Key you will get this error

Msg 3271, Level 16, State 1, Line 18 A nonrecoverable I/O error occurred on file “https://elmasrysqlbackup.blob.core.windows.net/sqlbackup/AzureSQLDBV2.bak:” The specified URL points to a Block Blob. Backup and Restore operations on Block Blobs are not supported when WITH CREDENTIAL syntax is used… Msg 3013, Level 16, State 1, Line 18 RESTORE DATABASE is terminating abnormally.

When I Should use SAS Token or access key Azure Storage

This is a very important question if we have the two options which one, I should use Microsoft recommended to use SAS Token (Shared Access Signature) because it is more secure but if your local SQL Server version is 2012 or 2014 you cannot use Shared Access Signature because Shared Access Signature Supported from SQL Server 2016 and later. That why you should upgrade your SQL to 2016 😂😂 at least to take security benefits 💪💪, add in your note that you still have the option to use access key in SQL 2016 but still this note recommended not for security wise only but anther reason you will know it in the next part.

When the Backup files will be saved as Block Blob or Page Blob

• When you are using access key option as a credential in SQL server to be able to access the Azure storage account to take backup and this will save your backup As Page blob
• when you are using Shared Access Signature is a credential in SQL server to be able to access the Azure storage account to take backup and this will save your backup As Block blob

How to Generate and configure Access Policy, Access key, and Shared Access Signature

You can Generate the access key using Azure Portal but for Shared Access Signature you can generate it from Azure portal or Azure Storage Explorer tool, also from this tool, you can configure the Azure Access Policy

• Create Access Policy: Open Azure Storage account from Azure Portal, open the Azure Container, on the left side select access policy then add new Policy (Identifier, Permission, start time, Expiry time) then save. this policy will be used when we need to generate SAS token (Shared Access Signatures)

• Access Key: Open Azure Storage account from Azure Portal, from the left side select access key under settings menu then take a copy from Key1 or Key2 that will be used on SQL Server Credential
• Generate SAS Token: Open Azure Storage account from Azure Portal, from the left side select Shared access signature under the settings menu, Configure the access based on what you need in our case we will select the only blob as services and Container as resource type and the access can be read-write or full access, then at the end click on Generate SAS and connection string and take a copy from SAS URL.

• Azure Storage Explorer tool: in case if you need to use this tool after downloading and installing it, open the tool and connect to your Azure Storage using your Azure Email account, then you will have full view management to your azure storage, go to your account and container right-click on the container then select Manage access Policies if you need to create access policy or select Get Shared Access Signature to create SAS token on this policy

Create SQL Server Credential using access key or SHARED ACCESS SIGNATURE

Now we do all of our preparation created the Azure Storage account, Container, access Key, created Azure access Policy in case we will use SHARED ACCESS SIGNATURE, Generated SHARED ACCESS SIGNATURE using the access policy, it is the time now to create the connection between local SQL Server and Azure Storage account and this can be done using SQL Server Credential

Use master 
Go
Create Credential SQLbackupcredential
With identity = 'elmasrysqlbackup',
secret = 'cGYBa3bNBnMHQYzjL6oD4Kw5FAyMpjX4DzbjY9REwcH3xLBO1GA9TBOmIXiF/UexFFm9utLMVUJbFA5JH8NEpQ==


CREATE CREDENTIAL [https://elmasrysqlbackup.blob.core.windows.net/sqlbackup] 
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', 
SECRET = 'sv=2019-02-02&si=elmasryaccess&sr=c&sig=T3D%2Bdl09z2m9C%2FM3hu51R%2FdaaXN6FbHOVorsYUuEnOM%3D';

Backup to Azure Storage and restore from Azure Storage

Now after creating the credential we can take backup and restore with using Access key one time and second time with using SHARED ACCESS SIGNATURE

• Backup and Restore using access Key

Backup Database [AzureSQLDB]
to URL = N'https://elmasrysqlbackup.blob.core.windows.net/sqlbackup/AzureSQLDBV1.bak'
WITH Credential = 'SQLbackupcredential' ,NOFORMAT, NOINIT,  
NAME = N'AzureSQLDBV1-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO


USE [master]
GO
RESTORE DATABASE [AzureSQLDBV1] FROM  
URL = N'https://elmasrysqlbackup.blob.core.windows.net/sqlbackup/AzureSQLDBV1.bak' 
with Credential = 'SQLbackupcredential'
,FILE = 1,  
MOVE N'AzureSQLDB' TO N'D:\Work\bacpacfiles\AzureSQLDBV1_Data.mdf',  
MOVE N'AzureSQLDB_log' TO N'D:\Work\bacpacfiles\AzureSQLDBV1_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

• Backup and Restore using SHARED ACCESS SIGNATURE Using SQL Server 2016 or later

BACKUP DATABASE [AzureSQLDB] 
TO URL = N'https://elmasrysqlbackup.blob.core.windows.net/sqlbackup/AzureSQLDBV2.bak'


RESTORE DATABASE [AzureSQLDBV2] FROM  
URL = N'https://elmasrysqlbackup.blob.core.windows.net/sqlbackup/AzureSQLDBV2.bak' 
with FILE = 1,  
MOVE N'AzureSQLDB' TO N'D:\Work\bacpacfiles\AzureSQLDBV2_Data.mdf',  
MOVE N'AzureSQLDB_log' TO N'D:\Work\bacpacfiles\AzureSQLDBV2_log.ldf',  
NOUNLOAD,  REPLACE,  STATS = 10
GO

Validate the Backup File

Now after taking backup if you need to check the backup files you can do it from

• Azure portal: by checking the Files under the container that is created on Azure Storage account
• Storage Explorer(preview) on Portal: open portal, Azure Storage account in the left side select Storage Explorer(preview)  
• Azure Storage Explorer Tool:

• SQL Server Management Studio: in the SSMS Object Explorer Select Connect then select Azure Storage, and connect to Azure Storage account using your Azure Portal account, Select the Azure Storage account, Select the Azure Blob Container Then Connect.

Notes and Limitation for SQL backup to URL

The best place you can review this part on it is Microsoft documentation check it from here but I have one note to add it here that doesn’t use SQL Server maintenance plan when taking backup to Azure Storage using SHARED ACCESS SIGNATURE It will give you error check this article for more information My advice to use your custom script using T-SQL or PowerShell or you can use OLA Backup check it from here. Another note If you are in SQL Server 2016 and you try to take backup to URL using the Wizard option you should know that wizard will automatically create SQL Credential using SHARED ACCESS SIGNATURE, To use access key you should do it with T-SQL.

References and Resources

• SQL Server Backup to URL
• Use Azure Blob storage service with SQL Server 2016
• SQL backup and restore to Azure Blob storage service
• My Azure Articles
• My Azure SQL Articles

Keep Following

• WordPress
• Facebook
• Twitter
• LinkedIn
• YouTube
• Link
• Link

Cloud Tech Website blog survey

IF you found this blog is helpful and sharing useful content please take few second to do rate the website blog from here