In the last posting, I talked about the differences between DACFx 3.0 and previous versions from an API point of view. This time, I'll look at the files in a DACPAC and see how those differ.

DACPAC really stores a database model, that can be used to recreate the database and server (3.0 only) objects in the model, in an XML format. Note also that I've seen it written the a DACPAC is a simple zip file and you can unzip it to produce the same files that the "Unpack" Windows explorer shell extention (i.e. right-click on a DACPAC on a machine where DAC is installed and you'll see an "Unpack" context menu entry. This is not exactly true, as unzipping a V1.1 DACPAC on my system produces the following files:
 DacMetadata.xml
 LogicalObjectStream.xml
 PhysicalObjectStream.xml
 [Content Types].xml

And using the Unpack extention produces:
 DacMetadata.xml
 LogicalObjectStream.xml
 PhysicalObjectStream.xml
 model.sql

Note that the "unpacker" uses the object definitions in the XML files to generate a SQL script for the model. To stay on the V1.1 DACPAC for a minute, if the DACPAC has been produced using the VS2010 DAC Project, additional files may be present. These include:
 Script.PreDeployment.sql
 Script.PostDeployment.sql
 TargetSelection.xml

Pre and post deployment scripts are intuitively named. TargetSelection refers to the file the VS2010 DAC Project produces if you use a SQL target selection policy. With this project type, you can specify (editable using the ServerSelection.sqlpolicy node in the Solution Explorer for the project) which servers this DAC should be installed on. Choices include Collation, IsCaseSensitive, OSVersion, and more. Note: TargetSelection seems to be missing from the SSDT projects.

With an DACFx 3.0 DACPAC, different files appear. The "unpacker" will produce:
 DacMetadata.xml
 Origin.xml
 model.xml
 model.sql 

Note that, just with pre-3.0 versions, no .sql file appears in the unzipped version, so the .sql script is actually produced by the "unpacker". The model is strictly XML-based until unpacked. As interesting aside is that in DACFx 3.0, unpacking is supported by the API, using the Unpack method in the DacPackage or BacPackage classes. These methods support unpacking 3.0 or pre-3.0 DACPAC/BACPAC formats. The equivalent code in the pre-3.0 unpacker couldn't possibly know about future versions.

So what do these files contain?
 DacMetadata.xml - The name and version of the "application" that this DACPAC represents. Note that in 3.0, an XML namespace is added.
 Origin.xml (3.0 only) - Some metadata about where this DACPAC came from (which version, which tool, etc).

So the difference really amounts to the distinction between LogicalObjectStream.xml and PhysicalObjectStream.xml (pre-3.0) and model.xml (3.0). These files contain an XML model of a database. In appears that, in DACFx 3.0, the two XML files were consolidated into one. And all the additional database and server objects that DACFx 3.0 represents were added.

Finally, let's take a look at the model files. DACPAC pre-3.0 refers to two XML schema, in both the "Logical" and "Physical" model files. These are:
 http://schemas.microsoft.com/sqlserver/ManagementModel/Serialization/2010/11
 http://schemas.microsoft.com/sqlserver/RelationalEngine/Serialization/2010/11

