Project Description

This tool is used for backup functionality on SQL Azure database and tables in a periodical timeline. The code can deployed as a Worker role with Azure or on-premise environment and the backup file can stored to blob storage or to file system.

Background

Backup and Restore option was one of important feature was missing with SQL Azure. It was available with SQL Azure using copy database option before, but now the same can be done using Import and Export (currently in CTP) option provided with Management portal.

Below are some of options available for importing and exporting an SQL Azure databases today.

  1. Copy database (For coping SQL Azure database to another database in SQL Azure environment)
  2. Import and Export option with Management portal (Currently in CTP) – for exporting the database as bacpac file into blob storage and can be imported.
  3. Using Data Tire Application (DAC) framework – a command line tool for exporting database as .bacpac file and for importing the same.
  4. Using BCP utility – a command line tool for exporting a table data into a text or xml format. The exported file can be used for importing.

Even though all these options are available for backup and restore the database, it requires some manual efforts. Restoring the database manually can be acceptable, but manually doing daily backup may not be acceptable solution. So, we required to automate the backup operation with SQL Azure databases.

Automating SQL Azure database backup:

I am using a Worker role for automating the SQL Azure backup operation. The worker role can run either in on-premise server or can be hosted on Azure environment.

This implementation uses DAC framework released with the SQL Server next release “Denali” (Currently in CTP 3.0) for backup the database. So to run this worker role, the system must have the following software installed.

  1. Install .NET 4.0 run time
  2. Install SQLSysClrTypes.msi
  3. Install SharedManagementObjects.msi
  4. Install DACFramework.msi
  5. Install SqlDom.msi
  6. Install TSqlLanguageService.msi

The first three installations must be done on the same order and after that the other three can be in any order. I have tested installing in the same order, so please try installing in the same order.

If your system already installed any other CTP version of SQL Server “Denali”, this installation will have some issue. So better to uninstall the older (Denali) CTP version and try with this new version. But installing SQL Server “Denali” CTP versions will not impact to any other SQL Server version such as SQL Server 2008/2008 R2, 2005.

Note: The package files provided in the download section is ready to deploy in Azure environment.  The deployable package contains startup Power Shell script for installing the above components automatically. So no action required while deploying in Azure environment, but the on-premise server must have the above installations (Denali CTP 3.0) required on the server before running the code.

Review the Documentation tab for Steps for deploying package to Azure Environment.