Backup strategies for Sql Azure Federations

March 26th, 2012 Add Your Comments

One of the greatest features of Sql Azure is the built in redundancy. For each Sql Azure database, besides the live instance, at any moment there are at least two replicas, one of them being transactionally consistent with the live one. This means that data stored in Sql Azure databases is pretty much bullet proof against hardware failures. In fact, the Azure team was so confident on the reliability of their infrastructure’s redundancy that when they initially launched Sql Azure no backup option was available for customers.

However, hardware failures are just one reason for backups, the others being application or human error or even malicious attacks. At the moment there are a few different ways to back up data for individual Sql Azure instances, each of them with its strengths and weaknesses. Things get more complicated when it comes to backing up federated databases but, as I will detail through the article, there are solutions for that scenario as well.

Backup options for standalone databases

As the title of the article implies, I am going to focus on the backup strategies for federated databases. However, in order to put things into context, I think it would be best to begin by quickly iterating through the available options on standalone databases.

 The built in Restore option

This would probably be the easiest and most effective way to protect your data against human error. The biggest problem with it is that it is not yet available. The Azure team has planned to deliver it in one of their future releases (details here), but no date is yet provided on when this will happen.

In just a few words, the Restore functionality will allow you to restore databases to a specific point in the past within the last 14 days. As it turns, Sql Azure is already keeping that 14 day backups info for each database, but at the moment that is only available internally. You can find more info about this functionality starting with minute 40 in this presentation.

At this point there is no hint weather this feature will also be available for federated databases. From the above mentioned presentation, it seems that the restore option will only allow restoring the database as a new instance. That might not be very convenient when restoring a federation root or a federation member since the restored database might have the federation links broken.

 Database copies

Sql Azure makes it very easy to create copies for databases using the AS COPY OF clause of the CREATE DATABASE statement CREATE DATABASE statement:

CREATE DATABASE destination_database_name 
AS COPY OF [source_server_name].source_database_name

The newly created copy will be transcationally consistent with the source databases at the moment the copy command will end. Depending on the size of the source database, the copy command might take a long time to complete. After the command is finished the two databases will be totally independent one from the other (that is there is no continuous replication between them, which is good when performing the operation for backup purposes).

One important thing to consider is that each copy will be treated and charged as a regular database. This means that if you have 3 copies of a database made at different intervals, you will pay for 4 databases. This makes copy not ideal for periodic backups.

Also, database copy is not available for federated databases.

 The Import/Export functionality

This allows exporting and importing databases to and from Data-Tier Application Packages (DACPAC). DACPACs can be used by both Sql Azure and on premises Sql Server databases. The Import/Export functionality is available in multiple forms: a REST service that allows Importing/Exporting Sql Azure databases with the BACPACs stored on the Azure Blob Storage ( details here) and a client side framework (details here). This makes the Export/Import very easy to include in automated processes.

One downside to the Export functionality is that it is not transactionally consistent and as a consequence it might generate BACPACs with data integrity problems. This wouldn’t make it the ideal backup tool. One way to get around the problem is by making the backup in two steps. First create a copy of the database, which when finished would be transactionally consistent, and then make an export of the copy. After that the copy can be dropped. This will involve however, that you will have to pay for one day usage of the copy database (the minimal time amount metered by Sql Azure).

RedGate offers a free Sql Azure backup tool which is based on the copy and Export/Import functionalities but with an intuitive and easy to use user interface.

Unfortunately, Import/Export is not available on federated databases. This forum discussion suggests that export would be possible on federation members, the only downside being that when imported back from the generated BACPACs, the databases would only be imported as standalone instances. From my point of view even that would be useful (since data would never be lost), but unfortunately it doesn’t seem to work. When I tried to test it, it failed with the following exception:

Cannot extract a DAC from database DbName because the database has federations. Dac does not support federated databases.

 Sql Azure Migration Wizard

Is a very useful application that was designed to help migrating Sql Server databases to and from Sql Azure. The Sql Azure Migration Wizard is open source and available on CodePlex. It helps exporting databases to packages which can then be imported to recreate them. This means that besides its main purpose of a migration tool it can also be used for backup / restore operations.

