Lifetime of a busines rule policy

These are 3 different states of a policy:

1. Editable - this is the initiate state of a new policy. In this state, you are able to add, remove, edit. You can also "save" the policy for later editing.
2. Published - after a policy has been published, it becomes read-only. In this state, you can remove it, but you cannot move it back to its initiate state (Editable). The policy is not available for execution in this state. This is considered as staging state before deployment.
3. Deployed - this is the state where the policy can actually be used by using the call-rule shape or the policy class. In this state, it cannot be removed. It has to be un-deploy to "Pulished" state before a policy can be removed. Once policy is un-deployed, it will not be available for execution any more.

ContextAdder XMLReceive Pipeline Component (with BRE or XML configuration)

I came across Jon Flander's very useful ContextAdder pipeline component a while ago. I found it to be very useful. In short it's a pipeline component where

"you can place in any stage of either a Receive or Send pipeline, and you can configure it with a collection of Context properties (simple written properties or promoted properties). The component will just blindly put whatever values you configure it with into the context. This is just useful as a generic way to add properties into the Message Context when needed (and it is needed pretty often - at least I need it pretty often)."

Though I love the tool very much, I found a limitation with the tool where the number of context properties can be written/promoted has to be defined at development time ONLY. Having said that, I think it would be great if the number of context properties to be written/promoted can be configurable to any number of context properties at runtime.

In order to do that, base on Jon Flander's sample code, I created 2 Context Adder pipeline components, which base on BRE and XML configuration.

1. BRE Context Adder Pipeline Component: It calls the rule engine in the pipeline, the result of the policy will return the context properties (with namespace, key and value) to be written/promoted in the message context.

sample code and sample MSI could be found here at my hotmail skydrive

2. Context Adder Pipeline Component with XML configuration:

sample code and sample MSI could be found here at my hotmail skydrive

Here is a sample of the configuration XML. As you can see, the number of context properties to be written/promoted is highly configurable. New property could be added/removed anytime during run time.

<tns:ContextAdder xmlns:tns="http://GML.BizTalk.ContextAdder.Schemas" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <tns:ContextProperties>
  <tns:ContextProperty>
   <tns:Key>EnableOrchestrationDebug</tns:Key>
   <tns:Namespace>http://GML.BizTalk.PropertySchemas.DebugFlag</tns:Namespace>
   <tns:Value>true</tns:Value>
   <tns:PromteProperty>true</tns:PromteProperty>
  </tns:ContextProperty>
  <tns:ContextProperty>
   <tns:Key>EnableAllDebug</tns:Key>
   <tns:Namespace>http://GML.BizTalk.PropertySchemas.DebugFlag</tns:Namespace>
   <tns:Value>true</tns:Value>
   <tns:PromteProperty>true</tns:PromteProperty>
  </tns:ContextProperty>
  <tns:ContextProperty>
   <tns:Key>EnablePipelineDebug</tns:Key>
   <tns:Namespace>http://GML.BizTalk.PropertySchemas.DebugFlag</tns:Namespace>
   <tns:Value>true</tns:Value>
   <tns:PromteProperty>true</tns:PromteProperty>
  </tns:ContextProperty>
 </tns:ContextProperties>
</tns:ContextAdder>

OLE DB provider "SQLNCLI" for linked server "HOSTNAME\SECONDARY" returned message "Query timeout expired". Warning: A Remote Query Timeout may have occurred

I got this error when "Backup BizTalk Server" SQL Server Agent Job was being executed.

Processed 630 pages for database 'BizTalkDTADb', file 'BizTalkDTADb_log' on file 1.
BACKUP LOG successfully processed 630 pages in 0.511 seconds (10.084 MB/sec).
Processed 6 pages for database 'BizTalkMgmtDb', file 'BizTalkMgmtDb_log' on file 1.
BACKUP LOG successfully processed 6 pages in 0.123 seconds (0.378 MB/sec).
Processed 1 pages for database 'BizTalkRuleEngineDb', file 'BizTalkRuleEngineDb_log' on file 1.
BACKUP LOG successfully processed 1 pages in 0.324 seconds (0.003 MB/sec).
Processed 12 pages for database 'SSODB1', file 'SSODB1_log' on file 1.


