BizTalk Management and MessageBox Databases Sync Issue – Part1- Deployment
While using BizTalk Clustered Environment with Availability Groups (Not Failover Cluster), an issue was found while deploying BizTalk Application.
BizTalk Management and MessageBox Databases goes out sync (due to some specific or unknown reason), we get error while deploying an application. (it could be 1 or more applications; in my case it was only one)
Deploying another version of same application in BizTalk Environment creates issue.
It is noticed that there are activation subscriptions left even after Un-deploying the BizTalk application. Its impossible to deploy the BizTalk application again with Orphan Subscriptions.
Executing AddApp command, it gives error
Error: Application registration failed because the application already exists
Executing RemoveApp command for same application , it gives error
Error: Application ” M ” not found in configuration database.
This proves there is contradiction in BizTalk Database instances.
Event Log produces generic log.
Unable to communicate with MessageBox BizTalkMsgBoxDb on SQL Instance ABCD11111\MessageBoxInst. Error Code: 0x8004d00e. Possible reasons include:
1) The MessageBox is unavailable.
2) The network link from this machine to the MessageBox is down.
3) The DTC Configuration on either this local machine or the machine hosting this MessageBox is incorrect
- Possibly, Maximum Degree Of Parallelism for MessageBox Database (MDOP) was not set to 1. It was grater than 1 during this period of time which have caused database issues when databases are Synched in AG.
- Orphan Subscriptions of application.
- Orphans of application.
Steps for Tracing the issue
- Executed command BTSTask ListApps
- Executed command BTSTask ListApp /ApplicationName:”M………”
- Check Test DTC settings.
No trace of application found.
Then the last option left is to dig in BizTalk Databases.
- Run SQL profiler on MessageBox and Management databases while executing BTSTask commands and collect the trace log.
- Run BizTalk Trace using “PSSDiagForBizTalk” while executing BTSTask commands
Try to search for application name in both trace logs and identify stored procedure or process creating issue.
This issue appears in MessageBox database due Orphans of Application. Execution of one stored proc i.e. bts_AdminAddModule got interrupted during deployment and application was not deleted from Modules and Services tables. Modules and Services tables in MessageBox are mainly responsible for subscriptions.
Note:- Do Not forget to stop Hosts while performing below activity.
- Delete Orphan Subscriptions using subscription IDs. (Using SQL Query or a tool you prefer)
- Run below SQL query and get nModuleID of application creating issue. SELECT * FROM [BizTalkMsgBoxDb].[dbo].[Modules] WITH (NOLOCK)
- Check for Instance in Application and Instances tables for nModuleID. Query result must be empty.
- Run below queries BEGIN TRANSACTIONDELETE FROM [BizTalkMsgBoxDb].[dbo].[Services] WHERE nModuleID = 111DELETE FROM [BizTalkMsgBoxDb].[dbo].[Modules] WHERE nModuleID = 111ROLLBACK TRANSACTION