SQL Azure Import/Export Service CTP Now Available!

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

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

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.

• SSMS update
  
• SSMS full install
  
• SMO redistributable update
  

Cloud Service Extravaganza!

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

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

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

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.

Development

Now that you know about the DACFx in-memory database model and its associated serialized representation, the DACPAC, it’s time to cover how to generate an in-memory model. There are three ways to create an in-memory model:

1. De-serialize (load) a DACPAC into a model
   
2. Build T-SQL into a model
   
3. Extract a model from a live database
   

Number one was covered in the basics section, number 3 will be covered in the next post, that leaves number two.

Database developers and administrators code in T-SQL so naturally DACFx must provide some way to convert T-SQL into an in-memory model. DACFx provides what is referred to as the compilation unit that allows you to load in arbitrary TSQL scripts into a container along with some other information and compile (or build) an in-memory model from a jumble of T-SQL statements. Once the in-memory model has been created, it can be serialized to disk as a DACPAC. The database projects in Visual Studio 2010 and the upcoming SQL Server Development Tools Code Name “Juneau” follow this workflow exactly: when building a project which is T-SQL they emit a DACPAC.

Here’s a quick example:

Video Coming Soon!

Declarative programming is pretty straightforward: you must declare the existence of an object, and nothing else. For example you can declare that table T1 exists with some set of columns but you cannot alter table T2 or alter table T3. By and large, alter syntax is not supported by the DACFx compilation service and there’s a good reason why. DACFx is not a SQL engine! It does not maintain database state and allow you to perform CRUD operations on an instance of a database, it merely creates an in-memory model of a database from defined (by CREATE statements) objects.

Effectively, this cuts down on the programmatic surface area the developer needs to worry about when authoring a database or making a change to an existing one.

For example:

Video Coming Soon!

The defined database must also be consistent. For example, I can go to a database, create a table T1, create a view V1 that refers to T1 and that’s perfectly legitimate. Of course, the view is useless, but it’s still there nonetheless. If I were to create the same view V1 that refers to a nonexistent table, then the create statement will fail because the dependent object doesn’t exist. Similarly, DACFx compilation validates all of your database object dependencies when being provided with a set of T-SQL scripts and the compilation will fail if it discovers a discrepancy such as in the case above.

DACFx also verifies that your database is contained. Containment in the database context means that the objects defined in the database and any other objects they might depend on are all contained inside the scope of the same database. Containment is an important concept that is best exemplified in SQL Azure.

SQL Azure is a fully contained database model by necessity. All databases in SQL Azure have to be contained because SQL Azure is a multi-tenant environment. In order to guarantee a base level of performance, availability, and security SQL databases have to be scoped to only allow users the ability to consume database level assets. In a nutshell, that’s why many features available today in the SQL Server box product are not currently available in SQL Azure. Over time, these features will be added into SQL Azure with a contained implementation.

The enforcement of database containment by DACFx does present a bit of an issue for on-premise SQL Server customers if they want to leverage any of the uncontained features in SQL Server. As the contained database model expands, DAC will also expand in lock-step so DAC will become more usable for these types of customers with uncontained feature requirements. For SQL Azure customers (or SQL Server customers without the need for uncontained features), the contained model fits the available features and objects so that there aren’t any limitations.

DACFx also allows the developer to set the version number, application name, or description either in the project or programmatically in the CompiliationUnit’s associated properties. You can see that here in Visual Studio 2010:

Video Coming Soon!

We’ll see how these DAC facets surface later on.

Extract

We’ve talked about creating an in-memory model of a database from a DACPAC file and straight from T-SQL, but what about from a database itself? Enter the DACFx Extract service.

The Extract service connects to a database, reads all of its objects and their properties, and then creates an in-memory model of the database. Similar to how the Build service validates the defined objects, the Extract service also checks for consistency and containment. The validation done here will also result in failures if you have a view that refers to a non-existent table same as with T-SQL! Additionally, unsupported or uncontained objects are blocked because these objects are not yet allowed in SQL Azure or the DAC in-memory model. Finally, once the in-memory model is complete and validated, a DACPAC is written to disk. Let’s take a look at how to do this with SQL Server Management Studio:

For a full list of supported objects, please see this link that lists the set of supported objects for DACFx as of SQL Server Code Name “Denali.” Note that every release of DACFx will add object support so check back if there’s an object that’s an issue for you.

Final note, the Extract service will extract your schema from:

• SQL Server 2000
  
• SQL Server 2005
  
• SQL Server 2008
  
• SQL Server 2008R2
  
• SQL Server Code Name “Denali”
  
• SQL Azure
  

Deploy

So far we’ve covered some different methods of creating a DACPAC. Well once you have a compiled or extracted DACPAC the first interesting service you can use is the deploy service. The deploy service will create a database with the specified settings and then create all of the objects defined in the DACPAC inside the new database. Once finished, the database is ‘registered’ (see the next post) as a Data-tier Application. Think of the DACPAC as a database definition MSI and the deploy service as a DACPAC installer.

 

Here’s a quick walkthrough of deploying a DACPAC to a new database:
    

For more information on how deployment is actually accomplished, see the Upgrade post.

 

Final note, the deploy service will create your schema in:

• SQL Server 2005 SP4 and above
  
• SQL Server 2008 SP2 and above
  
• SQL Server 2008 R2
  
• SQL Server Code Name “Denali”
  
• SQL Azure