Why should we build and deploy EVERY database script EVERY time, even if the object hasn't changed?
In MD3 Script Idempotency and MD3.CreateColumn we looked at why all sql scripts in MD3 must be "rerunnable", meaning that a script cannot have "side-effects" if it is subsequently executed multiple times. With MD3 every SQL script is run during every deployment. Folks that are new to MD3 question this approach.
|[[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)|
Just suspend disbelief and you'll see the benefits of doing this.
There are lots of ways to run PoSh scripts. I like to open a command prompt and start
powershell. You then
cd to the root directory where your MD3 deployment scripts reside. Then you simply call the deployment executor using PoSh dot notation, like this:
.\RunMD3.ps1. There are two parameters
set-ExecutionPolicy Unrestrictedfirst. This allows you to run PoSh scripts. This isn't my requirement, rather a requirement of powershell itself.
00Pre-install\00CreateDatabase.sql. In fact, this is a good idea if you are going to create CI Build loops (see [[Continuous Integration Testing with MD3]]). But by default, for safety reasons, MD3 requires the db to exist. If it doesn't you'll see an error similar to this:
Whenever an error is thrown with a Severity >= 16 the deployment will stop and an error message will be displayed. This works EXACTLY like SSMS.
In this screenshot we can see that the failure occured in
3Tables\HumanResources.Employee.sql because the
dbo.Flag user-defined data type does not exist. By using
start-transcript, or by piping RunMD3.ps1 output, you can capture the execution log seen above to a text file as well. Execution logs are also saved to a table named
MD3.Log. This table is truncated at the start of every deployment, so if you wish to archive the data from every deployment you must either alter the MD3 system or squirrel the table away to another location.
The following graphic shows the contents of MD3.Log after an error was thrown:
In this case we can see the error that was thrown. The constraint could not be created. The actual SQL statement that errored, that was generated by the MD3 system, based on the properties passed to MD3.CreatePrimaryKey (in the example), is always logged just before the error. This will help you determine what went wrong. These errors are extremely rare and should always be caught during continuous integration testing.
Here is what you can expect to see when MD3 succeeds:
Any WARNINGs will be displayed just before the success message, or can be queried from
MD3.Log by looking at
WHERE Severity = 'W'. Out-of-the-box the only WARNINGs MD3 will throw are untrusted constraint violations (if you allow them). You can code your own by tactically adding calls to
MD3.InsertLogMessage with the
@Severity = 'W' option.
In this post we showed how to run the MD3 executor PoSh script,
RunMD3.ps1. This utility executes any .sql file it finds in its folder or any subfolder. If a .sql script throws any errors those are bubbled up and logged to MD3.Log and are shown to the user running the PoSh script. In the next post we'll look at [[How to Reverse-Engineer Your Database with MD3]].
You have just read "[[MD3 Deployments in Action]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.
Dave Wentzel CONTENT
md3 data architecture sql server