March 2015 - Posts

Azure for storage of backups is great its cheap and you can easily store them compressed and encrypted. If bandwidth isn’t an issue away you go.

Microsoft introduced Backup to URL for SQL 2014 and back ported it to SQL 2012 SP1 CU2. This is great as you can just run your backup and they get uploaded direct to azure.

If however you are using SQL 2012 prior to SP1 CU2 or a version of SQL prior to SQL 2012 then you aren’t lost.

Microsoft have developed a tool (Microsoft® SQL Server® Backup to Microsoft Azure®Tool) that works for all versions of SQL Server.

It installs a filter driver (like many of the backup compression vendors do) that intercepts requests for backup files and streams them to Azure Blog storage.

Whilst it does what it does its not exactly intuitive. You install it and configure it and then think what next. Thats because the UI is just for configuring the folders that the tool will “filter” on and redirect requests to azure. Once you’ve done that you then run a normal backup or restore, well sort of. The tool doesn’t give you an explanation and so I’ve provided you one here which should explain all you need.

The crux of how this works is the creation of two files, a meta data file and a contents file. You need the meta data file to be able to do the restore. This file is titchy and points to the main data file

{
  "Version": 1,
  "Azure": {
    "DataBlobName": "master-40fdcc93-fe60-4a43-b2e1-8330e64dbbf9.bak.enc",
    "ManifestBlobName": "master-40fdcc93-fe60-4a43-b2e1-8330e64dbbf9.bak",
    "AzureStorageAccount": "--------",
    "AzureContainer": "------"
  },
  "Local": null,
  "FileSize": 3194880,
  "CompressedSize": 418288,
  "Trailer": "QZ0jQWlF4ymCLWn5OZJDoPRsWVQ0RPA1h2FywEvjwyX3n. . . Vh4OI2nizLgPC//OhR1IXmXhUFC0yIpuP1bDs=",
  "Compressed": true,
  "DataEncryptionDetails": {
    "Salt": "2ETZxxZZIhwbyyJfYvtJZiRyjQ2E9FXT5wCnanVcCcI=",
    "Iv": "fUGUKTOPRewwmgZYn1tTqQ==",
    "Mac": "qg3ziLsf9nbOc2CbRhN3ZbsWqBMeTl4wkFzD1Gwl/e8="
  },
  "TrailerEncryptionDetails": {
    "Salt": "R5Py4HN0ui+VkXCRiu2+NXVKyUtrBAzsuT/nZ6aRkfU=",
    "Iv": "p6Vf8h3SpRZPfCVRDJI60Q==",
    "Mac": "g8e2tHYU3Ld0Y+Wn69LE7+Ewc6xb2/GfQvGCk/2uvS8="
  }
}

Backup to azure

  1. Configure a folder in this tool that SQL has access to be monitored by SQLBackup to Azure
  2. Now go in to SQL Server and run a backup as normal to the folder and it will be backed up to Azure

SQL will create 2 files,

    1. The first file with the name you specified. This contains the meta data of where the file will be backed up to in azure (but not with any credentials)
    2. The scond file contains the data and will have the same name with a suffix to match the type of backup,
      • non compressed and non encrypted = .data
      • compressed and non encrypted = .gz
      • compressed and encrypted = .enc

Restore a backup to another machine

1. Configure a folder in this tool with the correct blog storage account and credentials (and password if using encrypted backups)

2. The meta data file

Download the file with the original name (i.e. no 1 from above) from blob storage to the folder you have configured in step 1.

3. Run your restore specifying the path to the file in the folder in step 2.

SQLBackupToAzure will intercept the request and stream the data from azure to SQL Server.

Posted by simonsabin | 1 comment(s)