An alternative to SQL Receive Adapter

Details about SQL Receive Adapter can be found here.

It simply Polls SQL Server DB for data.

Though this is an useful adapter, at times, it increases load on SQL Server with unnecessary calls.

This adapter is not worth using in scenarios where you are not sure of getting data all the times.

In such cases you can go for alternative design, wherein you can get notification to BizTalk Server, whenever new data is available in SQL Server.

This design can be done as follows.

NewData Comes to SQL Server -> Write a trigger that fires upon new data arrives -> Call a .NET function from the trigger -> From the .NET Function, notify BizTalk Server that new data has arrived (Say for eg: Save a dummy file in a folder and let BizTalk be notified on the new data or Call a BizTalk Orchestration Exposed as  a Service etc) -> Have your Logic to Fetch the new Data using an Stored Proc.

Rather than going with the standard polling approach, this new design will help us to use the features available with SQL Server and reduce unnecessay calls to SQL Server.

Hope its helpful.

-Shiv