Using Azure Storage for SQL Backups

you may have taken backups on local storage, file servers, NAS, etc. Now a days it is becoming crucial you have backup copies available in different locations just in case disaster strikes and you need your large backup readily available on other coast.

Cloud Storage probably is the best option you can have without having data centers offsite availability for BCP.

As I have been reviewing Azure for some time now, I find it very simple and intuitive taking backups on Azure Storage.

Some of the storage objects provided by Azure are Blob, Table, Queue and File. Not only Azure Storage is highly available from anywhere in the world also it secured and durable.

By default, the data in Storage Account is secured with Encryption at server-side (using Transparent Data Encryption).

Data from Azure Storage can be accessed via URLs (http and https). The URL (also called end points) are fixed for each object type and can be referenced by prefixing unique storage account name. Here are the URLs for various object types:

Blob: blob.core.windows.net

Table: table.core.windows.net

Queue: queue.core.windows.net

File: file.core.windows.net

For example, if my blob storage account name is “mystorage” then the URL will become http://mystorage.blob.core.windows.net/

In order to understand Azure Storage, I would like to give one End to End demo on Azure Storage. The demo will include creating Storage Blob and then taking backup directly on the Storage Blob.

At high level these are the steps we need to take:

Setup Storage

• Create Resource Group
• Create Storage Account
• Create Storage Context
• Create Storage Container
• Create Access Policy
• Create Shared Access Signature (SAS)

Preparing for SQL Backup

• Create Credentials for Storage in SQL Server
• Connect to Azure Storage from SSMS

Final Steps (SQL backup on Azure Storage)

• Take Backup on Azure Storage using connected Azure Storage (URL)
• Verify Backup on Storage

Setup Storage

• Connect to PowerShell (Elevated)
• Install and connect to Az (Azure Module)

Install-module az -AllowCobbler

Connect-azAccount

• Complete the Sign in Process as prompted
• Setup a few variables

$resourcegroup = '1209rg'

$storageaccount='1209sa'

$container='1209con'

$policyname='1209policy'

• Get the Subscription ID#Get SubscriptionID

$subscription=Get-AZSubscription

$subscriptionid=$subscription.ID

• Switch the context to Subscription as selected above

# Change the context to subscription ID for further work

Set-AzContext -SubscriptionId  $subscriptionid

• Create new resource group (read more about resource groups here: https://tinyurl.com/y8k3jqoj)

# create new resource group for

New-AzResourceGroup -Name $resourcegroup -Location 'East US'

• Create a new Storage Account

·         You may read more about storage accounts at https://azure.microsoft.com/en-us/pricing/details/storage/page-blobs/

·         For creating storage account, you need to provide Resource Group name, Location and Type of storage

·         For more information on storage types, visit this page: https://docs.microsoft.com/en-us/azure/storage/common/storage-account-overview

# Create new Storage Account

New-AzStorageAccount -Name $storageaccount -ResourceGroupName $resourcegroup -Location 'East US' -Type Standard_LRS

• Getting Access Keys

o    After creating storage account, azure will be default create two new access keys and these keys are secret passcode to access the storage remotely using http or https URL

$accesskey = (Get-AzStorageAccountKey -ResourceGroupName $resourcegroup -Name $storageaccount).Value[0]

o    Access keys can be validated from Portal as well

• Creating Storage Context

o    Storage context is a reference to storage by taking storage account name and access key

$storagecontext=New-AzureStorageContext -StorageAccountName $storageaccount -StorageAccountKey $accesskey

• Creating Storage Container
• Storage container can be thought of as a parent folder within Storage Account and holds the data

$storagecontainer = New-AzureStorageContainer -Name $container -Context $storagecontext

·         Creating Access Policy

o    Access Policy can be considered as a group policy for containers and Shared Access Signatures Token can be created with policy as defined in Access Policy and makes it easy to manage, revoke and restrict access. SAS without Access Policies can be difficult to manage. In order to revoke access from SAS without a policy, the access key would need to be changed.

$policy=New-AzureStorageContainerStoredAccessPolicy -Container $container -Policy $policyname -Context $storagecontext -StartTime $(Get-Date).ToUniversalTime().AddMinutes(-5) -ExpiryTime $(Get-Date).ToUniversalTime().AddYears(1)  -Permission rwld

·         Creating shared access key and getting container object

o    A shared access key is required for creating credentials and container object is required to get URL from container

# get blob container

$cbc = $storagecontainer.CloudBlobContainer

o    Following script will copy the script in clipboard

# Create credentials

$tSql = "CREATE CREDENTIAL [{0}] WITH IDENTITY='Shared Access Signature', SECRET='{1}'" -f $cbc.Uri,$sharedaccesstoken.Substring(1)  

$tSql | clip

Preparation of SQL Backups

·         Create Credentials

o    In the previous step we generated CREATE CREDENTIAL script and stored in Clip, paste the content from clip to SSMS where you wan to run the backup process

CREATE CREDENTIAL [https://1209sa.blob.core.windows.net/1209con] WITH IDENTITY='Shared Access Signature',

SECRET='sv=2017-07-29&sr=c&si=1209policy&sig=XbRBSAQiwkHds2a5vOBR8sM0rKpAQJkLbI3D'

o    To verify expand the Credentials from Security as you can see below:

·         Now add Azure Storage Account in SSMS

o    SSMS-> Connect-> Azure Storage

o    Enter Storage Account Name and Access Key as got from previous steps (or you may get it from Portal too)

o    Getting from Portal

Final Backups to Azure Storage

·         Backups

o    Backup on Azure Storage is just slightly different from traditional backups on file system or tape. When taking backups on Azure, you need to select URL as destination instead of file or tape:

o    Right click on database (I am using Test database) and select Task -> Backup

o    From Back up to option select URL and click on Add 

o    The Dialog box for Selecting Backup Destination would let you select the storage container that added in previous steps

o    Enter Backup File Name and click on Ok

o    





T-SQL for taking backup on Azure Storage Container is copied below:

BACKUP DATABASE [Test] TO  URL = N'https://1209sa.blob.core.windows.net/1209con/testbackup.bak' STATS = 10

GO

o    If the command completes successfully you should have your backup posted on Azure Container

·         Verify Backup

o    You can verify the backup file from either SSMS->Storage Container or from Portal itself

o    From Portal, Navigate to Resources-> Storage Resource-> Blob-> Container

o    From SSMS, Expand Storage Account-> Containers-> Container

Conclusion

You now have your backup on Azure Cloud and you can restore it from anywhere where there is internet to connect to cloud. You can restore backups to On-Premises and IaaS (SQL running on Azure VM). The process to restore is same, you have to follow the process of creating credentials and adding existing storage to SSMS.