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
Statistics
Synonyms
CursorParams
Security-Related Objects
  Permissions
  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...

@bobbeauch

So DAC 1.1 has been out for a while (since March 2, 2011) now. The big change is that the DAC "upgrade" process is now an in-place upgrade rather than a side-by-side upgrade. As far as I can see, you couldn't do a side-by-side upgrade in 1.1 if you wanted to. AFAIK, in-place upgrade is the only new feature in 1.1, there's no additional database object support. Guess they listened. And fast.

As a starter, realize that you need to change the tools to hook them into the appropriate GUIs. DAC 1.1 requires Visual Studio 2010 SP1 for the new in-place upgrade functionality. SQL Server 2008 R2 folks can upgrade to DAC 1.1 by installing the feature pack. This functionality will be available in the upcoming SQL Server 2008 R2 SP1 as well. You can use the upgraded SMO libraries to obtain the new functionality in PowerShell as well.

However, as you might expect, while DAC Framework 1.1 supports packages created using the earlier DAC Framework 1.0, clients still running DAC Framework 1.0 cannot process DAC packages created by using DAC Framework 1.1.

In addition, in the same timeframe, but not as part of the upgrade to 1.1, you can install DACs into SQL Server 2008 SP2 and SQL Server 2005 SP4 instances, but only using the APIs directly. The appropriate history tables are kept in MSDB. SSMS for SQL Server 2008 SP2/2005 SP4 doesn't support it in the GUI, but a SQL Server 2008 R2 version of SSMS connected to SQL Server 2008 SP2/2005 SP4 would support it.

SQL Server Denali CTP1 installs with a different version of DAC that appears to support 1.1 functionality, it doesn't support all the 2.0 features, but they'll likely be supporting DAC 2.0, which is currently in beta. We'll just have to wait and see about that. And the upcoming SQL Server Denali tool known as Project Juneau will support it, as least as far as I can gleen from the information presented at the TechEd US sessions in May.

The DAC 1.1 feature comes as four MSI files and will replace versions of:

DACFramework.msi
  Microsoft.SqlServer.Management.Dac.dll (version 10.50.1750.9)
  Microsoft.SqlServer.Management.DacEnum.dll (version 10.50.1750.9)
  Microsoft.SqlServer.Management.DacSerialization.dll (version 10.50.1750.9) 
SharedManagementObjects.msi
  All major components of SMO updated to version 10.50.1750.9
SQLSysClrTypes.msi
  Microsoft.SqlServer.Types updated to version 2009.100.1750.9
  SqlServerSpatial.dll (non-.NET part of SQL Server Spatial feature, looks like its installed side-by-side)
TSqlLanguageService.msi
  ManagedParser.dll (version 10.50.1750.9)

This post has also gone a little long (again!), so I'll cover DAC 2.0 in a separate post. Later.
 
@bobbeauch

About a week or so ago, I read a request for information about a SQL Server database management feature known as Data-Tier Applications, abbreviated as DAC (apparently someone realized that the DTA abbreviation was already "taken" in SQL Server (Database Tuning Advisor) but not that DAC was too (Dedicated Admin Connection)). Data-Tier Applications is a feature that was introduced as part of Visual Studio 2010 and is supported in SQL Server Management Studio for SQL Server 2008 R2. It is a fairly (understatment here) controversial feature, that provides a file (.dacpac) that uses an XML format that encapsulates descriptions of database objects, suitable for deployment.

The .dacpac could be thought of as the ".msi file" of database deployment. The basic premise is that you can produce a .dacpac from an existing database or create one with Visual Studio. Then you can point at the instance node in Object Explorer pane of SSMS and choose "Deploy Data-Tier Application". The underlying infrastructure produces and runs DDL to set up the database and underlying database objects. You can also automate all the use cases using PowerShell, as its based on SMO.

