PDC09 Day 1 – Data-tier Application projects in VS 2010

I made it back to PDC this year – although I wasn’t able to get in early to attend any preconference workshops. After getting up a 4am and catching a 6am flight to LAX, it was the LA traffic that got me in 45 minutes after the keynote started. I hear I didn’t miss too much, mostly just Ray Ozzie talking about cloud computing.

The first session that I attended was about a new project type in Visual Studio 2010 called a Data-tier Application. It doesn’t replace the existing Database Projects (e.g. with VS.NET 2008 Database Edition + GDR2) – coming out of the session, my understanding is that it is an alternative project type with some really cool features, but intended for “smaller” projects (“departmental” vs. “LOB/Mission Critical”, or “under 1000 objects”). I’m not quite sure what to make of that – based on some commentary I believe that it has a couple limitations but also the intended audience is different.

First off, here are some of the really cool features discussed. I imagine that some of these features apply to all SQL edited in VS.NET 2010 and not just this new project type:

• Intellisense when editing SQL in VS.NET
• Full integration with the VS.NET 2010 debugger – e.g. when debugging a stored procedure, you can add watches and view the call stack. I hope you can do more – look at a temp table for example, or see what the results of a query might be, but that wasn’t specifically discussed. In any case this is a big leap forward.
• “Compile” errors and warnings, for example, warning if you use “select *” in a view or stored procedure.

The following cool new features are definitely specific to the new project type only:

• Deployment from either VS.NET or SSMS using the “dacpac” output
• Migration to new schema during deployment without writing any migrations scripts – it looks at the old schema, the new schema, and “makes it so”! How cool is that?!
• When you deploy the dacpac using something called a Control Point, you can get a really cool dashboard in SQL Server that gives you usage statistics and other interesting information about your database

Sounds too good to be true?

Here are the “gotchas,” as far as I can tell, for the Data-tier Application project:

• It only works with SQL 2008 R2, which is still in CTP (not released officially yet)
• It is intended for “departmental” databases
• “up to 1000 objects” was mentioned
• only a subset of T-SQL is supported
• limitations to the ability to perform automatic migrations when changes are made
• other? not clear
• It is not yet released… I’m sure it’s leveraging the years of experience leading up to the GDR2 of the database edition, but that was a long and painful road – anyone who’s run into a bug in VSDB that blocked a deployment can relate to this fear

Bottom line: it sounds very promising with some great productivity features and general advancements in the managing database development, but when it comes to the new project types I would proceed with caution and have a backup plan (such as switching to the tried and true database projects).