500TB Storage for Database Backup

When your database is big, sometimes just its backup will be around 20GB in size already. Hence, keeping them on disk is always not a solution even though Microsoft Azure provides a data disk with 1TB.

Fortunately, Microsoft Azure offers a scalable and larger storage. It is called Microsoft Azure Storage, a storage with 500TB capacity limit. The good thing about it is we only need to pay for the amount we are using in the storage.

Hence, Chun Siong from Microsoft Singapore suggested my company to try out this service to store our database backups. It turns out that it can be easily done in just 3 steps.

Step 1: Create Azure Storage Account and Retrieve Access Keys

To create a new Azure Storage Account, I simply login to the Azure Management Portal and then choose the Quick Create option of the Storage under Data Services section. I am able to specify the affinity group and replication rule for the Storage Account.

After the Storage is created, I can retrieve access keys which will be used later in SQL Server to access the Storage Account.

Finally, I just need to create a Container in the Storage Account. All the database backup files will be put inside the Container later.

Step 2: Create SQL Server Credentials

I then execute the following T-SQL statement to create credentials so that SQL Server later can connect to the Storage Account.

CREATE CREDENTIAL mycredential 
WITH IDENTITY= 'chunlindbbackup', 
SECRET = '<storage account access key>'

The Storage Account access key here can be either Primary or Secondary access key retrieved in Step 1 above.

Step 3: Backup Database

I create a scheduled job in SQL Server Agent to do backup of my database daily. The URL is the URL of the container created in Step 1.

BACKUP DATABASE mydatabase 
TO URL = 'https://chunlindbbackup.blob.core.windows.net/dbbackup/mydatabase_' + REPLACE(CONVERT(VARCHAR ,GETDATE(),126) ,':','_')+ '.bak'
WITH CREDENTIAL = 'mycredential', INIT, NAME = 'Backup of Database mydatabase'

So yup, now the database backups will be stored on the Storage Account directly.

Restore Database Backup from Azure Storage

To restore a database backup from Storage Account, if the backup file is small, I can just simply execute the following T-SQL statements. The URL of the database backup can be found in the Container in Azure Management Portal.

RESTORE DATABASE mydatabase_test 
FROM URL = 'https://chunlindbbackup.blob.core.windows.net/dbbackup/mydatabase_2014-10-14T13_16_01.243.bak' 
WITH RECOVERY,
MOVE 'mydatabase_db_Data' TO 'F:\db\mydatabase_test_Data.mdf',
MOVE 'mydatabase_db_Log' TO 'F:\db\mydatabase_test_Log.ldf',
CREDENTIAL = 'mycredential'
GO

Unfortunately, the backup that I have is too big. So, I can only download it from Azure Management Portal to the database server first before restoring the database. The download is quite fast.

In case, you wonder why I do not use tool like Azure Storage Explorer, no, it did not work. It would crash also if the backup file was too big.

Pricing

Oh ya, just in case you would like to know the pricing of Azure Storage, you can check it out here: http://azure.microsoft.com/en-us/pricing/details/storage/.