DaveWentzel.com            All Things Data

SQL Server

Service Broker Demystified - Must my SQL Server be wired to the internet?

Service Broker uses a lot of syntax that is unfamiliar to data professionals.  This scares them away from using SSB.  For instance, DBAs will see references to URLs like http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification and immediately assume that their SQL Server will need to be wired to the internet to resolve those references.  It doesn't.  Those are simple URIs (namespaces) and SSB never connects to them.  In this post I'll prove it and show you why they are used.  

Service Broker Demystified - CLOSED conversations

Those new to Service Broker are confused by CLOSED conversations.  They hang around even when they are CLOSED.  A quick google search will tell you this is for security purposes.  In this post I'll show you exactly why that is.  Also, invariably, one day a DBA will see a huge number of CLOSED conversations that never get cleaned out.  I'll also cover why that happens and how to avoid it.  

Service Broker Demystified - Case-Sensitivity

Some SSB objects are case-sensitive, but which ones?  Shouldn't they all follow the case sensitivity of your server or database?  The answer:  No.  Only "exposed" SSB objects are case-sensitive.  This post will cover why.  

Service Broker Demystified - [Initiators] and 'Targets'

Service Broker "initiators" adhere to the rules for object identifiers.  But "target" service objects are simple string literals.  This leads to goofy syntax and confusion.  This is really no big deal and in this post I'll cover in detail why it works this way.  

Recurring DST Anamolies

Daylight Saving Time is an obnoxious concept to deal with in the IT world.  At a minimum it means that early morning scheduled tasks need to be monitored twice per year to ensure they aren't skipped or run twice.  But if you have customers in other time zones DST can become a real nuisance that isn't easily handled using any current job scheduler.  

Monitoring Service Broker


This is an update to my old post Service Broker Monitoring Routine.  The SB monitoring routine from that post was a little difficult to understand and tried to integrate setting up Service Broker with the monitoring of a Service Broker solution.  Bad idea.  This version of my monitoring routine is far easier to understand.  

Monitoring Disk Latencies with PerformanceCollector

PerformanceCollector (PC) is free and open-source software (FOSS) that I have developed to monitor and baseline performance of your SQL Server.  There are certainly plenty of commercial tools and even free scripts available on the net that handle all of this. What I like about PC is that it can be deployed with a simple PoSh call, runs without any dependencies, and is easily customizable to support any additional logging and reporting that you may need.  

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 - SQL Server