DAC-deployed databases keep deployment history information in MSDB (in master if you're using SQL Azure, as Azure has no MSDB). DAC-deployed databases also integrate with SQL Server 2008 R2's Utility Control Point feature.

Most (well, all that I've seen) articles cover version 1.0 of the DAC feature, which has the following limitations:
1. Didn't support all database objects, not even all the ones in SQL Azure Database.
2. It didn't address server-level objects (except for logins) like SQL Agent jobs, at all.
3. The "upgrade a database" action didn't upgrade in place; instead, it made a copy of the database set up the new DDL and copied the data in existing tables "the best it could". Then renamed the current database to old and new database to current. The "best it could" could result in data loss, and reading the docs (and runtime warnings) informed you of where this could occur. You would (naturally) need to back up your database after a DAC "upgrade", as your transaction log recovery chain (and anything else in the old database would be renamed away. Although they do keep the old database around for those instances where you need to (and are able to) scavenge from it.

Folks correctly target this as a SQL Azure Database-centric (but not only) feature. You can support other objects with manual pre-and-post deployment scripts. Moving just a database and logins directly relate to the SQL Azure product structure. But there is an upcoming Denali feature, Contained Databases, that also go by the concept that everything should be contained inside the database, to simpify moving a database from one SQL Server instance to another. This would another place where DAC will likely come into play.

Back to my original premise...since the original DAC annoncement and general upsetness by the SQL Server DBA community, the folks at DAC have listened to your "suggestions" (not the "off with their head" ones, just the constructive ones). And DAC has already released a 1.1 version. And a 2.0 beta version is also available. Because this posting is getting a bit long, I'll cover them in detail in the next entry. But for now, realize that if you're reading an article about DAC, its most likely about DAC 1.0. And things have changed. You can get version 1.1 here. It fixes the "upgrade" scenario, point number 3 in the list above.

@bobbeauch

Last week I did a talk at SQLConnections on SQL Azure Database and Data-Tier Applications (DAC). At the time (it was the day of Visual Studio 2010 launch), I explained that conference abstracts had to be submitted 6 months ago. At the time, because of some coincidental feature correspondence (e.g. the DAC whitepaper suggests only using DAC deployment on databases of 10gb or less; 10gb is the current maximum size of a SQL Azure database) I'd actually thought that DAC and Azure were "joined at the hip" and that DAC might already be used in the cloud (internally) for SQL Azure deployment.

It isn't. In fact, neither DAC nor SQL Azure Database supported each other. *Until last week*. At the VS2010 launch, the other DAC talk (by the team) said Azure would be supported as a development/deployment environment. But, except for "import from existing database", even the RTM VS2010 didn't work with SQL Azure.

Imagine my surprise on returning home to see this blog posting by the SQL Azure team. As of last Friday, SQL Azure enhancements "enables deployments of database applications directly from SQL Server 2008 R2 and Visual Studio 2010 to SQL Azure for database deployment flexibility". So, it does hook up, after all.

DAC is a pretty controversial feature because in V1, it only supports a subset of database objects and deployment via a
"new database-copy data-rename databases" funtionality. So, its not for everyone. But, at both talks, attendees seemed to understand the target audience, the "departmental database application", the "600th database application" in a large company, the ones that usually have no DBA support because DBAs are busy with 24x7 line-of-business OLTP apps. If you've ever worked in a big company where database and software development is not the main business of the company (ie, the main business is manufacturing cars, or banking, not developing software), you can grok what exactly what a "departmental application" is.

The attendees got it. When I asked at the end of my talk if, because there are customers for it in the present, the DAC ought to be postponed until it would work with all DBMS apps, I only got 1 taker (for postponed) out of 100. Not so controversial after all.

Now, to see exactly how DAC and Azure work together. As of Azure on Friday, Apr 16.

I thought it was curious that in a DACPAC you can specify required version and edition of SQL Server as a deployment option. But DAC (Data-Tier Applications) is a new feature of SQL Server 2008 R2 and VS2010 data tools. So what versions and editions does it support? (or will it support?). The somewhat surprising answer came in today, as an answer to a forum question.

Check this thread out... If I'm reading this correctly, and "No ¹" means "No with a superscript of 1", the functionality may be being backported (see note 1 below the chart). Very cool!

Visual Studio 2010 B2 can make DACs and deploy them as of Nov 2008 CTP of R2. Take a look at Sanjay's video if you missed this announcement.

Note that DAC is a separate concept from SQL Server Utility, although Utility will keep per-DAC stats (one diagram in BOL insinuates non-DAC databases too, but I think I might be misreading the diagram) on the SQL Servers it manages. Utility can only manage 2008 R2 instances at present but maybe (big maybe, move along, nothing to see here) they might backport the support to 2008 some day. And remember, the SQL Server edition for a UCP (Utility Control Point) must be SQL Server 2008 R2 Data Center, Developer, or Enterprise Evaluation. That's Data Center-only for "real" installations.

Glad to see this functionality taking shape.