Freigeben über

Get Started With Using External Activator

  • Artikel

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 removed

  • Anonymous
    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 blog

  • Anonymous
    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 removed

  • Anonymous
    May 27, 2012
    The comment has been removed

  • Anonymous
    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 2012

  • Anonymous
    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.