databases. In the last couple of posts of this blog series I covered what MD3 and why you should care about metadata-driven, declarative database deployments. In this post I'm going to cover how MD3, by default, executes its scripts.
First, there is no requirement to use the script executor, called
RunMD3.ps1. You can always use your own sql script deployment tool, such as Visual Studio db projects. Or you can write your own. I've written script deployment tools using batch files, osql, and vbscript. Do whatever works for you. For the remainder of this post I'll assume you are using
RunMD3.ps1. Before we cover what this script does let's cover how we should deploy sql objects.
Database Deployment Best Practices
Here are some best practices for any database deployment tool:
How MD3 handles these requirements
You can of course customize this process to suit YOUR needs.
RunMD3.ps1only executes .sql files. This means you can intersperse unit test files (such as tsqlt scripts) or documentation files with your .sql files. Simply give them a different file extension.
4Views\Bar.sql) that calls another view called Foo (in
4Views\Foo.sql). This will fail during your CI testing. No problem...two possible solutions:
00Bar.sqland create a new
Bar.sqlwith a comment that states the file has moved due to an ordering dependency.
1MD3Objectsfolder you'll see the objects and subfolders in the nearby graphic.
CreateMD3Schema.sqlwill run BEFORE any .sql files that are in the
Functionsfolder. All MD3 objects are deployed to the MD3 schema, hence why
CreateMD3Schema.sqlis run first. Otherwise, you'll get a dependency error (mentioned above).
7StoredProcedures. You may want to organize those into subfolders like
_Documentationin the screenshot above. That folder contains example .sql files. Those will NOT be executed.
If you use MD3's suggested folder hierarchy then ordering dependencies are minimized.
"Special" Folders and Files
These items break with the "convention over configuration" paradigm that I've designed with MD3 (discussed in [[The OO Design Principles of MD3]]).
PostApplyfolder: anything placed in this folder will run at the end of an MD3 deployment. This is useful for backup scripts, notification scripts, scripts to restart replication, SQLAgent jobs, etc.
.\00Pre-install\00CreateDatabase.sqlALWAYS runs FIRST. This script will CREATE DATABASE if needed. This is useful for CI Build Loops (discussed in the post [[Continuous Integration Testing with MD3|Continuous Integration Testing with MD3]]). You can also put backups scripts here or anything else that needs to run at the start of a deployment.
Errors, Logging, and Rerunnability
Since every script is (re-)run with each deployment you have to write your code to ensure it is idempotent/rerunnable. ([[MD3 Script Idempotency and MD3.CreateColumn|MD3 Script Idempotency]]). This is easy to test with proper [[Continuous Integration Testing with MD3|MD3 Continuous Integration Tests]].
When an ERROR occurs (Severity >= 11) in one of your scripts then MD3 aborts and displays and logs an error. Simply fix the error and redeploy. No need to restore the database first due to a failed deployment.
Back to RunMD3.ps1
Now that I've covered what
RunMD3.ps1 does and why, let's cover how it does it. At its core it is a little Powershell script that recursively calls all folders and sql scripts starting in the root folder from where it is called. It is not written in something like Python or Perl which many database professionals may not understand. Other than this little script, MD3 is written entirely in TSQL. (I have an Oracle version as well, also entirely written in PL/SQL. Contact me for details).
It requires 2 parameters, the
$DatabaseName of the database to be installed/upgraded. The source code is available here.
It uses NT authentication. It can be run remotely, but the machine executing
RunMD3.ps1 must have the sql client installed. The entire script is about 200 lines of Powershell. The bulk of the code is:
Line 181 runs .\00Pre-install\00CreateDatabase.sql. This is discussed above. If you don't need this functionality simply remove this line of code. However, this code really helps with CI Testing by building your shell database. Line 186 calls each .sql script in every subfolder starting with the folder where RunMD3.ps1 lives.
In the next post we'll take a look at [[MD3 deployments in action]].
You have just read "MD3.ps1: The MD3 Executor" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.
Dave Wentzel CONTENT
md3 sql server data architecture