Name Last Update
..
Demos Loading commit data...
How_to_script_your_db.txt Loading commit data...
README.txt Loading commit data...
powershell .\RunMD3 -ServerName "whatever" -DatabaseName "YourDbHere"


---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
MD3...the Metadata Driven Database Deployment
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

Database builds and deployments are notoriously difficult to do.  Why is that?  Java builds, assuming
they compile, just work.  But db deployments aren't that easy.  The reason is simple.  

State

Java "code", when it compiles, couldn't care less about the "state" of the previous build.  If the java
code compiles, it's good.  In the database world we care about state.  For instance, we've all written 
SQL code like this:  

IF EXISTS (select * from sys.indexes where name = 'Blah' and object_ID = Object_id('Blahdy'))
BEGIN
	DROP INDEX Blah.Blahdy
END
GO
CREATE INDEX Blahdy ON Blah (SomeCol) WITH (ONLINE=ON, SORTINTEMPDB = ...

It's just too cumbersome to write.  
And it's easy to mess it up.  
And if you don't think about your index (for instance) carefully, you'll get duplicates.

Wouldn't it be nice if you could call a "method" in TSQL to do all of this for you?  

Or, better still, wouldn't it be nice if you could just add a new "row" in sys.indexes and SQL 
Server would automagically create the index just the way you asked?  Without knowing DDL?  

That's what MD3 is.  You add metadata to a series of metadata tables that "describes" how you want your
	--index
	--primary key
	--foreign key
	--constraint
	--default
	--table column
...to look and, after you run MD3 you'll magically get what you asked for.  

Isn't that easy? 

But there's more...

	--Scripters to get the metadata created for you automatically
	--"ModelData" deployment mechanism to help you deploy/upgrade seed/model/system/master data
	--Validators that compare schema and ModelData to ensure your deployment worked.



---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
So, how does MD3 do "Metadata Driven Database Deployments"?
--or--
What is a "stateful" vs a "stateless" database object
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

A stateless object is one where we can change it without being concerned with the current state:
	--Stored Procedures
	--Functions
	--Views

In general, we can modify a stateless object at will by doing a DROP/CREATE or ALTER.  There are some
exceptions.  One is that we can't modify a view to reference a new column UNTIL the new column was 
created on the table.  Another is that we can't create a function that references a view UNTIL the view
is created.  These exceptions are merely because of the ORDER in which you apply your db objects.  MD3
overcomes this problem by always running these types of objects in the correct order for you.  

Stateless objects are always redeployed with every run of MD3.  


A stateful object is one where we must be concerned with the current state of the object (its history):
	--table columns
	--Primary Keys
	--Indexes
	--Foreign Keys
	--Constraints
	--Defaults
	--ModelData (data delivered with the db by YOU, not your user)

For example, look at the TSQL above that does "IF EXISTS" on the Blahdy index.  That code will ALWAYS
DROP and CREATE your index, as written.  But that might take hours if your table is huge.  But you 
really only need it to run ONE TIME.  After that, it should be bypassed.  You can add "bypass" code 
yourself, or further modify the "IF EXISTS" check, but that introduces risk.  

MD3 smartly handles stateful objects for you.  You just tell it what you want and it does it.  This
follows the OO paradigm of "Tell, Don't Ask".  Unless you act on an object that doesn't exist, or your
server crashes during a deployment, it will ALWAYS work.  

There are 4 rules to a metadata-driven deployment of stateful objects.  In this order:
	--if the object exists by name and by "properties", then silently continue, no work to do
		--in other words, there was no change
	--if the object exists by properties, but the name is different, then RENAME the object
	--if the object exists by name, but the "properties" are different, ALTER the object accordingly 
		--this is equivalent to CREATE or REPLACE INDEX Blahdy on Foo.Bar...
	--else, CREATE object with properties as defined


---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
General MD3 Rules
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------

Scripts and Folders are run alphabetically.
	--we only execute .sql files
	--If you need to run Foo.sql prior to Bar.sql then either
		--rename Bar.sql to 00Bar.sql and create a new Bar.sql with a comment that the file
			has moved due to an ordering dependency
		--use subfolders
	--it is VERY rare to have dependency issues within the same folder

Within a given folder, the .sql scripts are run PRIOR to any subfolders
	--use whatever folder structure you like, and nest as deep as you like

If you wish to skip a folder from execution, preface the folder with "_".  
	--For instance, "_Documentation"

If you wish to skip a file from execution either
	--rename it to something other than .sql
	--"zero out" (blank) the file. 

Any valid TSQL will be executed.  You don't need any special formatting
	--if you follow our "metadata-driven" approach, then you'll get "rerunnability" for free 
	--when an ERROR occurs (Severity >= 11) then MD3 aborts and displays and logs an error.  
		--Simply fix the error and redeploy.  
		--If you have your rerunnability correct then you do NOT have to restore the db first

Supports the concept of "MasterData" which is data that is deployed as lookup data.  
	--The "MasterData" file format ensures that any version of MasterData can be updated to current
	--without needed to write complex TSQL.

All MD3 objects are deployed to the MD3 schema.  This must run FIRST (or, almost first).  

"Special" folders and files
	--Anything placed in the PostApply folder will run near the END
	--.\00Pre-install\00CreateDatabase.sql ALWAYS runs FIRST.  
		--You can place db creation logic
		--or backup logic
		--or nothing in this file.  The choices are yours.  

There is a Scripter util that helps with creating the table-level objects in the "metadata-driven" format

There are Validators that help you to determine if your scripts are working correctly
	--Validator for schema differences between 2 databases
	--Validator for MasterData differences between 2 databases





---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
Outstanding Issues
---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
1.  MD3.CreateColumn has a lot of stubbed entries.