Windows Azure Backups with Bacpac – Automating Exports with Data Sync enabled (Part 4)

Here comes the final installment of my azure backup series.  This time we’ll be looking at doing backups on databases with Data Sync enabled.

Since I originally wrote the first post and made plans to do the four posts in the series, Microsoft has since fixed the issue with restoring a database with Data Sync enabled.  Best as I can tell, there was an issue with merge statements added as triggers to all the tables.  There are various reasons why you’d want to perform backups with all the Data Sync data, but the main reason is that in the event you restore the database you’ll have a full set of data available (even though it might be stale) and not have to wait for Data Sync to restore the data.

My solution presented below strips out all of the Data Sync data, so the resulting backup is only your production data and not the data that is most likely stored on-site (in your on premises servers).  Removing the data, could potentially make the backup/restore faster (really depends on amount of data) and more importantly makes the Data Sync restore a lot quicker.  In my tests with a bit less than 1gb of data, perform the initial Data Sync with the stale data took > 8hrs and still did not complete.  In comparison, a database with Data Sync data stripped out, resulted in a restore of < 1hr.  In the event of a catastrophic failure, an extra hour of delay for up to date information is my preference.

Backup With Data Sync

The function itself is quite long ~150 lines of code, but essentially it goes through and removes tables, triggers, types and schema added when you provision a database.  The link below will take you right to the line where the function starts.

https://github.com/anlai/AzureDbBackup/blob/master/AzureBackup/AzureStorageService.cs#L236

In the service I’ve written, the following function creates a copy of the database, removes Data Sync objects, exports the database to blob storage and drops the temporary copy.  It can be tedious to do it yourself since you have to sit there and wait for the copy operation and export operations which can take a bit of time.

public string BackupDataSync(string database, out string filename)
{
    var srcDatabase = database;
    var tmpDatabase = database + "Backup";

    var masterDbConnectionString = string.Format(ConnString, _serverName, "master", _sqlUsername, _sqlPassword);
    var tmpDbConnectionString = string.Format(ConnString, _serverName, tmpDatabase, _sqlUsername, _sqlPassword);

    // make a copy that we can alter
    CreateCopy(srcDatabase, tmpDatabase, masterDbConnectionString);

    // remove the datasync tables/triggers/sprocs
    RemoveDataSync(tmpDbConnectionString);

    // export the copy to blob storage
    var reqId = Backup(tmpDatabase, null, out filename);

    // keep checking until the export is complete
    do
    {
        var response = GetStatus(reqId);

        if (response == "Completed") break;

        // wait 30 seconds before checking again
        Thread.Sleep(30000);

    } while (true);

    // drop the temporary srcDatabase
    CleanupTmp(tmpDatabase, masterDbConnectionString);

    return reqId;
}

The last bit of getting this to work is to simply initialize the class, call the export and then to perform blob cleanup.  You will want to change the DacServiceUrl parameter to match the data center you’ll be using.  The current urls are coded into the sample under the DacServiceUrls class, so you can use the following options (so long as the urls don’t change):

  • DacServiceUrls.NorthCentralUs
  • DacServiceUrls.SouthCentralUs
  • DacServiceUrls.NorthEurope
  • DacServiceUrls.WestEurope
  • DacServiceUrls.EastAsia
  • DacServiceUrls.SoutheastAsia
var service = new AzureStorageService(
    ConfigurationManager.AppSettings["AzureServerName"],
    ConfigurationManager.AppSettings["AzureUserName"],
    ConfigurationManager.AppSettings["AzurePassword"],
    ConfigurationManager.AppSettings["AzureStorageAccountName"],
    ConfigurationManager.AppSettings["AzureStorageKey"],
    ConfigurationManager.AppSettings["AzureBlobContainer"],
    DacServiceUrls.NorthCentralUs	
    );

// perform the backup
string filename;    // filename generated for the bacpac backup
service.BackupDataSync("DatabaseName", out filename);

// cleanup the storage container
service.BlobCleanup();

Azure Backup Sample Application

