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

I'm the kind of person that doesn't often/always use Visual Studio "autodeploy" (that is, the "Deploy Solution" option) for SQLCLR projects. It's always been missing things (like ALTER ASSEMBLY) and never quite kept up with the newer things you could do with SQLCLR (e.g. ordered table-valued functions, multiparameter aggregates). But every once-in-a-while, especially while I'm teaching and writing example assemblies on-the-fly, its a useful way to get things cataloged fast without writing DDL.

So when I installed SQL Server 2012 and the SQL Server Data Tools on top of an existing VS2010 installation, I was not entirely surprised that, when opening a SQLCLR project, I was prompted to "convert to an SSDT project or leave it alone". Either choice promised me that "I wouldn't be prompted again" but I didn't want that. So I simply cancelled out of the dialog.

However, VS2010 SQLCLR projects autodeploy *doesn't work* against a SQL2012 database. Ever. Even with the simplest projects. A profiler trace of the proceedings looks like some of the DDL it generates is incompatible with SQL Server 2012. Hrmph.

So now I choose the "convert" option, and expect to see a full-fledged SSDT project in Solution Explorer after the conversion. I don't. Looks like I have approximately the same project. Not so. Looking at the project properties gives me all the SSDT properties, and using "Add/New Item" on the project gives the entire list of (85 or so) items that you can add to an SSDT project. So I DO have a full-fledged SSDT project. Does not mean I'll have to import the rest of the database objects to get "autodeploy" (there's no "Deploy Solution" but there is a Publish... option) to work??

Let's try this on the pubs database (I can always re-create the pubs database if something goes wrong). And "Publish" simply publishes the assembly, leaving the rest of the database undisturbed. Turns out that is what I want, so...great.

Now, I've made the moral equivalent of a SQL Server 2012/VS2012 SQLCLR project by conversion. How do I make a new one? There no choice with SSDT but "Other Languages/SQL Server/SQL Server Database Project". Made an empty one of those. So let's make an assembly. Add/New Item...and the SQLCLR items don't appear in the list of all new items. But there is a branch off the tree for "SQL CLR" and "SQL CLR C#". The "SQL CLR" is VB.NET. So first, I need to add an assembly info file, if I want one of these. So far, so good, but there are no referenced assemblies other than .NET 4.0's System.Dll (the properties window, SQLCLR tab, show Assembly Info and fill in the dialog does the same thing). It looks like it's smart enough to build a .NET 4.0 target because my target database in project properties is SQL Server 2012. Now let's add a SQLCLR UDF (the default template for this no longer returns "Hello", but String.Empty, hmmm...). And I do get System.Data and System.Xml added to the references. And Build and Publish works. BTW, during the "Publish" process, you also have the ability the generate a publishing script rather than publish in real-time. That's nice.

So, I'll go through of compare the generated DDL later and see if I can do anything in SQLCLR 2012 that SQLCLR 2008 autodeploy (Publish/Deploy) can't do. For now, I at least have a way to replicate the functionality of SQLCLR projects. That's works with a SQL Server 2012 (and 2008 and 2005, though I didn't try each one, I'll take them at their word, for now) database. Whew! But, once you've converted the original VS2010 project (unless you choose, "save old project" during conversion) you can't go "home" anymore... You're in SSDT-project-land now.

@bobbeauch

As I'm getting ready for my week-long SQLskills Immersion Event in Chicago next week, I got word that the demos from my events in Europe in late March/early April have been posted on the Resources part of the SQLskills website.

I got to catch up with a lot of old friends and make a lot of new ones at these events. Hope you liked the talks.

@bobbeauch

Thanks to all the folks who attended my sessions at DevWeek, SQLBits, and SQL Saturday Dublin. Hope that you enjoyed the sessions. It was really nice to meet up with old friends and also to make new ones.

As promised, the demos will be posted on the SQLskills website, but it may be a week or so, as I'm not headed home directly. It's on to Stockholm for a 2-day SQL Server 2012 for developers seminar before returning home at the end of the week. So it will be "soon".

Cheers, Bob

@bobbeauch

Starting the middle of next week I'm about to embark on a series of speaking/teaching engagements in Europe. Although I've mentioned some of these before, and some of these are already sold out, it bears mentioning again. If you've read my "content" (books, blog posts, whitepapers, articles) in the past, and even if you haven't, if you're at these events, please stop by and say hi. Events like this are a good chance not only to catch up with old friends, but to make new ones. I'll be at:

SQL Saturday in Dublin - my first trip to Dublin and first SQL Saturday, really looking forward to it. Speaking on Searching files and data with SQL Server 2012 (a.k.a Filestream/FileTable and Full-text search improvements/internals).

DevWeek in London - A chance to catch up with old friends. I'm doing a precon here on SQL Server 2012 for Developers and a few talks; see the website for a schedule.

SQLBits X London - I've always wanted be at one of these. Speaking on Friday afternoon on SQL Server 2012 Spatial and Saturday on Extended Events - The New Trace.

Addskills Expertseminarium series - Two days of SQL Server 2012 and performance (Apr 3-4) for database developers. Addskills website has the outline. I always enjoy Stockholm and the Addskills folks are great hosts.

And, let's not forget the US and our very own SQLskills event. It's an almost sold-out SQLskills Immersion Event in Chicago in April. Five days of intense training on everything a database developer needs to know. And more.

Hope to see you at one of these events. Cheers!

@bobbeauch

I recently completed another update to the whitepaper, now entitled "SQL Server 2012 Security Best Practices - Operational and Administrative Tasks" to cover best usage of the security features in SQL Server 2012. And, concurrently with the launch of SQL Server 2012, it was available on the web a few days ago. Many thanks to the reviewers. Enjoy!

@bobbeauch

Hope that you'll find time in your schedule to attend DevWeek 2012 in London, March 26-30. I'll be doing a preconference talk, an entire day of SQL Server 2012 for Developers. In addition, I present some breakouts on SQL Server security best practices, Extended Events and the new profiler, Mission-critical SQLCLR and other relevent topics. Here's hoping to see you there, drop by and say hi!

@bobbeauch