An exported package is made out of a script file containing the Transact-SQL DDL statements that will generate the database’s schema and of BCP generated files for the database’s data. BCP is a tool that comes as part of the Sql Server Command Line Utilities and which helps importing and exporting the content of Sql Server tables to data files.

The Migration Wizard can be used with both federated and standalone Sql Azure databases.

Backup / restore strategies for federated databases

When it comes to federated Sql Azure databases, the backup options are much more limited compared to the ones for standalone databases. As mentioned above, the only option that currently works for federations is the Sql Azure Migration Wizard. Below, I will try to detail the process of using it for both manual and automatic backups.

 Manual backup using the Sql Azure Migration Wizard

When it comes to backing up standalone databases things are pretty much straight forward. You use the backup tool to export the database to a backup package. Later, using the backup package, you can recreate (restore) the database to the same state as it was before just before the backup operation.

By its nature, a federated database is more complicated that a standalone one. It is made out of multiple instances which represent the root database and the members of the different federations (if you are not familiar that, check the Federation Architecture section in this article). At the moment, there is no way to backup a federated database as an atomic unit. This means that there you cannot make an export from which at a later point you would be able to reconstruct the entire structure of a federated database. Instead, what you can do is backup individual instances from the federation (be them federation members or the root database).

From the backup’s perspective, the Sql Azure Migration Wizard is no different when it comes to exporting a standalone database or an instance inside a federation. The restore procedure is a bit trickier for federations, but that will come a bit later in the article. For now, below is an overview of the procedure of exporting a database with the Migration Wizard.

1. Launch the Sql Azure Migration Wizard and on the first screen choose the Migrate Sql Database option and click next.

Wizard start screen

2. In the next screen enter the connection parameters for your federated Sql Azure database. Make sure to check Sql Azure Federation as the Server Type. In the Specify Database area you will have to enter the name of the root database.

Wizard enter credentials

3. In the next screen you will have to choose the instance which will be exported. For the purpose of this article I have been using the federated version of the Adventure Works sample database available for download on CodePlex. By default, the sample will setup for you the root database and a federation created around the Customer table called CustID. During my tests I have made a split at the ID of 10000 on the CustID federation. This resulted in a total of 3 databases which are visible below. The Sql Azure Migration Wizard can be used to export any database, be it the root or a federation member.

Wizard select database

4. The next screen is just the confirmation before starting the export. Inside it you will also have the chance to choose any advanced options (also available in the Migration’s Wizard config file). For the purpose of my tests, the default values were just fine.

5. The last step is to actually generate the script and the data files. Depending on the size of your database and the connection speed between your PC and the Azure data center that hosts the database, this step can take a while. If errors are encountered during the export process they will be output in the Results Summary tab. In the SQL Script tab you will have the generated script. The script together with the BCP generated data files represents the actual backup, so be sure to remember saving it. Speaking of the data files, by default they will be saved in ” c:\SQLAzureMW\BCPData” (you can configure this via the Migration Wizard’s config file).

Wizard summary

 Manual restore using the Sql Azure Migration Wizard

While the backup operation is rather straight forward, the restore is a bit tricky and the procedure to follow would be strongly dependent on the situation that lead to the restore. In many cases you will need to dive inside the generated SQL file and change things manually.

One very important thing to keep in mind is that if a database inside the federation is lost it will first need to be recreated inside the federation and only after that the Migration Wizard can be used to restore the schema and data.

If the root database is lost, you will have to manually recreate it and manually recreate all the federations for it, after which an import with the Migration Wizard should be performed to recover the root’s schema and data. After this first step, each individual federation will only have one member. The first thing to do on all the federations is to use the Migration Wizard to recreate their schema. One thing to keep in mind is that in the DDL script generated by the Migration Wizard there will be no FEDERATED ON clauses (at least this is the case in the current version 3.8.6) and you will have to add those clauses manually where needed. The last things to perform will be importing the federations’ data and make any necessary splits.

