DaveWentzel.com            All Things Data

Data Architecture

GitLab on Microsoft Azure

I wrote a guest blog post for gitlab.com regarding how to Setup a GitLab Instance in Microsoft Azure.  I'm a big user of git and gitlab puts a nice webUI on top of git, as well as a bunch of additional features.  With gitlab's new images on the Azure Marketplace developers have another avenue to host their git repositories without managing a git server or using external hosting like github.  

U-SQL In Azure Data Lake Presentation

Presentation Download  

(check back for an updated link to the presentation materials)

Hadoop for the RDBMS Expert


Presentation Download

I'll be presenting Hadoop for the RDBMS Expert at the PSSUG March Meeting at Microsoft's Malvern office.  

Polyglot Database Development with 0xDBE

This is an unsolicited review of OxDBE, a universal SQL IDE by JetBrains.  Universal SQL IDEs are meant to make your life easier developing code for multiple DBMSs.  Instead of learning the nuances of multiple DBMS IDEs you can spend your time learning the nuances of each vendor's SQL dialect.  The problem is universal SQL IDEs tend to be either pricey or buggy.  0xDBE is polished and has almost no learning curve if you are familiar with IntelliJ.   Try it while it's still free.  

(This is an unsolicited review of a product I really love.)  

PMP Certified!

Last week I sat for, and passed, the PMP certification.  In this post I'll discuss problems with vendor certifications and why an industry certification like the Project Management Institute's Project Management Professional certification is valuable.  Even a non-manager, propellor-head should consider the PMP certification to advance his career and improve his brand.  

PostgreSQL for the SQL Server Guy

PostgreSQL is an open source RDBMS that is very similar to SQL Server.  In this post I'll compare the features and syntax of the two.  Learning a new DBMS tool can be a lot of fun and will help you understand SQL Server even better.  

Entrepreneurial Programmers

The definition of 'entrepreneur' is misunderstood.  An entrepreneur is so much more than a businessman.  Entrepreneurial programmers are those technologists that exhibit similar traits.  The best programmers are entrepreneurial programmers (EPs).  You want these folks on your team.  And you want to be one of them.  In this post I'll describe what it means to be an EP.  Do you qualify?  

MD3: Metadata Driven Database Deployments

MD3 (Metadata Driven Database Deployment) is my build-and-deploy system that I've evolved over the past 15 years. Deploying database code tends to be challenging for most organizations, especially ISVs that may support multiple versions of their software at many customers.  MD3 handles this all very elegantly.  Over the past 16 posts I've covered why I think MD3 is the best choice for your database deployments.  

Please don't think that MD3 is just database deployment software.  It isn't.  It is also a set of processes and best practices that will help you create reliable and repeatable database deployments.  I've covered concepts like "stateful vs stateless database objects", "script idempotency" and "continuous integration testing" that are Best Practices regardless of the tooling you ultimately use.  

This post summarizes everything I've covered in this blog series.  

Table of Contents

Page Summary
Introduction to Metadata Driven Database Deployments This page.  
Why do we need another database deployment tool like MD3? Covers some scenarios where traditional database deployment tools fail and how MD3 makes hand-crafted DDL a thing of the past.  
MD3 and "state" In this post we cover stateful vs stateless database objects.  It is the stateful objects (those with "history" that we need to consider) that cause our database deployments grief.  We'll cover how MD3 handles this problem.  
MD3 and The Four Rules The Four Rules will ensure you always deploy your stateful database objects correctly.  You don't need MD3 to use The Four Rules, but it helps.  
The Other MD3.Create Procedures Covers the other "stateful" database objects and how MD3 handles their deployment.  

MD3.CreatePrimaryKey in Action

Shows some use cases with primary keys where MD3 can really save you a lot of time and rewrote.  We also further demonstrate how The Four Rules work.  
nodetitle We cover the last MD3.Create procedure in this post and cover the one aspect of MD3 scripts that is mandatory...scripts must be idempotent, which is just a fancy way of saying that all scripts must be rerunnable.
The OO Design Principles of MD3 There are certain object-oriented design principles I incorporated into MD3.  These principles are what sets MD3 apart from other database deployment tools.  
MD3 Model Data Patterns Model data is the data, usually static, that you deliver with your application.  MD3 deploys this data in much the same way as other stateful objects are deployed.  We cover how this is done in this post.  
RunMD3.ps1: The MD3 Executor RunMD3.ps1 is a short Powershell script that simply executes each .sql file in each subfolder in your deployment folder.  In this post I'll cover how it does what it does, and why.  
MD3 Deployments in Action In this post we put everything together and actually show how deployments work with MD3.  
How to Reverse-Engineer Your Database with MD3 The hardest part of any new deployment tool is getting your database migrated into the new structure.  This is actually easy with MD3.  I'll demo how to reverse-engineer AdventureWorks in the MD3 format, which takes about a couple of hours to do.  
nodetitle  CI Loops are a definite development best practice.  Yet few shops do this with their database code.  And if they do CI Loops they may not be doing them correctly.  In this post I'll share some secrets to doing really good CI testing with your database code and then I'll show you how to make it really great with MD3.  
MD3: Why you should care about declarative database deployments If you still don't see the value of declarative/properties/metadata driven database deployments I'll give you some additonal arguments in this post.  
MD3 Extensibility Stories I've deployed MD3 at numerous employers and clients and it has always been a success.  In this post I'll recount some stories about how I saved tons of man-hours refactoring database schemas using MD3.  
What are the benefits of using MD3 over my existing solution? You don't have to use the entirely of MD3 to get some of its benefits.  You can pull out just pieces that you feel may help you.  This is a summary post that reviews all of benefits of the tool.  


Quick Links
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)

You have just read "Introduction to Metadata Driven Database Deployments" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  

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
    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.  nodetitle
  • 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 nodetitle.
  • 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. nodetitle

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.  nodetitle
  • 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.  nodetitle
  • 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 "nodetitle" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.  


Subscribe to RSS - Data Architecture