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







Add new comment