Server: Msg 50000, Level 16, State 1, Procedure sp_MarkAll, Line 118
Failed running the log backup on HOSTNAME\SECONDARY.BizTalkMsgBoxDb
BACKUP LOG successfully processed 12 pages in 0.053 seconds (1.835 MB/sec).
OLE DB provider "SQLNCLI" for linked server "HOSTNAME\SECONDARY" returned message "Query timeout expired".
Warning: A Remote Query Timeout may have occurred


Scheduler:
processed 1 pages in 0.041 seconds (0.024 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 2 pages for database 'SSODB1', file 'SSODB1_log' on file 1. [SQLSTATE 01000] (Message 4035) Failed running the log backup on HOSTNAME\SECONDARY.BizTalkMsgBoxDb [SQLSTATE 42000] (Error 50000) BACKUP LOG successfully processed 2 pages in 0.027 seconds (0.322 MB/sec). [SQLSTATE 01000] (Error 3014) OLE DB provider... The step failed.

Based on the error message, it seems like the backup script time out before it's completed. Looking at the backup history with the following SQL script, I found that the last time the BizTalkMsgBoxDB is successfully back up is 3 months ago. Checking the size of  BizTalkMsgBoxDB, I found that BizTalkMsgBoxDB_log database file is already up to 78 GB.

SELECT * FROM [BizTalkMgmtDb].[dbo].[adm_BackupHistory]
WHERE [DatabaseName] LIKE '%MsgBox%' ORDER BY [DatabaseName], [BackupDateTime]

Before I go any further, I think it's worthwhile to mention the architecture of the SQL servers. As shown in the diagram below, the SQL Services are clustered in an Active/Active configuration to maximize the available hardware resources. The high availability is achieved by configuring BizTalk databases separately of one another. As shown, BizTalk MessageBox database is deployed in one server and the other dependant databases are deployed to the other server within the cluster.

SQL Server Node A	SQL Server Node B
BizTalkMsgBoxDB(MessageBox)	BizTalkMgmtDB(Management)
	BizTalkDTADB(Tracking)
	SSODB(Single Sign-On)
	BizTalkRuleEngineDB(Rule Engine)

As such, a linked server is added in each node. For example, in Node A, a linked server is setup to link to database server that hosts databases such as BizTalkMgmtDB, BizTalkDTADB and etc. In Node B, a linked server is setup to link to the database server that hosts the BizTalkMsgBoxDB database.

Because of this configuration, whenever the "Backup BizTalk Server" SQL Server Agent Job is being executed, the linked server is utilized. With further investigation, I found that “Remote query timeout” of HOSTNAME\SECONDARY (SQL cluster Node B - BizTalkMsgBoxDB ) is set to 600 seconds, which is exactly the same amount of time the "Backup BizTalk Server" SQL Server Agent Job ran before it failed with aforementioned error. With this finding, I decided to change the "Remote query timeout" value to a higher value (such as 0 - no timeout).

Unfortunately, this change didn't resolve the problem. The job still failed after 10 minutes with exactly the same message. What else could go wrong?

Since there's only 2 servers involved in running the "Backup BizTalk Server" SQL agent job, if the error is not caused by HOSTNAME\SECONDARY (SQL cluster Node B - BizTalkMsgBoxDB ), it must be caused by the other server (SQL cluster Node A - other BTS databases other than BizTalkMsgBoxDB ). With this in mind, I decided to change the "Remote query timeout" value for cluster Node A to "0" as well.

After the change is made to cluster Node A, I re-ran the backup job again and bingo it ran successfully without any error.