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
|Introduction to Metadata Driven Database Deployments|
|Download MD3 (with samples)|
|Documentation on CodePlex/git|
|Presentation on MD3|
|Other Posts about MD3|
|AdventureWorks2013 reverse-engineered into MD3 (takes 5 mins to do with ANY database)|
Here is a short list of problems that MD3 and/or any "declarative" deployment utility fixes.
WITH CHECK CHECKwhen you create your foreign keys? Isn't it easier to simply remember to select an option to "allow existing data to violate the constraint"?
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.
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.
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.
Dave Wentzel CONTENT
md3 sql server data architecture