dave wentzel dave@davewentzel.com
Post on 24-Dec-2015
226 Views
Preview:
TRANSCRIPT
METADATA DRIVEN DATABASE DEPLOYMENT
Dave Wentzeldave@davewentzel.comwww.davewentzel.comhttps://md3.codeplex.com/
Why do we need another database deployment tool?
NoSQL: “RDBMS schemas are too hard to change”
If your Java compiles on your machine, it will compile on, basically, any machine. Then, the deployment is simple
But just because your database deploys on your machine doesn’t mean it will deploy anywhere else.
Why?
State!
Java code, when it compiles, couldn't care less about the "state" of the previous build. It is “stateless”
Database code is both “stateless” and “stateful”. We need to worry about “history”. The code must do more than just compile
Stateless db objects: Views Procedures Functions
Stateful objects: Tables/columns Indexes Constraints (PKs/FKs/CHECK) Model Data (Model/Seed/Lookup/Master/System)
Stateless Object Patterns
Simple No state dependencies (rarely anyway)
If exists (‘SomeProc’)DROP PROC SomeProc
GOCREATE PROC SomeProc AS SELECT ‘Hello World’GOGRANT EXEC ON SomeProc To SomeOne;GO
Stateless Object Patterns (cont)
Another variant on the patternIF NOT EXISTS (‘SomeProc’)
CREATE PROC SomeProc AS SELECT ‘Shell’GOGRANT EXEC…GOALTER PROC SomeProc AS ..
Stateless Object Patterns (cont)
Oracle PL/SQL gives you this syntax which is much simpler
CREATE OR REPLACE PROCEDURE SomeProc AS
The Point is
You can deploy stateless database objects at (almost) anytime
There are some exceptions Always involve ordering of objects to be
applied Can’t create a function that references a
view that does not yet exist
Stateful Object Patterns
Java has no “stateful” code, if it compiles, it's good. In the database world we care about state.
An example of “stateful” index creation code.
\Demos\Index_example.sql
Stateful Patterns - Lessons Learned
DDL is kinda esoteric and difficult to remember.
The same goes for some DML for master/seed/model/system data that you may need to deliver. We’ll look at this later.
You really kinda, sorta, need to remember The Four Rules if you want to do stateful database object deployments correctly and repeatably
The Four Rules
1. if the object exists by name and by "properties", then silently continue, no work to do in other words, there was no change
2. if the object exists by “properties”, but the name is different, then RENAME the object Sp_rename
3. if the object exists by name, but the "properties" are different, ALTER the object accordingly May need to DROP/CREATE sometimes , unfortunately
4. Else, CREATE object with properties as defined
State and The Four Rules
If you don’t do The 4 Rules correctly you risk, at a minimum, recreating expensive objects at every deployment
This is A LOT of SQL to write every time you need to modify a stateful object
Wouldn’t it be nice if you could just enter the “properties” of the object in a metadata table and SQL Server just did what it needed to do?
Metadata Driven Db Deployments
“Tell, Don’t Ask” Don’t
get information then make decisions Then tell SQL Server what to do
Just Tell SQL Server what to do We do this by supplying metadata to MD3
which looks SIMILAR to underlying SQL Server metadata like sys.indexes
But first, let’s see this in action (Demos\Index_example2.sql)
MD3.Create* Procedures
Deploy stateful database objects smartly
But don’t you still need to hardcode a lot of calls to MD3.Create* for every stateful object in your db?
Nope. Just change the metadata Let’s look at an example using
AdventureWorks2012
Stateful Changes – Summary
Just change the appropriate metadata seed data in the Tables folder
But how do we create the metadata? That’s gotta take awhile.
Nope!
Creating the Stateful Metadata
Hard Part: find a “pristine” “model” copy of your database
Deploy the MD3 objects run MD3.Repopulate* procedure
Repopulates the metadata table run MD3.Script*
Issues a bunch of PRINT statements to build the .sql file to be put in the Tables folder
Demo: Using AdventureWorks2012
ModelData
You may know this as MasterData, SeedData, LookupData, or SystemData
It is data YOU deliver with your solution. It can “evolve” over time and our
ModelData scripts handle this. Base ModelData assumes ONLY you
change your ModelData, but this can be changed.
Example: modeldata.sql and Production.ProductCategory.sql
Additional Stateful Object Examples
These are all scripted for AdventureWorks2012
PrimaryKeys.sql Indexes.sql CheckConstraints.sql DefaultConstraints.sql ForeignKeys.sql (slightly special
case)
Putting It All Together
RunMD3.ps1 (but you don’t HAVE to use this. Use whatever you want)
Deploys any .sql script you write. can be ANSI, UTF-8, or both
Starting in the RunMD3.ps1 folder… runs .sql FILES first then runs SUBFOLDERS recursively
Uses MD3 stateful deployment scripts (or not) No need to have CRUD and DDL scattered in hundreds of “daily
build” sql scripts Handles the “ordering” issue for stateless deployment
scripts Will create a NET NEW database or UPGRADE an existing
database to the current buildlevel (state of the files)
Why Always Build/Deploy Every Script?
It’s safest…always deploy what you tested It takes a few milliseconds to check
“properties” and determine there is no net change to an object
We are not locked into build-by-build deployments. We can upgrade any database to “current”
Ever have somebody cowboy a stored proc fix without checking it in?
That’s how every other language does it
RunMD3.ps1 “Rules” Every script must be idempotent/re-
runnable Scripts and Folders are run
alphabetically. 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
“Rules” (cont)
Within a given folder, the .sql scripts are run PRIOR to any subfolders use whatever folder structure you like nest as deep as you like If you use our “suggested” folder structure the
ordering dependencies are handled for you If you wish to skip a folder from execution,
preface the folder with "_". "_Documentation"
If you wish to skip a file from execution either rename it to something other than .sql "zero out" (blank) the file.
“Rules” (cont) Any valid TSQL will be executed. You don't need
any special formatting When an ERROR occurs (Severity >= 11) then MD3
aborts and displays and logs an error. Simply fix the error and redeploy. No need to restore and redeploy. Deployments are “full-fidelity”. If there is failure it
restarts from the beginning 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 at the END
.\00Pre-install\00CreateDatabase.sql ALWAYS runs FIRST. You can place db creation logic/backup/whatever
“Rules” (cont)
Actually, all of these rules are just suggestions. You can easily modify RunMD3.ps1 with almost ZERO experience with PoSH to get it to do whatever you like.
I did!
Benefits of RunMD3.ps1
No third party software to deploy or license Runs any TSQL. No special formatting. You can create a CI build loop with ant, cruise
control, jenkins, msbuild, etc If you don’t like PoSH build/use something
else, the important things are: Understanding how to deploy stateful objects How to deploy stateless objects in the right order
Works with any VCS MD3 works equally well with Oracle, MySQL,
etc. It’s just SQL!!!
Benefits (cont)
Do your own release numbering (MD3 doesn’t care about it)
Open source Does full logging to both MD3.Log table
and the console (even pipe it to a .log file) WARNINGs are logged (trusted constraints) FAILS on the first error, which rarely
happens. Will script out your existing db for you
Benefits (cont) No GUI necessary…you can visualize the
deployment by looking at the folder structure Can backup your db prior to an upgrade, at
the end, stop/start replication… WHATEVER you need
No enforced .sql file naming convention. Use whatever works for you. Just remember, everything is run alphabetically, files first, then subfolders.
Has a stateful deployer for ModelData Has “validators” to compare databases to
ensure everything is deploying correctly
top related