Rule Archetype Pattern in SQL Modeling Services – Part 4

As I noted in Part 3 of this series, the subject of this post will be the work necessary to deploy Contoso’s ‘M’ business rule model to the Repository. Given that Contoso’s model implementation leverages the PatternApplication sample, there will be some additional work involved over doing a standard deploy using Visual Studio or using the ‘M’ tool chain (i.e., mx.exe in this case).

Deploying with the PatternApplication Sample

As I noted in Part 3, the PatternApplication.m file declares the desired Repository design pattern configuration of Contoso’s business rule model. Part 3 also discussed how this configuration metadata is processed by a stored procedure that performs the required additions and changes to Repository database objects. The calling of this PatternApplication stored procedure is accomplished via the ‘M’ tool chain’s support for custom SQL processing. The details of adding the SQL processing needed to call the PatternApplication stored procedure is documented on MSDN here. Since the addition, configuration, and coding of the post.sql file is straightforward, I won’t waste space detailing it here.

However, it is worth noting that the use of a post.sql file complicates things from the perspective of using Visual Studio. Specifically, at the time of this writing Visual Studio doesn’t handle ‘M’ model deployments with post SQL processing very gracefully. Additionally, in the current Modeling Services CTP there are considerations for iterative deployments of Repository-based models. For interested readers there’s some MSDN coverage on the subject located here. Due to these complexities, the Contoso business rule model will use a small batch script for deploying the ‘M’ model to the Repository.

Deploying the Model with a Batch Script

<Author’s Node>This is a very simple implementation to illustrate the key ideas. In an actual Production scenario the use of MSBuild to create a more robust solution would be more likely. No flame mails, please :-)</Author’s Note>

The first step is to add a .bat file to the RulesModel project:

1. Add a new item to the RulesModel project of type “Text File”. Name this file “DeployRulesModel.bat” and click the “Add” button.

Next, configure the DeployRulesModel.bat to be copied to the build output folder:

1. In Visual Studio right-click on the DeployRulesModel.bat file and select “Properties”
2. In the Properties pane, under the “Advanced” section set the value of “Copy to Output Directory” to “Copy if newer”

Per MSDN guidance, the deployment script will execute the following high-level steps:

1. A new RulesModel database will be created within SQL Server
2. The Repository (i.e., the Base Domain Library) will be loaded into the RulesModel database, configured for both Auditing and Change Tracking
3. The PatternApplication sample will be loaded into the RulesModel database
4. The Contoso ‘M’ model for business rules will be loaded into the RulesModel database

Here’s the code for the .bat file:

    1: mx.exe create /database:RulesModel /force
    2:  
    3: mx.exe install "C:\Program Files\Microsoft Oslo\1.0\bin\Repository.mx" /database:RulesModel /server:(local) /property:rct=+ /property:ra=+
    4:  
    5: mx.exe install "C:\Program Files\Microsoft Oslo\1.0\bin\PatternApplication.mx" /database:RulesModel
    6:  
    7: mx.exe install RulesModel.mx /database:RulesModel /server:(local)

It is worthy to note the following in terms of executing the script:

1. The script assumes that either mx.exe is local to the script or that the PATH environment variable has been updated to include mx.exe’s location
2. The script assumes that the Modeling Services CTP is installed in the default location
3. The script assumes that the PatternApplication sample has been compiled to the default CTP install location
4. The script assumes that the RulesModel project has been compiled locally to the where the script is being executed

A successful deployment using the script should look like the following:

For interested readers and update version of the Visual Studio solution is available from my SkyDrive, the link to the files is at the end of the post.

Verifying the Deployment

Now that the Contoso model has been deployed, it’s pretty

trivial to verify that the correct configuration of the PatternApplication metadata was applied to the RulesModel database. To check on the foreign keys being added to the RulesModel tables, just fire up SQL Server Management Studio (SSMS), connect to your DB server, and navigate to Databases –> RulesModel –> Tables –> RulesModel.OperationsTable. If you take a look at the Keys folder for the table you should see the following:

Sweet. The Foreign Key was applied to the table per the metadata in the PatternApplication.m file. Checking up on the triggers for the views is a good idea. To see these navigate to Databases –> RulesModel –> Views –> RulesModel.Operations. If you take a look at the Triggers folder for the view you should see the following:

Excellent. If you check the other tables and views of the RulesModel schema you should see the same pattern repeated.

Next Time

OK, the Contoso business rule model is coming together nicely. Repository patterns have been configured for the model via PatternApplication metadata and the model can be deployed successfully to SQL Server. That brings us to the final post of the series – creating some Repository Folders for segmenting Contoso’s business rules and applying Repository security to those folders.

Until then, any comments or feedback is welcomed.

SkyDrive Files