Share via

Working with SQL Service Broker

VDT-7677 121 Reputation points
2021-02-08T12:27:44.677+00:00

Hi,

Running SQL Server 2017. I have a requirement to send an email based on certain row conditions in a given table. The email is dynamic, and based on values contained in the row itself. My initial thought was to use an after update trigger, encapsulating the logic to build the email and send it inside the trigger.

However, my research has led me to looking at using Service Broker instead. I have read Adam Machanic's workbench series on Service Broker, but am still at a bit of a loss. In essence, I need to know what PKs in the after update trigger meet my condition, so I can build/send an email for each PK. But I'm not sure of how to tackle this from a Service Broker standpoint.

I could, at the trigger level, insert the PKs into a staging table and then run a job at intervals to loop through the staging table and do the rest of the processing in the SP called from the job but I would like these emails to be sent as soon as possible after the trigger runs, not necessarily at set intervals. How could I use a Service Broker message to pass the PK to the activation procedure? Or, if I'm way off how would I use Service Broker to accomplish what I want to do?

Any suggestions are greatly appreciated!

Best Regards
B

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,060 questions
0 comments
Accepted answer
  1. Erland Sommarskog 103.2K Reputation points MVP
    2021-02-08T23:08:33.473+00:00

    Service Broker is certainly an option. The one thing that would hold you back is that there is a hurdle to get started, and if you only use Service Broker in a corner of your application, the cost for maintaining that corner would be high. Then again, Service Broker is quite powerful, and you may find other uses for it in your application, and it is used on a wider basis, knowledge in the team is likely to be higher.

    In this the example you would do something like: 

       SET @data = (SELECT pkcol FROM inserted FOR XML AUTO)

    and then send @alrt in the SEND statement. The activation procedure would shred the XML to get the PK values.

    You don't have to use XML - you could sent a comma-separated list. Or, since you ultimately are sending a binary value, you create a varbinary with fixed sizes for the ids.. (If they are integer ids.)

    However, the normal thing to do is to send XML documents.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. VDT-7677 121 Reputation points
    2021-02-09T00:53:39.56+00:00

    Hi @Erland Sommarskog ,

    Thanks for your response. My approach currently uses an after update trigger to insert rows into a staging table, and then sends a message to the target service which executes an SP that loops through the staging table and performs whatever processing is required.

    I used the following statements to create the SB configuration:

    ALTER DATABASE [SSBTest] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
    GO
    CREATE MESSAGE TYPE [SSB_Message] VALIDATION = EMPTY
    GO
    CREATE CONTRACT [SSB_Contract] ([SSB_Message]) SENT BY INITIATOR)
    GO
    CREATE QUEUE [SSB_Queue_Init]
    GO
    CREATE QUEUE [SSB_Queue_Target] WITH ACTIVATION (STATUS = ON, PROCEDURE_NAME = [dbo].[Process_Messages], MAX_QUEUE_READERS = 1)
    GO
    CREATE SERVICE [SSB_Service_Init] ON QUEUE [SSB_Queue_Init] ([SSB_Contract])
    GO
    CREATE SERVICE [SSB_Service_Target] ON QUEUE [SSB_Queue_Target] ([SSB_Contract])
    GO

    The initiator and target are on the same server. After update trigger uses EXECUTE AS CALLER, as does SP being invoked from target service. The trigger inserts data into the staging table correctly, and syntax for SB dialog is as follows:

    BEGIN DIALOG CONVERSATION @George Wang FROM SERVICE [SSB_Service_Init] TO SERVICE 'SSB_Service_Target' ON CONTRACT [SSB_Contract] WITH ENCRYPTION = OFF
    ;
    SEND ON CONVERSATION @George Wang MESSAGE TYPE [SSB_Message]
    ;
    END CONVERSATION @George Wang
    ;

    However, when I check my logs I get 'The activated proc '[dbo].[Process_Messages]' running on queue 'SSBTest.dbo.SSB_Queue_Target' output the following: 'Access to the remote server is denied because the current security context is not trusted.'

    I've tried restarting (disable/enable) the target queue but just wind up with an additional entry (same error message) in the log each time.

    Do you have any suggestions to diagnose/fix?

    Thanks!
    B

  2. Erland Sommarskog 103.2K Reputation points MVP
    2021-02-09T22:29:44.12+00:00

    Sorry for the delay, but I only check out this forum about an hour in the evening my time.

    There are several issues here. Let's start with RECEIVE TOP (1). Yes, TOP(1) is the normal thing - you take one message at a time.

    As for the pattern, that looks like fire-and-forget and that is not the way to do it. I don't know how often data is written to that table, but if insertions are frequent, it may be worthwhile to reuse conversations.
    Anyway, if you are working with Service Broker, you should bookmark Remus Rusanu's blog. He was one of the original developers of Service Broker and his blog is a great resource for Service Broker. Here is a link to his fire-and-forget post: https://rusanu.com/2006/04/06/fire-and-forget-good-for-the-military-but-not-for-service-broker-conversations/ He also have post on how to reuse and recycle connections.

    Let's then move over the permissions issue which you resolved by setting the database Trustworthy. You should be aware of that setting a database Trustworthy can be a security risk. If there is a person in the database who is in the db_owner role (or more precisely can create and impersonate users), that person can elevate to be sysadmin if the database is trustworthy.

    The alternative is to use certificate signing. This is a general technique which not related to Service Broker as such. The idea is that you can sign a procedure with a certificate. Then you create a login or a user from that certificate, and then you grant that login or user permissions. These permissions are then packaged with the procedure, so that the procedure can perform actions although the calling user do not have these permissions.

    In your case, this is required to break out of the sandbox which is set up when a database user is impersonated, which is the case with an activation procedure. To break out of the sandbox, the procedure must have EXECUTE AS OWNER, and be signed with a certificate that has AUTHENTICATE SERVER. Or AUTHENTICATE in the database you want to go to, which is msdb if you want to send mail. (But the error message suggests that you are trying to access a linked server.)

    I have written about this in quite some detail. On my web site, I have a longer article, [Packaging Permissions in Stored Procedures] (http://www.sommarskog.se/grantperm.html). That article does not discuss Service Broker, though, but you will need to continue to the appendix where I have a chapter on Service Broker. (And in the appendix, I don't discuss sending mail, neither through Service Broker or directly, but the appendix has some other chapters that may be helpful.)

    So, yes, there is quite some material to get through, and I will have to apologise that I did not warn you about the permissions last night. It did not occur to me, but I am certainly well aware of it.

    0 comments
  3. VDT-7677 121 Reputation points
    2021-02-11T03:56:33.683+00:00

    Hi Erland,

    Thanks for your assistance. I did read Remus' blog and it was very informative. I have things set up and working correctly, however one question I have remaining is (assuming there was an error in message processing and/or activation SP) how can I log the actual error content? I have an IF block that checks the message type name, and if it's Error I use RAISERROR WITH LOG. However how can I capture the actual error encountered so it can be included in the server log?

    Thanks!