If just a federation member is lost, you will first need to recreate it using a split command and after that restore its data with the Migration Wizard (schema will be automatically created during the split).

A much easier to handle scenario is when only some data inside a database instance is lost. Inside the Sql Azure Migration Wizard generated script, the data restore is the last step and is represented by the commands starting with BCPArgs. When performing data restores only, all the commands in the script except the BCP related ones will have to be deleted.

Also, BCP will fail to insert data that already exists (it doesn’t perform overrides), so you will also need to manually add delete statements for all tables that are going to be populated, just like in the example below:

delete from [Sales].[Customer]
delete from [Sales].[SalesTerritory]

-- BCPArgs:10:[Sales].[SalesTerritory] in "c:\temp\bkp\23-March-2012 1438\Sales.SalesTerritory.dat" -E -n -b 10000 -a 16384
-- BCPArgs:701:[Sales].[Customer] in "c:\temp\bkp\23-March-2012 1438\Sales.Customer.dat" -E -n -b 10000 -a 16384

With that script in place, the procedure to restore data using the Sql Azure Migration Wizard would be like the one below.
1. Start the Migration Wizard and choose Run TSQL without Analyzing -> TSQL File and supply your script file.
2. Select the target serer. Just like when backing up, choose the Server Type as Sql Azure Federation.
3. Select the particular instance on which you want to make the restore and click next.
4. The last window will display the status of the operation.

 Automatic backups using the Sql Azure Migration Wizard

The Sql Azure Migration Wizard has a couple of command line versions that can be used for automated processes. The command line tool for backups is called SQLAzureMWBatchBackup and is available for download on the Migration’s Wizard download section on CodePlex. There you can also find the documentation for it.

However, even with the command line tool, there are still a few challenges for implementing an automated process.

First of all, backups should be launched from an Azure worker or VM role. This way data transfers will only happen inside the Azure datacenter so you will not be charged for them. Also the backup speed will be optimal.

As the documentation states, the Sql Azure Federations Wizard is dependent on Sql Server 2008 R2 SP1 bits. This is rather vague but I have identified those bits to be just the Shared Management Objects and the Command Line Utilities. Both of them can be freely downloaded as part of the Sql Server 2008 R2 SP1 Feature Pach available here. You will also need to read the on page documentation, because the two components have some dependencies which also need to be installed. In total six packages need to be installed, after which the Migration Wizard will work perfectly.

Another challenge is getting the names for all database instances that need backing up. For the federation root things are quite simple, since you know its name upfront. To get the names for all the federation instances you will need to run a couple of TSQL scripts. First of all the script below will return all federation instances inside a federated database:

SELECT, fmd.distribution_name, fmd.member_id, range_low
FROM sys.federations fed
JOIN sys.Federation_distributions dis ON dis.federation_id = fed.federation_id
JOIN sys.federation_member_distributions fmd ON fmd.federation_id = fed.federation_id
ORDER BY, range_low

Then, for each of the returned rows, the instance name will be returned by this script:

USE FEDERATION CustomerFederation(cust_id=<range_low>) WITH RESET, FILTERING=OFF

The last thing to solve would be that the Migration Wizard only generates the export packages on the local file system. You would have to implement additional functionality that would take those packages and move them to a durable storage like the Azure Blob Storage.

Final thoughts

In this article I tried to give an overview on the current strategies and some of the challenges related to backing up data for Sql Azure Federations. One obvious thing is that while backups are indeed possible they are a bit more complicated than for standalone Sql Server or for Sql Azure databases. Sql Azure Federations are offering endless scalability and as consequence new possibilities for business to develop in directions which were not possible so far, but it also brings a slightly more complicated administration and maintenance process.

Another thing to keep in mind is that while backing up is possible and a must for critical data, applications should always be built robust enough so that they reduce to a minimum the risk of losing data. It is always best to take all the measures to prevent the events that would require a restore.

External references

Business continuity in Sql Azure
Cihan Biyikoglu talks on the MSDN forum about Sql Azure Federations backups
George Huey talks about scaling out with federations and demonstrates using the Sql Azure Migration Wizard

Comments are closed.