I’ve written a sample application, that is a basic console application for testing purposes.  You can download the source from my github project AzureDbBackup.  It is however missing the AppConfig file (mostly because I didn’t trust myself to not check-in the credentials for my Azure account.  So you’ll need to add it in, you can copy and paste from below and fill in your information:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appsettings>
    <add value="server.database.windows.net" key="AzureServerName"></add>
    <add value="server user name" key="AzureUserName"></add>
    <add value="password" key="AzurePassword"></add>
    <add value="blank" key="AzureStorageAccountName"></add>
    <add value="blank" key="AzureStorageKey"></add>
    <add value="blank" key="AzureBlobContainer"></add>
  </appsettings>
</configuration>
  • AzureServerName – The address to your Azure SQL instance.
  • AzureUserName – Username to log into your Azure SQL instance.
  • AzurePassword – Password to log into your Azure SQL instance.
  • AzureStorageAccountName – Storage Account Name
  • AzureStorageKey – Storage key (primary or secondary) to access storage account.
  • AzureBlobContainer – Container inside the above storage account.

My recommendation is that you give the console run a shot first before integrating it into your application.  If you have any suggestions/fixes, I’m happy to integrate them.

Integrating into Your Application

Sure you can run the above console application on an on-premises server and use windows Task scheduler to run it every night.  Or you can integrate it into something running on Azure to run the code on a schedule for you.  If you are running your application on more than  one instance (you probably don’t want to do it on your app), but maybe in a worker role.  But essentially the same integration steps should apply.

  1. Take the AzureStorageService.cs file and drop it into your application.   Or you can download the solution and build the class library and pull the dll into your project (doing it this way will also give you the dac service urls for the data centers).
  2. Next you’ll have to setup a scheduler to perform the backups on a regular interval.  We’re used the Quartz.Net Job Scheduler to run it on a nightly backup job.  Below is a sample setup of a scheduled job using the Quartz scheduler.
  3. It should do it’s magic every night, and you can collect the kudos.
public class DatabaseBackup : Job
{
    public static void Schedule()
    {
        var jobDetails = JobBuilder.Create().Build();

        var storageAccountName = ConfigurationManager.AppSettings["AzureStorageAccountName"];
        var serverName = ConfigurationManager.AppSettings["AzureServerName"];
        var username = ConfigurationManager.AppSettings["AzureUserName"];
        var password = ConfigurationManager.AppSettings["AzurePassword"];
        var storageKey = ConfigurationManager.AppSettings["AzureStorageKey"];
        var blobContainer = ConfigurationManager.AppSettings["AzureBlobContainer"];

        var nightly =
            TriggerBuilder.Create()
                          .ForJob(jobDetails)
                          .WithSchedule(CronScheduleBuilder.DailyAtHourAndMinute(1, 30).InPacificTimeZone())
                          .UsingJobData("StorageAccountName", storageAccountName)
                          .UsingJobData("AzureServerName", serverName)
                          .UsingJobData("AzureUserName", username)
                          .UsingJobData("AzurePassword", password)
                          .UsingJobData("AzureStorageKey", storageKey)
                          .UsingJobData("AzureBlobContainer", blobContainer)
                          .StartNow()
                          .Build();

        var sched = StdSchedulerFactory.GetDefaultScheduler();
        sched.ScheduleJob(jobDetails, nightly);
        sched.Start();
    }

    public override void ExecuteJob(IJobExecutionContext context)
    {
        var storageAccountName = context.MergedJobDataMap["StorageAccountName"] as string;
        var serverName = context.MergedJobDataMap["AzureServerName"] as string;
        var username = context.MergedJobDataMap["AzureUserName"] as string;
        var password = context.MergedJobDataMap["AzurePassword"] as string;
        var storageKey = context.MergedJobDataMap["AzureStorageKey"] as string;
        var blobContainer = context.MergedJobDataMap["AzureBlobContainer"] as string;

        // initialize the service
        var azureService = new AzureStorageService(serverName, username, password, storageAccountName, storageKey, blobContainer, DacServiceUrls.NorthCentralUs);

        // make the commands for backup
        string filename;
        var reqId = azureService.BackupDataSync("PrePurchasing", out filename);

        // clean up the blob
        azureService.BlobCleanup();
    }
}

Links:

Azure Backups Series: