[This article was contributed by the SQL Azure team.]

Importing and exporting data between on-premises SQL Server and SQL Azure just got a lot easier, and you can get started today with the availability of the Microsoft SQL Server “Denali” Data-tier Application (DAC) Framework v2.0 Feature Pack CTP.  Let’s call this the DAC framework from this point on J.  To learn more about DAC, you can read this whitepaper.

If you’re eager to try it out, go to the SQL Azure Labs page; otherwise read on for a bit to learn more.

There are 3 important things about this update to the DAC framework:

• 1. New import & export feature: This DAC CTP introduces an import and export feature that lets you store both database schema and data into a single file with a “.bacpac” file extension, radically simplifying data import and export between SQL Server and SQL Azure databases with the use of easy commands.
• 2. Free: This functionality will be shipping in all editions of the next version of SQL Server “Denali”-including free SKUs-and everyone will be able to download it.
• 3. The Future = Hybrid Applications Using SQL Server + SQL Azure: Freeing information to move back and forth from on-premises SQL Server and SQL Azure to create hybrid applications is the future of data. The tools that ship with SQL Server “Denali” will use the DAC framework to enable data movement as part of normal management tasks. 

The Data-tier Application (DAC) framework is a collection of database schema and data management libraries that are strategic to database management in SQL Server and SQL Azure.  In this CTP, the new import and export feature allow for the retrieval and restoration of a full database, including schema and data, in a single operation. 

If you want to archive or move your database between SQL Server versions and SQL Azure, you can export a target database to a single export file, which contains both database schema and data in a single file.  Also included are logins, users, tables, columns, constraints, indexes, views, stored, procedures, functions, and triggers.  Once a database has been exported, users can import the file with the new import operation.  

This release of the import and export feature is a preview for fully supported archival and migration capability of SQL Azure and SQL Server databases.  In coming months, additional enhancements will be made to the Windows Azure Platform management portal. Tools and management features shipping in upcoming releases of SQL Server and SQL Azure will have more capabilities powered by DAC, providing increased symmetry in what you can accomplish on-premises and in the cloud.

How Do I Use It?

Assume a database exists that is running within an on-premises SQL Server 2008 R2 instance that a user has access to.  You can export the database to a single “.bacpac” file by going to a command line and typing:

DacImportExportCli.exe -s serverName -d databaseName -f C:filePathexportFileName.bacpac -x -e

Once exported, the newly created file with the extension “.bacpac” can be imported to a SQL Azure database if you type:

DacImportExportCli.exe -s serverName.database.windows.net -d databaseName -f C:filePathfileName.bacpac -i -u userName -p password

A DAC database running in SQL Server or SQL Azure can be unregistered and dropped with:

DacImportExportCli.exe -s serverName.database.windows.net -drop databaseName -u userName -p password

You can also just as easily export a SQL Azure database to a local export file and import it into SQL Server.

How Should I Use the Import and Export Features?

It’s important to note that export is not a recommended backup mechanism for SQL Azure databases. (We’re working on that. so look for an update in the near future).  The export file doesn’t store transaction log or historical data.  The export file simply contains the contents of a SELECT * for any given table and is not transactionally consistent by itself.

However, you can create a transactionally consistent export by creating a copy of your SQL Azure database and then doing a DAC export on that.  This article has details on how you can quickly create a copy of your SQL Azure database.  If you export from on-premise SQL Servers, you can isolate the database by placing it in single-user mode or read-only mode, or by exporting from a database snapshot.

We are considering additional enhancements to make it easier to export or restore SQL Azure databases with export files stored in cloud storage – so stay tuned.

Tell Us What You Think

We’re really interested to hear your feedback and learn about your experience using this new functionality.  Check out the SQL Azure forum and send us your thoughts here.

Check out the SQL Azure Labs page for installation and usage instructions, and frequently asked questions (FAQ). 

 

Thanks!

-Steve