DaveWentzel.com All Things Data
Data Tier Applications (DTA) vs My DB Porting Process
Data Tier Applications (abbreviated DAC) are the latest method in SQL 2008 R2 to make database deployment and versioning a little bit easier and native to the RDBMS stack. A few years ago MS created database projects in Visual Studio that was a slightly different take on deployment and versioning. Database projects are actually a superset and have some useful features, but I never thought the packaging/deployment stuff was all that great. It's affectionately called Data Dude.
Back to DAC...my take is that this technology won't take off and implementations will be few, leading to a quick deprecation similar to Notification Services (it died after 3 years). The reasons are numerous but mainly database deployments are not the same, nor as simple, as your .NET code. Database upgrades require you to understand the current state of the db and what must happen to the data to get it up to the current release. DAC actually does a good job of this by running schema compares. I'm impressed here. But it still issues warnings and gives an admin choices as to what to deploy and skip. That's the problem...I would never allow someone else to deploy my database upgrade piecemeal. Like everything else in a database, the upgrade needs to be ACID.
Obviously I like My Build Process better than anything else on the market. It handles these situations cleanly, yet is still flexible. Let's compare and contrast.
|Feature||DAC||My DB Porting Process|
|single unit of deployment that contains all elements used by the application (schema, db objects, instance-level objects)||Yes||Yes|
|centrally manage, monitor, deploy, upgrade applications using SQL Server Management Studio||Yes||Kinda...it can be centrally managed, just not with SSMS|
|requires additional licensing fees||No||No|
|can migrate an existing db to the DAC/DB Porting Process format||Yes||Yes|
|SQL Server release compatibility||2008R2 only for deployment. Package can be created on older versions||7,2000,2005,2008,2008R2|
|can utilize other RDBMS backends||No||Yes (Oracle only...but can be ported easily)|
|compares database schemas between 2 dbs (dev and prod for instance)||Yes||Yes|
|compares master/system/lookup data between 2 dbs (dev and prod for instance)||No||Yes|
|Integrates with any version control system easily||Yes||Yes|
|robust error handling (fail/continue on error, warnings, etc||Yes||Yes|
The differences are in the flexibility when upgrading a database from version x to version y. This is especially cumbersome when version x is not known but the process "figures it out" dynamically and knows just what to do. DAC does a good job at this where all other tools require a source/target compare, which is difficult for deployment to thousands of customers.
|Feature||DAC||My DB Porting Process|
|Method to preserve data prior to upgrade||copies db to new db||standard sql backup|
|Method to upgrade data||deploys new db/schema, copies data to new db from the copy, renames new db to old db name, maintaining the copy||In place. This will be *much* faster than DAC since most data in most upgrades won't need to be touched, yet DAC is always copying data.|
|Upgrade is an online operation||No||Partially. New objects (without dependencies) are deployed while the db is online, saving downtime.|
|Can upgrade FROM any version TO any version||Yes. Has a "change detection" phase that looks for differences and auto-generates the SQL to make the change||Yes. Scripts are written assuming a change is always needed, hence will run *much* faster since the evaluation is not done during upgrade-time.|
|Has a "confirmation page" before applying changes||Yes.||No. Not needed, nor desired. This should never be left to an anyone but the developer. An admin should not make these decisions. We either succeed, fail, or log a warning but the outcome of the scripts is always known in advance, we never leave decisions to someone outside of the approved change control process.|
Various DTA links