DaveWentzel.com All Things Data
MD3 and The Four Rules
In the last few posts I covered what MD3 is (Metadata Driven Database Deployments) and why it is better than hand-crafted DDL. In this post we are goingto cover The Four Rules of Database Deployments. Regardless of whether you decide to use MD3 or not, if you understand The Four Rules your database deployments will run much smoother.
MyIndex, created on
dbo.Examplewith a fill_factor of 90, on column
Foo, with included_column
Bux. Here is the DDL:
MD3.Indexes), which looks very similar to the "sys" system tables (in this case
sys.indexes). Here's a screenshot that shows how to add an index to the HumanResources.Department table:
MD3.CreateIndex(which you can call instead of populating the
MD3.Indexestable) similar to the nearby graphic that is adding the index
MD3.CreateIndex(and each column in the
MD3.Indexestable (see above screenshot) is a metadata property that we can declare for an index. These properties cover just about every option you can think of regarding an index. There are some properties missing...for instance, there is no option for filtered indexes because I've never really found a good use case in my career where I absolutely needed a filtered index. Since MD3 is open source you could always add this functionality if you needed it. Here are some other index properties that are missing and why:
|Index Property||Why it is missing?|
|WHERE <filter predicate>||filtered indexes. You can always add this yourself.|
|IGNORE_DUP_KEY||I've never found a good use for this option.|
|STATISTICS_NORECOMPUTE||I've never found a need for this.|
|ONLINE = ON|OFF||
So the question is...how does MD3 actually build the DDL under the covers and what makes it so much better than hand-crafted DDL?
- if the object exists by name and by "properties", then silently continue, there is no work to do. In other words, there was no change in the object between deployments. The "properties" are the parameters to the
MD3.Createprocedures (see the screenshot above).
- if an object exists with the same “properties”, but the name is different, then RENAME the object. Example: I have an index with the same column list, order, fill factor, on the same filegroup, with the same compression, etc...but it is named differently. All MD3 does is run
sp_rename. This scenario occurs, for instance, when your DBA adds an emergency index to a table and you check in the same index structure, but you use your naming conventions.
- if the object exists by name, but the "properties" are different, ALTER the object accordingly. Example, you have an index with the same name and mostly the same properties, but you need to change the INCLUDED col list and FILLFACTOR. MD3 handles this without esoteric, hand-crafted DDL. Usually an ALTER can bring the current object up to the desired properties, otherwise DROP/CREATE is used. The most important thing to remember is that YOU don't have to code the DDL and worry about covering all possible scenarios.
- Else, CREATE object with properties as defined. At this point MD3 realizes this is a missing object and creates it according to your declarations.
MD3.CreateIndex(tsqlt unit tests available on request).
|Database State||Expected Result||Why?||Covers which of The Four Rules|
||FAILURE||This scenario will be caught during continuous integration testing and will never happen during a production deployment. (See Continuous Integration Testing with MD3)||None.|
||Index created||The index will be created with the declared properties.||4|
|An index exists called
||Index renamed||The index will be renamed using
||Index altered||When possible the index will be ALTERed. Otherwise the existing index will be dropped and the desired index will be created. In some cases, depending on what is being changed, an index cannot be ALTERed.||3|
||Silently continue||There is no work to do.||1|