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


      (


            [http://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 (20)

  1. modis says:

    I am continuously seeing this error message in the error logs and after doing some research found that a BRKR TASK is the cause. The BRKR TASK DBName is master but service broker is not enabled on master database.

    Message

    An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

  2. junan_msft says:

    Please define all broker related objects in a database where broker is turned on (don’t use master).

  3. michael G says:

    Can you add a sample external application (myMessageReceiver.exe) on this blog

  4. mamu says:

    There is no movement in this blog, is this good source to stay up to date with SQL broker service?

  5. Tom says:

    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.

  6. mamu says:

    Thanks Tom, thats gave me lot more confidence in sql server broker services.

  7. Joseph says:

    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.

  8. Alex says:

    Does External Activator work on SQL Server 2005? Please advise.

  9. External activation works fine with both SQL Server 2005 and SQL Server 2008. Over WMI. Your can see code sample on my blog page http://www.queue.net.ru/…/external-service-broker-activation-with.html  (on russian language).

  10. bkjuice says:

    Any thought on convergence w/ WAS and WCF here?

  11. Wim says:

    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!

  12. Ruben says:

    Can EAS call an executable that's running on another machine, that does not have sql server 2008/2005 installed?

  13. mark says:

    "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?

  14. Ryan says:

    Is it possible to configure multiple service broker enabled databases in the EAConfig.config file.

    Notification is only working for the first NotificationService in the list of NotificationServiceList.

    Here is my config:

     <NotificationServiceList>

       <NotificationService name="NotificationService" id="100" enabled="true">

         <Description>WAG QA</Description>

         <ConnectionString>

           <Unencrypted>server=WH-S8MP-1SQLT;database=IntegrationDB; Application Name=External Activator;Integrated Security=True</Unencrypted>

         </ConnectionString>

       </NotificationService>

       <NotificationService name="NotificationService" id="100" enabled="true">

         <Description>WAG DEV</Description>

         <ConnectionString>

           <Unencrypted>server=WH-S8MP-1SQLT;database=DevIntegrationDB; Application Name=External Activator;Integrated Security=True</Unencrypted>

         </ConnectionString>

       </NotificationService>

     </NotificationServiceList>

    Notification does not work on DevIntegrationDB, but it does on IntegrationDB.

  15. Ajit says:

    The External Activator Service is an extremely powerful addition to SQL Server, so big thanks to the Service Broker Team! After performing a lot of research on the subject of auditing data changes in user tables, using this component along with XML message processing in Service Broker turned out to be most scalable and reliable solution. For those interested in this subject, take a look at the article I wrote on my blog at ajitananthram.wordpress.com/…/auditing-external-activator

  16. Mike says:

    Hi, does anyone know what causes these messages to appear in the event log?

    "WARNING An event notification has been dropped"

    Thanks.

  17. leila says:

    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 ;(

  18. Hi10 says:

    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.

  19. JMac says:

    Is there a release of External Activator which is specific to Sql Server 2012

  20. Mik says:

    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.