Prevent Data Loss from Force Majeure

The current Windows Azure SQL Database (formerly “SQL Azure”) service level agreement specifically has an exception for factors outside Microsoft’s reasonable control, i.e. Force Majeure. Force Majeure is an extraordinary event or circumstance beyond the control of the parties, such as a war, strike, riot, crime, or a natural event (e.g., flooding, earthquake, volcanic eruption). The consequence is that the data center is damaged in such a way that databases can’t be recovered from the replicas or the on-site backups. Currently, Windows Azure SQL Database doesn’t keep any off site backups; this is the customer’s responsibility. The SQL Database team has published an excellent guide for Maintaining Business Continuity on SQL Database, and I encourage customers to read this guide.

This blog post will show you how to easily create an offsite backup of your Windows Azure SQL database that is hosted in a Windows Azure data center in another region. This type of back up is easily done using Windows Azure Portal to export a Data-Tier Application logical backup file (BACPAC) to a Windows Azure Storage location in another data center.

Creating a Copy of a Your Windows Azure Database

A BACPAC is a logical backup and does not guarantee transactional consistency. That means if users are writing to your database while the export is taking place, the resulting BACPAC will contain inconsistent data--for example broken foreign key references. Therefore, you need to first create a copy of your database then run the export from the copy.

Here is how to create a copy of your database:

1. Connect to your Windows Azure SQL Database server using SQL Server Management Studio.

2. Select the database you want to export in the Object Explorer, right click and choose from the drop down New Query.

3. In the query window type:

   CREATE DATABASE <destination_database_name> AS COPY OF <source_database_name>

4. When you execute this command, it will return immediately. However Windows Azure Data will still be executing the copy in the background. You can monitor the copy process by using the sys.dm_database_copies and sys.databases views. Issue the following T-SQL:

   SELECT * FROM sys.dm_database_copies;

   SELECT state_desc, * FROM sys.databases;

5. Once the copy completes, you can use the destination database as the source for the creation of the BACPAC. While the BACPAC is generating don’t execute any INSERTS, UPDATES, or DELETES against it.

For more information see Copying Databases in Windows Azure SQL Database

Finding the Data Center

It might have been sometime since the creation of Windows Azure SQL database or someone else created the database and you might not know where it is located. In order to keep your backup in a separate data center from your database, you need to know where the database is located.

1. Login To Windows Azure Portal

2. Now click on the Database button on the lower right.

   

3. In the database main view of the Windows Azure Portal, navigate to the subscription that hosts your database.

4. In the properties view on the right hand side it should show the Region

   

Now that you know where your database is located you can make sure to back up to a different region (i.e. a different data center).

Create a BACPAC backup file Using the Windows Azure Portal

The Windows Azure Portal allows you to create a BACPAC. It will not output the package to your local hard drive, instead it writes to a Windows Azure Blob Storage.

These are the steps for creating a BACPAC from the Windows Azure Portal:

1. Login To Windows Azure Portal

2. Click on the Hosted Services, Storage Accounts & CDN Button on the lower right.

   

3. Once loaded, click on Storage Accounts in the left top navigation.

4. In the main view, choose a subscription and either use an existing Windows Azure storage account or create a new Windows Azure storage account. Make sure that the Windows Azure storage account is in a different region then your Azure database. Copy the access key where you can get at them later (use notepad) and also the Blob Url to your storage account.

5. You must also create a container inside the selected Blob storage for your BACPAC files by using a tool such as Azure Storage Explorer. Install this (or another Blob storage tool) and create a container, for this example we are using a container named: dac.

6. Now click on the Database button on the lower right.

   

7. In the database main view of the Windows Azure Portal, navigate to the subscription that hosts your database, then to the server and finally click on the copied version of the database from the steps above.

8. In the top toolbar click the Export button.

   

9. Fill out the Export Database to Storage Account dialog.

   1. The Login for the Database Export Settings should be your admin login for your database.

   2. The Password for the Database Export Settings should be your admin password for your Azure SQL Database.

   3. The New Blob URL for Table Storage Account should be the Blob Url that you noted down in step 4. Prefix with https://, concatenated with a container name and a file name for the location of the BACPAC. There must be a container, you can’t put the file in the root. There must be a file name, something like this:

      https://myblobstorage.blob.core.windows.net/dac/exportedfile.bacpac

   4. Double check your blob url and make sure you do not have any spaces, uppercase letters, or special characters in the blob url. Take a minute to get it right and it will save you a lot of frustration.

   5. The Access key for Table Storage Account should be the access key that you saved in step 4.

      

10. The process of creating the BACPAC is submitted asynchronously. You will get an immediate message that your job has been submitted to the queue. Then it can take a few minutes to several hours for the export to complete.

    

11. To check the status, click on the server name in the right hand window and then the status button in the top toolbar. Now fill out the dialog with your Windows Azure SQL Database admin login and password.

    

12. If you have done everything correctly, you should see a success message when the BACPAC is finished. You can follow the status of the BACPAC being generated; note that the last update column in the status dialog is UTC.

    

Restoring the BACPAC

I am going to save that for a later blog post.

Summary

With the Window Azure Portal, you can easily create a Data-Tier Application logical backup package (BACPAC) and store it in an offsite Windows Azure Blog Storage account. This will help mitigate data loss from Force Majeure.

{6230289B-5BEE-409e-932A-2F01FA407A92}