2017-09-15

Dipping My Toes In The Azure Pool

Dave Mason SQL Server Azure Blob Storage

I've known about SQL Server's ability to backup a database to Microsoft Azure Blob Storage for some time. But until recently, I'd never tried it. This seemed like one of the easier ways to start learning about Microsoft Azure, so I decided to give it a try.

To start, I needed an Azure account (surprise, surprise). I navigated here and tried logging in with my Microsoft account. That didn't work. That was my first lesson: a "Microsoft Azure account" is not the same as a "Microsoft account". I won't retrace all the steps, but creating a new account was simple. I got $200 of credit towards Azure services for 30 days. After that, I'd be paying on my own. Note that a credit card was required to create the account.

At some point, I got redirected to the new Azure Portal. From there, I created the following objects (in order):

  1. A Resource Group.
  2. A Storage Account
  3. A Container.

Naming things (servers, database objects, .NET Framework classes and methods, etc.) often causes a lot of hand wringing. I didn't stress it, though. I chose "SQLBackup", "dmasontestbackup", and "sql-backups", respectively. If I had it to do over, I'd pick something more sensible. C'est la vie.

Now let's dive into more familiar territory: SSMS and T-SQL. I'll need a CREDENTIAL to backup to a URL. The credential name can be whatever I want, the IDENTITY name is my Azure Storage Account name, and the SECRET is my Azure Storage Account key.

CREATE CREDENTIAL AzureBackup_Credential
WITH IDENTITY = 'dmasontestbackup',
SECRET = 'tI4rqRyHhq94MdcV2QjVEsYy/cmkzvcFNeSUJHKfzWXw1TM0I6CY4zUTcUjlio8k59c3a89fzY1epauPCJqrsE==' 
GO
Dave Mason SQL Server Microsoft Azure Storage Account Access Keys


Now I can backup TO URL (instead of the usual TO DISK). The format for the URL string is:

http://<StorageAccountName>.blob.core.windows.net/<ContainerName>/<BackupFileName>


Here's my backup statement:

BACKUP DATABASE XEventTest
TO URL = 'http://dmasontestbackup.blob.core.windows.net/sql-backups/XEventTest.bak'
WITH CREDENTIAL = 'AzureBackup_Credential';
GO


Navigating to the "sql-backups" container will show the backup:

Dave Mason SQL Server Microsoft Azure Container


RESTORE commands FROM URL have the expected results (as long as the CREDENTIAL is specified):

RESTORE FILELISTONLY
FROM URL = 'https://dmasontestbackup.blob.core.windows.net/sql-backups/XEventTest.bak'
WITH CREDENTIAL = 'AzureBackup_Credential';

RESTORE HEADERONLY
FROM URL = 'https://dmasontestbackup.blob.core.windows.net/sql-backups/XEventTest.bak'
WITH CREDENTIAL = 'AzureBackup_Credential';
Dave Mason SQL Server RESTORE Output


Lastly, let's restore the database:

RESTORE DATABASE XEventTest_Restored
FROM URL = 'https://dmasontestbackup.blob.core.windows.net/sql-backups/XEventTest.bak'
WITH CREDENTIAL = 'AzureBackup_Credential',
 MOVE 'XEventTest' TO 'C:\SQL Server\XEventTest_Restored.mdf',
 MOVE 'XEventTest_log' TO 'C:\SQL Server\XEventTest_Restored.ldf';


Common sense tells me there will be some differences between backup and restore TO/FROM URL vs TO/FROM DISK. I did, in fact, try striping a backup TO URL across multiple files and got an error. Still, in my brief experience, once the necessary objects were created in Azure, backup and restore via URL worked as normal.


SHARE

No comments :

Post a Comment

Subscribe