Receive query notifications On Multiple Receive Locations from SQL using BizTalk Server
Consider a scenario where you have multiple receive locations created as part of different BizTalk applications configured to receive query notifications for the same table (e.g. Employee) in the same database. If a hundred records are inserted into the same table, all the receive locations will get the notification message. To effectively receive notifications across multiple receive locations, you can call operations from your BizTalk application in such a way that if a notification is received by one receive location, the other receive location does not get the same notification. So, you can effectively load-balance notifications received on multiple locations.
The tasks required to set up an orchestration to load-balance receiving notifications are same as that for Receive Query Notifications Incrementally from SQL using BizTalk Server. This topic lists the only the difference between the two approaches.
Load-Balancing Query Notifications Across Multiple Receive Locations
Like in the topic Receive Query Notifications Incrementally from SQL using BizTalk Server, you configured incremental notifications by executing an UPDATE statement on the records that are already notified for. To configure load-balancing, you could execute a stored procedure that deletes the records that have been notified for. For example, consider a stored procedure PROCESS_EMPLOYEE with the following definition:
DECLARE @var int
SELECT TOP 1 @var = Employee_ID FROM Employee
SELECT * FROM Employee WHERE Employee_ID=@var
DELETE FROM Employee WHERE Employee_ID=@var
When you execute this stored procedure as part of the BizTalk application, the record for which notification is already received gets deleted. So, the other receive location gets notification for the next record.
Here are the high-level steps you must perform to configure load-balancing for receiving notifications.
Create schema for Notification (inbound operation) and PROCESS_EMPLOYEE stored procedure (outbound operation).
Add an orchestration and add three messages for receiving notification, executing stored procedure, and getting response for the stored procedure.
Create an orchestration by adding Send and Receive shapes, Construct Message shape, and ports. You can use the same sample code for constructing a message to invoke the PROCESS_EMPLOYEE stored procedure. Note that while performing the operation in BizTalk Server Administration console, you must have the request message for the PROCESS_EMPLOYEE stored procedure in the location C:\TestLocation\MessageIn. You do so because the code snippet you invoke as part of the orchestration created in Receive Query Notifications Incrementally from SQL using BizTalk Server creates a request message based on the request XML present in C:\TestLocation\MessageIn.
Build and deploy the application. To demonstrate load-balancing, you must deploy this orchestration at least on two different computers that have BizTalk Server and SQL adapter installed.
In the BizTalk Server Administration console on both the computers, specify the following binding properties for the WCF-Custom or WCF-SQL receive location:
Binding Property Value InboundOperationType Set this to Notification. NotificationStatement Set this to:
SELECT Employee_ID, Name FROM dbo.Employee WHERE Status=0
Note: For notification statements, you must always specify the table name along with the schema name. For example,dbo.Employee.NotifyOnListenerStart Set this to True. Start the BizTalk application.
To start receiving notifications, insert a hundred records into the EMPLOYEE table. While doing so, make sure the request XML for invoking the PROCESS_EMPLOYEE stored procedure is available in C:\TestLocation\MessageIn.
Monitor the location (on both the computers) where the BizTalk application will be dropping the notification messages. You will notice that of the hundred records inserted, one location gets notifications for some records while the other location gets notification for the remaining records. Together, both the locations will get notification for all the hundred records.