SQL Receive Location disables after n-retries

Question

We have one SQL Receive Location in our BizTalk 2010 Application. It is pointed at a server hosting SQL Server and works every day without fail.

Today SQL Server was down for some reason and after 5 retries the SQL Receive Location became disabled.

I'm looking for some way to avoid this automatic disabling by allowing a larger number of retries or disabling this feature if possible. Does anyone have any suggestions as to how I may overcome this?

Answer 1

There isn't a way to disable this feature AFAIK, so your options are:

1. If you are using the WCF-SQL Adapter to poll SQL, under the Adapter's Binding Tab, set the Close, Open and Receive Timeout settings to values that you are comfortable with and ones which will provide sufficient time for BizTalk to 'ride out' the failure. In the Messages Tab, under 'Error Handling', there is also an option to 'Disable Location on Failure' - keep this option unticked.

2. If you are using the 'old' SQL Adapter (i.e. not WCF), you just have to live with the fact that the Receive Location will disable itself if it cannot connect to the SQL Server.

However, it would be worthwhile introducing a strategy to handle these types of failure. When a Receive Location disables itself, an Error message is written to the Application Event Log, some fairly unsophisticated software could check for particular errors in the Event Log and send e-mail alerts. Alternatively, you could also consider using specialised Ops management software (SCOM / BizTalk360 etc.) to monitor your BizTalk environment and alert your when there is a failure of a service.