MD3: Why you should care about declarative database deployments

Over the last several posts we've covered how [[Introduction to Metadata Driven Database Deployments|MD3]] handles database deployments.  You may still be wondering why you should care about deploying your database code using metadata instead of hand-crafted DDL.  Even if you use a GUI-based "database compare" tool to generate and deploy your DDL you may want to consider MD3 instead.  In this post I'll briefly cover why MD3 is superior to these tools.  IMHO.  

Why you should care about Metadata Driven Database Deployments

Here is a short list of problems that MD3 and/or any "declarative" deployment utility fixes.

1. Are you fearful of refactoring large tables in your application for fear of writing all of those DDL scripts to migrate the data from the old structures to the new structures?  
2. Have you ever written DDL that ran on your dev and QA databases without incident but failed miserably during your production deployment?  
3. Do you have trouble remembering esoteric DDL that you may only use a couple of times per year?  Example...can you remember when to use WITH CHECK vs WITH CHECK CHECK when you create your foreign keys?  Isn't it easier to simply remember to select an option to "allow existing data to violate the constraint"?
4. Do you use "graphical DDL" tools to make your schema changes but worry about the tool recreating expensive objects during your deployment because you can't see the source code or the DDL that it is applying?  Do you wish your "graphical DDL" tool gave you finer grain control over HOW it deployed database changes?  For instance, would you like to be able to build some indexes during downtime and schedule others for later.  
5. You have customers on multiple releases of your software/database and you fear that you don't have time to test every database migration path.  You've been burned by this before and management now fears every database schema changes, regardless of how minor.  
6. Your developers want to move at Internet speed.  They hate having to come to the Database Review Board for every database change request.  They want to prototype something quickly and modify the database schema on-the-fly.  The Board doesn't allow this because the DDL to migrate the schema is always buggy and causes botched deployments.  
7. You've heard your developers at the water cooler bitching about what a gigantic PITA it is to alter a relational schema.  You heard them talking about bringing in a few NoSQL products because they are "schema-less".  Now the developers can alter their schemas without having to deal with huge data migration efforts and buggy, hand-crafted DDL.  (This is complete bunk BTW).  

Using a "declarative" approach to database deployments solves these problems.  And as I've shown over the last several posts, MD3 does all of this for you.  

Why MD3 is better?

Look, I've used quite a few of the database deployment tools on the market.  By and large, they are great.  Ingenious even.  But the problem is that none of these tools solves all of the full data lifecycle issues that I've mentioned above.  None of them.  Some might make schema changes simple with point and click tools, but don't support Continuous Integration Testing.  Some make schema changes easy, but don't even attempt to provide a framework to handle the data migration tasks that are inevitable with any schema change.  

Evolutionary Databases

In January 2003 Martin Fowler, a development industry heavyweight wrote about the need for evolutionary databases.  At that time I already built the first iteration of MD3 that supported both Oracle and SQL Server.  Even this early version of MD3 had all of the features Mr. Fowler advocated.  Or had the underpinnings that would get MD3 to a full-featured database deployment tool in the future.  My biggest complaint with this article is that Fowler pointed out very common-sensical issues with relational database development, but offered no real solutions.  There is a section at the end that mentions "tools to help" but doesn't actually list any tools.  

During my entire career every employer/client has been uncomfortable with database refactoring and "database evolution" because of the points Mr. Fowler makes.  Once exposed to MD3 this mindset slowly changes.  Management, senior developers, and architects realize they now have tooling, processes, and patterns that actually make constantly evolving relational schemas easy.  

The Curse of Relational Databases

Grant Fritchey, a SQL Server industry heavyweight wrote The Curse of Relational Databases in June 2014.  Eleven+ years after Fowler wrote about evolutionary databases Mr Fritchey points out many of the same problems.  Eleven years and not much progress.  But now there are alternatives to relational databases and Fritchey notes the NoSQL movement which touts ways to get around the difficulty of relational schema lock-in.  I've noticed the same things.  He succinctly sums up his article by mentioning that relational folks aren't paying enough attention to the pain that a RDBMS imposes on organizations (my words, not his).  He's right.  But there are simple solutions, like MD3, that can give to data developers the same processes and patterns that are working for regular 'ol developers...continuous integration testing, properly versioning database objects with the application code (name one tool that does this) not as a separate code base or tool, and declarative programming instead of hand-crafted DDL.  

Summary

Sorry for rambling on in this post but I believe the perceived difficulty in evolving relational schemas may be a turning point in the relational industry.  More shops will turn to NoSQL solutions because of the perceived ability to be unconstrained when it comes to schema.  Folks don't want to plan their schemas up-front anymore.  They want to be agile.  They want their schemas to evolve as their businesses evolve.  They don't want unnecessary schema lock-in.  Unfortunately, I believe NoSQL in many of these cases is the WRONG solution to the schema lock-in problem.  Sure, the schema isn't quite as locked-in with NoSQL, but your just trading one set of problems for another.  Ever tried writing a report in Crystal with a NoSQL product?  Can't be done.  So all the time you saved on your DDL you wasted on report development.  There are good use cases for NoSQL, but the "schema-less" argument is very weak.  The fact is, with good data lifecycle tooling we can achieve evolutionary databases quite easily without the relational database being a curse.  

In the next post, [[MD3 Extensibility Stories]] we'll look at some use cases where MD3 and declarative database deployments can really save you a lot of time when you need to do schema refactoring.  Stay tuned.  

You have just read "MD3: Why you should care about declarative database deployments" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.