Most companies required to keep their database backup offsite (on tape) for many years due to audit requirement. This normally achieved by backing the database to local storage and use another software to backup to tape, or backup the database directly to tape and send them offsite to fulfill the audit requirement.
However, with SQL Server 2012 SP1 CU2 onwards added support for issuing backup of the database directly to Windows Azure Storage. With that, you can also fulfill your audit requirement of keeping your backup offsite (since its in Azure), and you do not require to manage your tapes as well as other vendors to keep your tapes (if there are any). Not to mention your backup is always online and do not need to load the tape if you ever need to restore old backup. As well as driving down the overall cost of your whole backup solution.
Let go through how it can be setup:
Creating Windows Azure Storage Account for backup storage
You can go to Windows Azure Portal (you can find more about pricing for storage here) and create a storage account as shown below, select location group and specify whether you want to enable Geo-Replication for the data storage.
Once the storage account got created, you can click on Keys for managing primary and secondary access keys used when connecting to storage account.
Once you have everything setup from Azure side, you can connect to your storage account via a nice tool called Windows Azure Storage Explorer, or use the portal to manage the storage account.
Using Windows Azure Storage Explorer
To connect to Windows Azure Storage account, click on Add Account button and will get you the screen below. Fill in the storage account name and storage access key as you got from previous screen and click on Save button.
Once you have connected to the Azure storage account, you should see the interface below. You can create new container (like folders) to store your backup files.
Simply specify the name of the container and the Access Level and click on Create, it will then create the container for you. For Access Level, its recommended to use Off as it will restrict access to the account owner only, providing a more secure peace of mind when storing your backup.
Using Azure portal to manage container
If you like to use the portal, it would be very easy to manage as well, simply click on the storage account, go to Containers and click on Add, provide the container name and pick the Access Type then click on the OK button.
Once you did that, it will create the container for you and listed on the Containers screen
Backup SQL server database directly to Window Azure Storage using T-SQL
In SQL Server 2012 SP1 CU2 onward, both backup database and restore database commands have been enhanced to have two new clauses: CREDENTIAL and URL. The CREDENTIAL clause is used to specify the credential required to authenticate with resource outside SQL server, in this case Windows Azure Storage Account, where the URL clause is used to specify the URL for Windows Azure Blob Storage location and the backup file name.
In order to perform the database backup, you will need to first create a credential using CREATE CREDENTIAL command. Simply specify the Azure storage account name and access key as shown below, and use the BACKUP DATABASE command to take the database backup store it directly to Azure Storage using the URL and WITH CREDENTIAL clauses.
--Create a credential to connect to the windows azure storage service
IF NOT EXISTS (SELECT * FROM sys.credentials WHERE credential_identity = 'myazurestoragedemo')
BEGIN
CREATE CREDENTIAL myazurestoragedemocredential WITH IDENTITY = 'myazurestoragedemo'
,SECRET = 'Hax9VVBuOg4pA3omcmK3KeaOCAwVzsXlpQWOng5gkTcjrLWLXILPBP9cumDXQNaBXc9ALtxHOn0dEKsRF5Tdxw=='
END
GO
--Backup the database to the windows azure storage service - blob using URL
BACKUP DATABASE AdventureWorks2012
TO URL = 'https://myazurestoragedemo.blob.core.windows.net/sqlbackup/AdventureWorks2012.bak'
WITH CREDENTIAL = 'mydatabasebackstorecredential'
,COMPRESSION --Compress the backup
,STATS = 10 --This reports the percentage complete as of the threshold for reporting the next interval
GODo note that unless you have a VPN setup to Azure, your backup will go through public internet, depending your network speed, this process might take time.
Restoring from Azure Storage is simple as well, simply use the RESTORE DATABASE command with URL and WITH CREDENTIAL clauses as shown below:
USE master
RESTORE DATABASE AdventureWorks2012
FROM URL = 'https://myazurestoragedemo.blob.core.windows.net/sqlbackup/AdventureWorks2012.bak'
WITH CREDENTIAL = 'mydatabasebackstorecredential'
,MOVE 'AdventureWorks2012_Data' to 'D:\D Drive\SQL Server 2012\SampleDatabases\AdventureWorks2012_Data.mdf'
,MOVE 'AdventureWorks2012_Log' to 'D:\D Drive\SQL Server 2012\SampleDatabases\AdventureWorks2012_log.ldf'
,STATS = 10
GOAgain, depending on your network speed and the size of the backup, the restore operation might take some time to complete.
Things to consider
There are a few things to point out and consider when using Azure Storage. its great with its low cost and it might help you to keep more database backup online rather then keeping it on tapes. You might can even get rid of your current 3rd party backup solution and offsite tape storage and start to have saving on your overall backup solution.
However, with planning for budget with Azure storage, you will have to be aware that you are not just paying for the storage, you will have to pay for the bandwidth as well. Although upstream to Azure is free, all downstream traffic will be charged (with a monthly first 5 GB free data transfer). In the case of backup, you normally do not require to download it back unless you require to perform a restore, so it might not have a big impact of cost, but if you do require to have regular restore to staging or development environment, it might be a better idea to store a few local copies as well.
Another thing is note is security, although you can set the Storage account access level to be Off (account owner access only), you might still want to encrypt the database backup before putting to Azure.
