SQL Azure Import/Export Service CTP Now Available!

September 10, 2011

New and just launched this week – hosted import and exports! We are happy to announce that DACFx is now available as a SQL Azure cloud service as well. While the client side tools are fully supported and are every bit as important, the new hosted service provides DAC at a new level of convenience to SQL Azure customers. The new service is publically available as a CTP in all SQL Azure datacenters across the world. The service is provided free of charge and no sign-ups or codes are required – just go use it!

The service is designed to directly import and export between SQL Azure and Windows Azure BLOB storage bypassing any need for client side binaries, MSIs or any real work on your part.

To get started using the service, you will need:

  • A SQL Azure server in any datacenter
  • A Windows Azure storage account

The service hosts some public REST endpoints that you use to submit an import or export request. There are a few ways to submit a request:

  1. Use the Windows Azure Portal
  2. Use the reference EXE service client (see next post)
  3. Post HTTP requests directly to the public endpoints

Let’s start with an export. In the video I assume you are starting essentially from scratch:

A couple notes on an export request:

  • Export requests will generate some load on the target database
  • At the beginning of an export request a 0 byte file is written to your storage account

We write a temporary 0 byte file to verify we have write access to the BLOB container you selected. We do not stream your data to your BLOB account during the export process because if the export fails for some reason, you are still charged for storing bits in your BLOB account. We cache your data stream inside the service and the write it out at the end all at once.

Now let’s import a BACPAC back into SQL Azure with the service:

Couple notes on an import request:

  • While the import is in progress, you will actually be able to see your database as an active database
  • We recommend you do not log in to or otherwise modify the database while the import request is in progress

Finally, we do provide some rudimentary status information in this first release. We will add additional details in a subsequent release but for now we can inform you whether your request is Queued, Running, Completed, or Failed.


SQL Azure Backups

September 10, 2011

An obvious question many customers ask is whether you can use import and export (service or client) as a backup mechanism. The answer is yes, but there are few caveats. Please remember that both DACFx and the Import/Export Service are in CTP. Second, ensure you read the notes in the import/export post. Third, the export process is not transactionally consistent. So if you want to ensure your “backups” are transactionally consistent you will need to provide consistency separately.

The current recommended way to provide consistency is to create a copy of the database and export from the copy. Red Gate has released a tool that automates the copy/export process quite nicely. Check out their tool at their site and check out the walkthrough:



DAC and the SQL Azure July Service Release

September 9, 2011

For anyone out there that’s been using SQL Server 2008 R2 (10.50.*.*) versions of DAC either in SQL Server Management Studio (SSMS) or by directly leveraging the redistributable MSIs there’s a slight issue in one of DACFx’s dependencies – Shared Management Objects (SMO) which is used by DACFx to connect to SQL Azure instances. If you are experiencing issues connecting to SQL Azure, you will need to update SMO.


Cloud Service Extravaganza!

September 9, 2011

I’m sure you’ve heard of the “cloud” recently. There are lots of “cloud services” that are popping up in and around SQL Azure so I’d like to take a moment to put a few things into perspective.

Here’s a quick summary for each:

SQL Azure

  • A database engine platform as a service

SQL Azure Data Sync

  • A service which will synchronize data between database engines

SQL Azure Import/Export (New – see the next post!)

  • A service that will load data directly between a SQL Azure database and Windows Azure BLOB storage

So to rationalize in a nutshell, SQL Azure is the core database and there are other services which provide additional value and capabilities. The Data Sync service is designed to replicate data between different SQL databases whereas the import/export service is designed specifically for data loading scenarios to and from a file format.


Example Import/Export Service REST API Usage

September 9, 2011

Just like the client-side reference API implementation we have gone a step further to provide a reference implementation over the REST APIs of the service as well! You can see the EXE and its source files at our CodePlex site here.


DAC Concepts

September 9, 2011

First, there’s the issue of terminology. There are lots of names and concepts that swirl around DAC so it’s important to nail a few of them down before proceeding so that we are on the same page.

DAC

  • The general brand that encompasses all subsequent names and concepts

DACFx

  • The actual framework consisting of several DLL files

DACPAC

  • The file format used by DACFx to represent the full definition of an application (usually schema only). Best analogy: an MSI.

BACPAC

  • The file format used by DACFx to contain the definition of an application as well as its (table) data.

We’ll start with DACFx because it’s the foundation of the DAC house. Quite simply, DACFx is a framework that provides services around Microsoft SQL Server. While there are many services that DACFx provides, the highest order services are typically:

  • Build a DACPAC from a set of T-SQL scripts
  • Extract a DACPAC from a database
  • Deploy a new database from a DACPAC
  • Upgrade an existing database (schema) with a new DACPAC
  • Export a new BACPAC from a database
  • Import a new database from an existing BACPAC

Together, the suite of DACFx services enable:

  1. A database application lifecycle
  2. Schema and data portability

Please note that all of the services have publically available interfaces that can be called without using any GUIs.

The next few posts will walk through the services and how they can help database developers and administrators.


DAC Basics

September 9, 2011

The services DACFx provide revolve around some key concepts and technologies. We’ll walk through these step by step so you can see how these basic concepts can leveraged together to provide advanced services.

Basic Concept 1: The In-Memory Database Model

Central to every DACFx service is the need to build and traverse an in-memory model of a database. DACFx is a client side library and so it communicates with SQL Server like any other SQL client does – over a standard SQL TDS connection. Before DACFx can provide services over a database, DACFx must deeply understand what a database actually is. DACFx has an internal in-memory model that covers all the different database objects, their properties, and state. Database objects are any entity you can create in SQL Server with a “CREATE” T-SQL statement. Examples of objects include tables, views, functions, procedures, indices, and constraints.

Another way to describe the model is to say that SQL Server has a structured physical model for a database. When you create an object in SQL Server, a physical record is written out on disk or disk space is allocated for it. DACFx has an in-memory model that maps to the SQL engine’s model.


The in-memory database model is vital to all DAC operations because it allows DACFx to provide client-side services without having to change or manipulate a real database.

Basic Concept 2: The File Format

DACFx has two associated file formats, the DACPAC and the BACPAC. Both file formats are based on the standard System.IO package format in .NET. Effectively, this means you can modify the file extension of any DACPAC or BACPAC to “.zip” and unzip the contents.

If you crack open a DACPAC you will find some XML files which contain the full schema definition for a database. All database objects are represented through entities in XML. DACFx also stores associated properties about an entity as well as any relationships it has to other entities.

We do not store T-SQL inside the DACPAC! Only the serialized in-memory database model is stored for simple retrieval later on. For example DACFx can quickly read a DACPAC to create an in-memory model of the database defined in the DACPAC. Similarly, DACFx can write out a new DACPAC to disk from an in-memory database model for later use or reference.

    

You’ll see in subsequent sections how this simple but vital capability is used in providing a useful service.


Follow

Get every new post delivered to your Inbox.