Failed to Load Group : BizTalkMgmtDB : Issue while opening BizTalk administration console

We had two BizTalk servers added in a group and it is connecting to remote external sql server. Every thing was going fine.. life was coformtable.. until one fine day recieve a email marked Urgent and Subject :
with these two images.

One node of BizTalk Group was working fine and it was really a big relief as we can easily rule out any possible issue realted to SQL server.
So the problem was with the other node and there were numerous BizTalk errors logged by Host Instances, where host instance is trying to start but BizTalk is not able to make connection with the sql server.
Also, in Secuity log there were many audit failures of Windows filtering service.
As our BizTalk most instance were trying to establish the connection with SQL server but al incoming data packets were filtered by this service.Therefore, neither BizTalk Host Instances or BizTalk Administrator Console is able to connect to the SQL Server.



So following are the checklist:
1. Ping SQL server to verify if server is resolved properly.
2. Verify the name of SQL instance where BizTalkMgmtDB is place, there shouldn't be any typo mistake
3. SQL Server is running(not in my list but adding for the completion of blog)
4. Appropiate port (in this case UDP 1434) should be added in exception list or disable firewall(but definately check with server admin before disabling)

Following it the link(which saved my a$$): http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx


Good Luck :)

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction

There are many times I have encountered issues with DTC when working with BizTalk and SQL. In most of the cases either DTC isn't running or configured properly or some sort of security mismatch between the client and server DTC.

But recently, I seen some warings in eventlog wheneven I am enabling my SQL receive location and finally it gets disabled as SQL adapter didn't recover from the failures but this time the reason for these error wasn't any of them mentioned above.

Following are the warnings I was getting in windows eventlog:
HRESULT="0x80040e14" Description="The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction." - Most of the time

Fewer times I got the following errors/ warning:
1. Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction
2. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0
3. Cannot roll back . No transaction or savepoint of that name was found

I was tired of watching Abort Transaction Counts on MSDTC :(

Root cause:
In our case, we are calling an wrapper stored procedure which houses the call to 6-7 other stored procedures and one of the Stored procedure was giving an error and transactions were not handled in the right manner.
Strange thing is that, this time the orginal error is getting suppressed because of further issues in transactions which made me really running like headless chinken to look where exactly the issue is. All I was looking at the possiblities near DTC and BizTalk.
It was SQL store procedure that was the real culprit.
So I've learnt its better to use the default isolation level(serializable) of SQL Adapter but if are overriding it in Stored procedure handle transactions very carefully and remember good exception handling mechanism is key of solving the issue faster.. really fast..:)
Gud Luck