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

Back when I was saw SQL Azure was producing an @@version string that mentioned version 11 (that's SQL Server 2012's major version number), I'd started looking at the version number every week or so. And looking for SQL Server 2012 functionality in SQL Azure. See the blog post "SQL Denali T-SQL features in SQL Azure now" for a list of the first functions supported. I'm not going to list them all again here. And Ed Katibah's (a.k.a Spatial Ed) latest list of SQL Server 2012 spatial functionality in SQL Azure.

This week, I'd thought to look at the SQL Azure version again. I get:

Microsoft SQL Azure (RTM) - 11.0.1831.30
 Jan 27 2012 23:11:55
 Copyright (c) Microsoft Corporation

This is different (newer) than when I previously looked, so its time to look for new T-SQL 2012 functions again. And to find some. 13 of the 14 new T-SQL scalar functions (all of them except TRY_CONVERT, I wonder why that one didn't make it) are in. That would be: FORMAT, PARSE, TRY_PARSE, IIF, CHOOSE, CONCAT, EOMONTH, and the new "date-from-parts" series.

In addition, the T-SQL 2012 updated version of the T-SQL LOG function that allows a logarithm's base as a second argument works in SQL Azure as well.

There's also one more new function that isn't mentioned in the books online (I hit it by mistake when I thought I was somehow mistaken about there being a TRY_CONVERT and it works in SQL Server 2012 RC0 as well), that is TRY_CAST. It's analogous to TRY_CONVERT, as CAST is analogous to CONVERT, modulo CONVERT's expression and style arguments.

This isn't a complete implementation of SQL Server 2012's T-SQL improvements in SQL Azure, though. SQL Azure is still waiting for:

1. Sequences
2. UTF-16 collations with supplimentary characters
3. Windowing (OVER clause enhancements, LAG/LEAD and others, new analytical functions)

I didn't notice any change in spatial support moving more towards to SQL Server 2012 feature set this time, maybe Ed noticed something.

Well, a bit at a time, I guess. Welcome the new T-SQL functions to SQL Azure.

@bobbeauch