Get Started With Using External Activator
In the blog post Announcing Service Broker External Activator, we introduced Service Broker External Activator and showed what benefits a broker user can get from using it. In this article, we'll get you started with using external activator in four steps:
· How to create a notification service
· How to create an event notification to associate your user queue with the notification service
· How to modify external activator configuration file to connect to the notification service you just defined and to launch applications when messages are arriving at your user queues that are being monitored
· A few things External Activator expect you to do
To begin with, external activator must connect to a notification service before it can do anything useful. If you don't have a notification service yet, here is the script you can use to create one:
-- switch to the database where you want to define the notification service
USE my_db
GO
-- create a queue to host the notification service
CREATE QUEUE my_notif_queue
GO
-- create event notification service
CREATE SERVICE my_notif_svc
ON QUEUE my_notif_queue
(
[https://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
)
GO
Next, let's create an event notification object so whenever messages have arrived at the user queue you are interested in (my_user_queue), notifications will be posted to the notification service we just created above:
CREATE EVENT NOTIFICATION my_evt_notif
ON QUEUE my_user_queue
FOR QUEUE_ACTIVATION
TO SERVICE 'my_notif_svc' , 'current database'
GO
The above example assumes my_user_queue and my_notif_svc reside in the same database. In the case of my_notif_svc is in another database, 'current database' should be replaced with the broker instance GUID where my_notif_svc is defined in.
Assume you have already downloaded the Service Broker External Activator MSI package and installed external activator to C:\Program Files\Service Broker\External Activator\. Suppose the message processing application that you want external activator to invoke is in c:\test\myMessageReceiver.exe, and your notification database server is running on my_pc01. Here is what your configuration file will look like (C:\Program Files\Service Broker\External Activator\config\EAService.config):
...
<NotificationServiceList>
<NotificationService name="my_notif_svc" id="100" enabled="true">
<Description>my notification service</Description>
<ConnectionString>
<Unencrypted>server=my_pc01;database=my_db;Application Name=External Activator;Integrated Security=true;</Unencrypted>
</ConnectionString>
</NotificationService>
</NotificationServiceList>
<ApplicationServiceList>
<ApplicationService name="myMessageApp" enabled="true">
<OnNotification>
<ServerName>my_pc01</ServerName>
<DatabaseName>my_db</DatabaseName>
<SchemaName>dbo</SchemaName>
<QueueName>my_user_queue</QueueName>
</OnNotification>
<LaunchInfo>
<ImagePath>c:\test\myMessageReceiver.exe</ImagePath>
<CmdLineArgs>whatever cmd-line arguments you need to pass to your receiver application</CmdLineArgs>
<WorkDir>c:\test</WorkDir>
</LaunchInfo>
<Concurrency min="1" max="4" />
</ApplicationService>
</ApplicationServiceList>
...
We now have specified notification service name, notification database connection string, the four-part user queue name whose activities we like to watch, and the message-receiving application we like External Activator to invoke when messages are coming in. We have also configured the min attribute of the <Concurrency/> element to 1, which means External Activator will launch a single instance of c:\test\myMessageReceiver.exe upon the first QUEUE_ACTIVATION notification message received for my_user_queue. The max attribute is set to 4, meaning as many as four instances of the same application can be launched if service broker sees my_user_queue are not being drained fast enough (e.g., messages keep accumulating). We recommend max to be set to the number of CPU cores of the machine where External Activator is deployed (my_pc01) to take full advantage of the machine power.
A couple of things External Activator expects you to do it right include:
· The windows login-account external activator service is running under needs to have the set of permissions that are listed in Security Implications section of C:\Program Files\Service Broker\External Activator\bin\<language_id>\ssbea.doc in order to connect to the notification service and database to read notification messages from the notification service queue (my_notif_queue). Assuming the service account is my_domain\my_username, we have provided the SQL scripts below that can be used to set up the minimum set of permissions required by external activator. Please refer to Service Broker Identity and Access Control page for more information about what permissions are expected by service broker applications, and Service Broker Tutorials for more information about broker programming in general.
-- switch to master database
USE master
GO
-- create a sql-login for the same named service account from windows
CREATE LOGIN [my_domain\my_username] FROM WINDOWS
GO
-- switch to the notification database
USE my_db
GO
-- allow CONNECT to the notification database
GRANT CONNECT TO [my_domain\my_username]
GO
-- allow RECEIVE from the notification service queue
GRANT RECEIVE ON my_notif_queue TO [my_domain\my_username]
GO
-- allow VIEW DEFINITION right on the notification service
GRANT VIEW DEFINITION ON SERVICE::my_notif_svc TO [my_domain\my_username]
GO
-- allow REFRENCES right on the notification queue schema
GRANT REFERENCES ON SCHEMA::dbo TO [my_domain\my_username]
GO
· Your application (c:\test\myMessageReceiver.exe), when launched, must issue RECEIVEs and consume messages from your user queue (my_user_queue) before it exits for service broker to post more event notifications when either your user queue is not completely drained at the time your application finishes or there are new messages coming in later after your application quits. For more details about how broker activations work, check out Understanding When Activation Occurs of SQL Server Books Online.
If you have done all the above necessities, now try to start your external activator service, and send a few messages to your user queue, you should probably be able to see the messages are read and processed. But if not, in our next EA blog article, and we'll show how you can trouble-shoot external activator to find out what have gone wrong. Stay tuned! :)
Comments
Anonymous
May 28, 2009
The comment has been removedAnonymous
May 28, 2009
Please define all broker related objects in a database where broker is turned on (don't use master).Anonymous
November 09, 2009
Can you add a sample external application (myMessageReceiver.exe) on this blogAnonymous
January 20, 2010
There is no movement in this blog, is this good source to stay up to date with SQL broker service?Anonymous
January 22, 2010
Fear not. The blog is still the source for broker info. We are all pretty tied up for a while on development for the next release, but will be posted anon.Anonymous
February 12, 2010
Thanks Tom, thats gave me lot more confidence in sql server broker services.Anonymous
March 11, 2010
Can some please add more detail about the user queue? I'm struggling to understand how the user queue is configured and more importantly how to send messages to it. Also, more information about the myMessageReceiver.exe application would be very helpful.Anonymous
March 26, 2010
Does External Activator work on SQL Server 2005? Please advise.Anonymous
June 22, 2010
External activation works fine with both SQL Server 2005 and SQL Server 2008. Over WMI. Your can see code sample on my blog page www.queue.net.ru/.../external-service-broker-activation-with.html (on russian language).Anonymous
September 06, 2010
Any thought on convergence w/ WAS and WCF here?Anonymous
March 29, 2011
The permissions part didn't work for me. I ran the TSQL with the service account assigned to the service, the notification is received but I then get an error that my target queue can not be read because it doesn't exist or has not enough permissions. When I make my service account dbowner. it works fine. Any help would be welcome!Anonymous
October 23, 2011
Can EAS call an executable that's running on another machine, that does not have sql server 2008/2005 installed?Anonymous
December 28, 2011
"Can EAS call an executable that's running on another machine, that does not have sql server 2008/2005 installed?". +1 I would also be curious to know if this is possible, for a true scaleout solution surely this must be possible?Anonymous
February 03, 2012
The comment has been removedAnonymous
May 27, 2012
The comment has been removedAnonymous
February 04, 2013
Hi, does anyone know what causes these messages to appear in the event log? "WARNING An event notification has been dropped" Thanks.Anonymous
March 03, 2013
Hi, I have error: EXCEPTION ERROR = 32, No enabled application monitor is on behalf of queue [PARANGHOSHI-PC].[ElectronicPaymentsLog].[dbo].[AppMessageQueue]. how can I solve it? plz help me ;(Anonymous
October 16, 2013
stackoverflow.com/.../no-enabled-application-monitor-is-on-behalf-of-queue-xyz See the above link if you are phasing the issue, the config and message structure has to be accurate. Then only it will work.Anonymous
November 15, 2013
Is there a release of External Activator which is specific to Sql Server 2012Anonymous
January 31, 2016
Could someone explain how to send a messages using this service broker architecture? For instance, if sending a simple message like this: DECLARE @conversation uniqueidentifier, @msg varchar(max)='Hello world!'; BEGIN DIALOG @conversation FROM SERVICE ????????? TO SERVICE 'my_notif_svc' ON CONTRACT ????????; --Sends a messagewor SEND ON CONVERSATION @conversation MESSAGE TYPE [????????] (@msg); What contract name, and service in the 'FROM' clause should I use if it didn't was created? Thank you in advance for your help.