A First Look at Data-Tier Applications 2.0

The next version of Data-Tier Applications is version 2.0. You can get a CTP of it today; where the CTP is located really gives away the game. It's located at SQL Azure Labs, listed under SQL Azure "Import/Export". The writeup on this page identifies a few interesting things:

1. It's a preview of the Denali Data-Tier Application Framework (although its not the version in Denali CTP1).
2. It's meant to be "a solution for archival and migration scenarios".
3. (Easy implication on my part) – they're concentrating on SQL Azure "database movement" for now. Although you can use it with SQL Server, coverage includes exactly what's in SQL Azure.

With respect to the second point listed, DAC 2.0 supports two new operations: import and export. This import and export is on an entire database at a time, as opposed to say, BCP, which is table-by-table. It also imports/exports data and schema at once, unlike BCP which needs a schema to be defined ahead of time. This import/export is NOT backup/restore. There's no point-in-time recovery, and, if the export is executed on an "active" database, there's no guarentee that its transactionally consistent. It's just of copy of all of the schema and data into one container called (are you ready for this?) a .bacpac. The data is actually stored row-at-a-time, in JSON (Javascript Object Notation) format.

Better than that (depending on your point of view), DAC 2.0 adds support for additional database objects and types, including ALL of the database objects supported by SQL Azure Database (but not objects not supported by SQL Azure, like Service Broker objects). Objects added in the CTP include:

Geometry, Geography, and HierarchyId data types
Spatial Indexes
Security-Related Objects
  Role Memberships
  Logins and Users (I don't see Denali's Contained Database logins, but I bet these will be added eventually)

The CTP offering consists of five components: new versions of the four components in earlier DAC versions (DAC framework, SMO, SQLClrTypes, T-SQL LanguageService) and one new component: the T-SQL ScriptDom. The T-SQL ScriptDom is a component that generates SQL Server version-specific scripts for create and alter/update.

When you install DAC 2.0 CTP (the components actually show up in Add/Remove Programs as e.g. "Microsoft SQL Server "Denali" Data-Tier App Framework (CTP 2.1)", there are four such components), it does NOT replace existing DAC components. It installs side-by-side in the GAC and in the "110" subdirectory SQL Server in program files. I didn't install it on Denali CTP1 system, but installed it on an OS with SQL Server 2008 R2. It doesn't affect any GUI components, and existing VS 2010 (RTM or SP1) and SQL Server 2008 R2 (RTM or SP1) DAC 1.0 or 1.1 functionality continues to work as it used to.

Because there's no GUI components or new VS Projects for now, it comes with a command-line utility. Unless you program it using the object model, you interact using the utility for now (although the code for the command-line utility is available on CodePlex). The utility, DACImportExportCli.exe (sounds like they'll implement a Windows Azure service over it, no?), contains at least one exciting feature: BACPACs can be imported/exported from/to Windows Azure Blob storage. You just name your Windows Azure storage server and cert as command-line parameters and "it all happens in the cloud" (and no, I'm not contracting to write advertising slogans, thanks).

So, to wrap this up, both SQL Azure Database and SQL Server Denali work with the concept of the portable, movable, database. It's meant to be unencombered by server-level (or MSDB-level) objects, and supports a subset of database objects that increases with each new SQL Azure Database Service Release. And the vehicle for wrapping these databases up and moving them around is the DAC. In addition to working with multiple SQL Server versions, it also encapsulates the differences between applying database schema changes to disperate versions. As an example, at TechEd 2011, Adrian Bethune illustrated how to change the data type of a clustering key on SQL Server Denali and SQL Azure Database. It's different on Azure because you can't simply drop and re-create the clusting key, so DAC accomplishes it differently on each instance. Very interesting…


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.