I was not able to find a current version of the XSD files at the location implied by the namespace name (that's not a requirement of the XML schema spec, but sometimes it works), but I was able to find an older version of the ManagementModel schema at the schema.microsoft.com website.

In DACFx 3.0, there is reference to a single XML schema in the model.xml and DacMetadata.xml files:
 http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02

This schema appears at the location that matches its namespace name. And two additional XML schemas also exist related to DACFx 3.0. They are:
 http://schemas.microsoft.com/sqlserver/dac/DeployReport/2012/02
 http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02

So, we've just taken a little spin through the items in a pre-3.0 and 3.0 DACPACs. The XML files and the information contained in those files have evolved greatly in DACFx 3.0. This is likely due to the integration of SQL Server Database Projects from VS2010 and the DAC Project in VS2010 SP1 into a single project type and format, that of SQL Server Data Tools, which subsumes both types of project. As for the concept of using DAC to manage "Deployment target" systems (i.e. the TargetSelection.xml file) depending on its utility and acceptence for use in database/application management, maybe this will return one day.

@bobbeauch

Looking at the API and at the serialized form (i.e. the DACPAC), it turns out that DACFx 3.0 is not just "DAC V-next". It's an entire quantum change from all other versions, including DAC 2.0, the version that introduced the BACPAC (serialized schema and data). This has some interesting repercussions with compatibility. Let's start with the "native" API (meaning, programming over the DLL directly, I'll leave the REST API used by SQL Azure Portal's Import/Export for another time).

The DACFx 3.0 API lives in a different DLL than previous versions of DAC. The old DAC (2.0 and below) lives in Microsoft.SqlServer.Management.Dac; DACFx 3.0 lives in Microsoft.SqlServer.Dac. The API is quite different with respect to classes and methods then the one I wrote about in SQL Server Magazine's Jan 2012 issue. Instead of using the DacStore and DacExtractionUnit classes from previous versions, most everything has been refactored into the all-new DacServices controller class. This new controller class contains most of the methods from the original DAC (albeit with different names like Deploy, Extract, Register, Unregister) along with new methods that generate a drift report and methods to Export and Import the BACPAC (schema and data) format. The one method that's been dropped in the new API is the Uninstall (unregister and drop database) method.

One other difference is that DACFx 3.0 can work with DACPACs and BACPACs from previous DAC versions, but can only emit DAC 3.0 formats. The upshot of this is you can't use a DACPAC from SQL Server Data Tools or SSMS 2012 with earlier versions or tools.

The nice improvement we get from the DACFx 3.0 format is support for almost every object in SQL Server 2005-2012. In addition, when using the 3.0 APIs directly, or with SSDT, there are infinitely more options for working with DACPAC, encapsulated in the DacDeployOptions or DacExtractOptions classes. These options are exposed by SSDT as project properties, and its command-line utility, SqlPackage as command-line parameters as well. Some random examples would be the deploy option to "BlockWhenDriftDetected" or the extract option to "IgnoreUserLoginMappings".

You can read the documentation for the new classes in Microsoft.SqlServer.Dac here.

@bobbeauch

I can script out a database schema (and optionally data) from SQL Server databases. Any version from 2005 and beyond, as well as SQL Azure Database. And most all instance level objects.  And I'm not the SMO scripter object. Or a DBA. Who am I?

I would be DACFx 3.0. And I can do some things (unlike SMO scripter, or a busy DBA) like script out deltas (schema only), given any two databases or DACPACs. Or any combination of SSDT project, database, and DACPAC. 

When DAC (that's data-tier applications, not dedicated administrator connection) was introduced back around SQL Server 2008 R2, there were a subset of "most likely candidate" database objects it could script. This increased over time to include all SQL Azure database objects. But in DACFx 3.0, which appears in SSMS 2012, SSDT (along with its command-line brother sqlpackage.exe), and soon the Azure portal, there is almost full-fidelity up to SQL Server 2012. There are still some rough edges being worked on (along with enhancements), both in the functionality and in the integration with SSMS, as you might expect.

SSMS and SQL Server's PowerShell provider are both built with SMO, and SMO's functionality roughly follows what SSMS supports (e.g. before SMO 2012 there was no SSMS/SMO support for extended events; now there is, in both places). SMO is much, much more granular in its API... think "almost every SSMS dialog is built over SMO". DACFx providers a discrete set of (less than 10) utility functions. But in SSMS 2008 R2, 2008 R2 SP1, and 2012, DACFx makes its presence felt. For example in SSMS 2012, we have, in SSMS Object Explorer:
 
Databases Tab:
    Deploy (from DACPAC)
    Import (from BACPAC)
Individual Databases/Tasks:
    Extract (to DACPAC)
    Deploy Database to Azure (Migrate via BACPAC)
    Export (to BACPAC)
    Register (to MSDB, to master on SQL Azure)
    Upgrade (existing registration and schema)
    Unregister

In SSMS, these functions are fairly rigid, i.e. there aren't many options, failure to comply with the "default options" can cause failure. But with SQLPackage.exe and SSDT and the DACFx API, a plethora of options exist (albeit not as many as SMO scripter, but some of scripter's options are for code formatting). These options live in the DacDeployOptions and DacExtractOptions classes in the API (that is Microsoft.SqlServer.Dac.dll) and are exposed as command-line switches in SQLPackage.exe or properties in the SSDT project properties page. And documented in the SQL Server documentation. 

If you don't want to install SSDT on your machine, you can download DACFx 3.0 as part of the SQL Server 2012 Feature Pack. It's called "Microsoft® SQL Server® 2012 Data-Tier Application Framework".

More to come...

@bobbeauch

After my last adventure, I thought it would be interesting to try the "upgrade path" with SSDT and older "Database Projects" (i.e. Visual Studio for Database Professionals, or whatever the last name was before we went to the nice SSDT acronym, I always called them "Data Dude" projects after Gert Drapers, the original Data Dude).

Note first-off that when you have both VS2010 and SSDT projects installed into Visual Studio, there are two main menus, one called "SQL" and the other called "Data". In case you can't guess, "Data" is the one that goes with the "older" projects (it contains an entry for "Data Compare", a feature that SSDT doesn't have yet). And "SQL" is the SSDT project menu. There's a few "dualing toolbars" too; luckily for me, I'm not a big toolbar user in VS.

At least in VS2010 the Database Projects were always version-specific in name so I know that I can't target SQL Server 2012 in a "SQL Server 2005 or 2008 database project". Easy. Opening one of these projects brings up a "Convert to SSDT" dialog. This works as advertised, just realize that there are some "artifacts" that are not converted because the corresponding feature doesn't exist in SSDT yet (e.g. Data Generation Plans). Backing up the old project is a good idea in any case. See this page for a list.

Finally, there are Data Tier Application projects. These support SQL Azure and the subset of database objects that SQL Azure supports. You can create a DAC Project in Visual Studio after the SSDT tools are installed. Interestingly (but expected), you can't import database objects from a SQL Server 2012 database, even if the database only contains the subset of objects that DAC supported in DAC V2.0, you're presented with an "Unable to connect to database" message. Surprisingly, if you set your SQL Server 2012 database to Version 90 or 100 compatibility mode (thats SQL2005 or SQL2008 compatibility), creating a DAC project and importing the database now works! (if it contains the right subset of objects). And of course the project type would work (at least at this point in time) with a SQL Azure database or importing a V2.0 (but not V3.0) DACPAC. Bringing up an existing VS DAC project brings the (at this point) familiar "Convert" dialog. Bear in mind that DAC projects could have some of the same unsupported artifacts as Database Projects. 

Got it? So now you can co-exist with both/all sets of SQL database projects, database-version willing. There are some nice new features in SSDT that make it compelling to convert, and the "catch up" features may be added soon, because SSDT will be updated "on a SQL Azure cadance". That's once every 3-6 months. And, come Visual Studio 11 (available in CTP/beta now), there will ONLY be SSDT projects. So...Enjoy!

@bobbeauch

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.