Sending SQL Notification to C# Application using Service Broker
Posted by Sourabh Agarwal on September 14, 2012
On one of my earlier post on CDC, I was asked if we can send out CDC notifications to a C# application. The ensuing post is an attempt to answer that question. This post does not specifically talk about sending CDC notification, but a general guidance on how Service Broker can be leveraged to send out notifications to an External Application.
In order to send out the notification using Service Broker to an external application, we would need to make use of the SQL Server Service Broker External activator, which is distributed as part of the SQL Server Feature Pack (available for 2005/2008/2008R2/2012). The links for the respective SQL Versions (latest versions).
SQL 2008 R2 SP1:http://www.microsoft.com/en-us/download/details.aspx?id=26728
From a work flow perspective, the following events take place.
The Application event or service communicates with the Notification Service (Event Notification). The External Activator services is continuously listening on the notification queue. When it encounters a message in the Queue, it invokes the External Application, which can process the required operation.
Once the External Activator is installed, it works as a NT Service.
Before the External Activator can be configured, the Event Notification mechanism needs to be created in SQL. This event notification would be fired on the Queue Activation Event on the Application Queue. In this example, I have implement an Async Trigger, which uses Service Broker to activate an external application.
Setting up the Async Trigger Code
Next we need to setup the objects for Event Notification
Once we have setup the Event notification, we need an create the External Application as per our requirement. In my case the External Application, takes the message from the Service and inserts it into a secondary table.
Once the application has been created, the last step would be to configure the EA.
In order to configure the EA to listen to listen for notification it needs to be configured accordingly using the configuration file which is created as part of the installation.
The above snapshot of the Configuration File shows the important parameters which needs to be specified for the EA to function properly.
- Notification Service Name: Name of the Service which would be used for Notification.
- Connection String: Information about which server/database on which the Service and the queues are created.
- Application Name: Name of the application which would be invoked as part of External activation.
- On Notification: The four-part name of the queue on which to listen for notification.
- Launch Info: The executable path for the application which would be launched as part of the notification. The Concurrency section indication how many instance of the applications to be invoked in case multiple messages arrive in the queue.
In my case, there were some issues with launching the application from the External Activator which were resolved after setting some policies and UAC controls.
Hopefully this helps!!