Introduction

The Data-tier Application Framework from Microsoft helps database developers to deploy and manage database structures. Lately it has been introduced in SQL Server 2008 R2 and Visual Studio 2010. SQL Azure supports DAC to a certain extent. For this article it is important to know that SQL Azure does not only support so called DACPACs (zipped file containing all information about a database's structure) but also BACPACs (DACPACs + data). You can easily export any SQL database (including SQL Azure) and import the data into another SQL database (including SQL Azure). At the time of writing this sample BACPACs for SQL Azure were available as a CTP version. Check out SQL Azure labs to learn more about it.

At the time of writing SQL Azure does not offer any kind of restore functionality. Microsoft has already announced that there will be powerful backup/restore mechanisms in SQL Azure in the near future (check out this TechEd talk for further details). Until these great features become available we need a different method for backing up our databases. BACPACs are an option.

This sample should demonstrate how you could implement a worker process that creates a transactional snapshot of your databases, creates a BACPACK file from it and puts this file into blob storage. From there you can grab it and create e.g. an on-premise copy of the database. You could also re-create this snapshot at a later point in time (even in a different data center).

To make this sample valuable even after SQL Azure's backup/restore features will have come out I take the chance to demonstrate additional points that may be of general interest:

Building the Sample

To build and run the sample you have to download the source code and change the configuration information in the .cscfg file appropriately.

Additionally you have to download the BACPAC prerequisites from the SQL Azure labs website and from SQL Server 2008 R2 feature pack). You have to put these files into the DACFramework folder of the solution. In summary you need to get the following files:

Description
To be able to create BACPACs with the DacImportExportCli tool we have to install all prerequisites in a role startup task. Here is the XML snippet that you have to add to your .csdef file to register the task:
XML
Edit Script
<Startup> 
  <Task commandLine="DACFramework\install_dac.cmd" executionContext="elevated" taskType="simple" /> 
</Startup> 
 
 
The tasks references a shell script that does all the installation magic:
Windows Shell Script
Edit Script
REM Script to install components for DAC 
 
REM Cleanup old log files (just in case) 
erase DACFramework\install*.txt 
erase DACFramework\install_SQLSysClrTypes.txt > DACFramework\install_log.txt 
 
REM Install DAC components using MSIEXEC 
msiexec /i DACFramework\SQLSysClrTypes.msi /qn /l* DACFramework\install_SQLSysClrTypes.txt 
if ERRORLEVEL 1 goto InstallError 
msiexec /i DACFramework\SharedManagementObjects.msi /qn /l* DACFramework\install_SharedManagementObjects.txt 
if ERRORLEVEL 1 goto InstallError 
msiexec /i DACFramework\DACFramework.msi /qn /l* DACFramework\install_DACFramework.txt 
if ERRORLEVEL 1 goto InstallError 
msiexec /i DACFramework\SqlDom.msi /qn /l* DACFramework\install_SqlDom.txt 
if ERRORLEVEL 1 goto InstallError 
msiexec /i DACFramework\TSqlLanguageService.msi /qn /l* DACFramework\install_TSqlLanguageService.txt 
if ERRORLEVEL 1 goto InstallError 
msiexec /i DACFramework\SqlCmdLnUtils.msi /qn /l* DACFramework\install_SqlCmdLnUtils.txt 
if ERRORLEVEL 1 goto InstallError 
 
REM Success 
echo INSTALLATION SUCCESSFULLY COMPLETED >> DACFramework\install_log.txt 
goto EndOfScript 
 
:InstallError 
REM Handle installation error 
echo INSTALLATION ERROR (ERRORLEVEL=%ERRORLEVEL%) >> DACFramework\install_log.txt 
goto :EndOfScript 
 
:EndOfScript 
 
 
After this startup script has run we can use the DAC tools to create the snapshot. I tried to document the sample worker as good as possible. Here is how the process works:

Source Code Files