DaveWentzel.com All Things Data
What are the benefits of using MD3 over my existing solution?
This will be the last post (for a while) in my MD3 blog series. I'm going to wrap up with a post on the benefits of using MD3 over other DDL deployment solutions.
Embodies Free and Open Source Software Principles
- There is no third party software to deploy or license.
- It's completely free and open source (MIT license). You can look at the code and figure out exactly what will happen when you run it. Now look at how Visual Studio database projects deploy...there is almost no documentation on how the engine makes its decisions as to what DDL will be run. So, how do I make money off of MD3? Services. I've deployed MD3 for 6 different ISVs...huge ISVs...and they love it. One ISV has about 100 customers with databases as large as 10TB that MD3 deploys with almost zero downtime. Another ISV runs over 4000 customers on tiny SQL Express databases...yet they have NO support personnel to handle database upgrades...because the upgrades NEVER fail.
Quick Links 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)
It follows the Unix principle of "do one thing and do it well." MD3 is a bunch of really small, separate components. Let's say you only want to use MD3 to deploy system/model/lookup data...you can do that without deploying any other component.
- Runs any TSQL. It requires no special, proprietary syntax or formatting. This makes your code portable if you decide later that MD3 isn't working for you. This also allows you to tranfer your existing custom .sql scripts in to MD3 with almost no effort.
- Follows OO design principles. Use only what you need, or use everything as designed, or add new features as you need them.
- If you need to customize it to fit your unique requirements you can. MD3 Extensibility Stories
- Although I haven't open sourced it, MD3 is totally portable to Oracle. Procedures like MD3.CreateIndex have a counterpart written in PL/SQL that runs on Oracle. Obviously properties like "fillfactors" and "clustered indexes" do not exist in Oracle, which has similar properties like "Index-organized tables" and "PCTFREE". In theory, all MD3 components can be ported to any DBMS platform. Under-the-covers everything is just SQL, DDL, and The Four Rules. This allows your application to be DBMS-agnostic.
- There is also a component that uses MD3 to generate Zero Downtime Database Upgrades. Please contact me for more information.
- Handles the deployment of model/system/lookup data without complex, hand-crafted scripts. No other tool on the market does this. None. And this is infinitely flexible and handles any model data deployment scenario that you can think of. Please see MD3 Model Data Patterns.
- MD3 will completely reverse-engineer your schema into a format compatible with MD3 (which is really just SQL). It will even reverse-engineer your model data. How to Reverse-Engineer Your Database with MD3
Customizable and geared toward DevOps data professionals
- Uses your existing TSQL development tools. It works equally well with SSMS, VS, TOAD, dbeaver, or whatever other tool you might prefer. There is no need to context switch to another tool to use MD3.
- There is no GUI because a GUI isn't necessary. You can visualize your deployment by looking at the folder structure of the database deployment scripts. It's easy to visualize the exact order your stateful database objects will deploy in.
- There is nothing written in Python, Perl, Ruby, or whatever, which becomes another language you have to learn to support the tool. MD3 has a small driver written in Powershell (less than 100 lines of code) that simply recursively executes each script in each folder. But the driver could be just a simple batch file, some vbscript, or you could integrate it into your existing tool.
- Infinite customization. Do you need to backup your db prior to your deployment? Just add a script in the appropriate pre-deployment folder. Do you need to stop or start replication? Put the necessary sql script in the right location. Do you need customer-specific versions of scripts? Just put the correct MD3 calls in the correct folder.
- Written entirely in T-SQL (with a tad of Powershell, which is not required). No need to learn another language/tool.
- Does full logging to both a table (
MD3.Log), the console, or pipe the output to a file. MD3 Deployments in Action
- Need some special logging? Just add a PRINT statement that logs whatever you need, wherever you need it in your code.
- MD3 will fail on the first error and is totally rerunnable without having to restore your database.
- Errors can be converted to WARNINGs which can be logged for later analysis. This is useful for warning when a foreign key is not trusted. You might want to fix those untrusted keys later, but you don't want to fail your deployment.
Helps enforce good software development practices
- You can create a CI Build Loop for your database very quickly with whatever tool you want...ant, cruise control, jenkins, msbuild, etc. Continuous Integration Testing with MD3
- MD3 has built in "validators" that compare a "net new" and an "upgraded" database to ensure they are schematically identical. It will even validate your system/model/lookup data. This is the key piece of any database CI Build Loop.
- Works with any version control system without the need for any plugins.
- Do your own release numbering (MD3 doesn't care).
You have just read "What are the benefits of using MD3 over my existing solution?" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.