Friday, March 19, 2010

Asynchronous triggers using SQL Broker

SQL triggers exists to answer a very common need in applications to allow us to react when all sort of database events occur.
As you probably know, the basic triggers are insert ,update & delete triggers, that potentially allow us to react to every inserted/deleted/updated row.
One of the main problems with triggers starts when we attach to those events heavy processing.

One way to reduce the time the user waits for the related transaction to end is to change the behavior of those triggers to be asynchronous.

The main idea is to build an xml in the trigger and pass it to an sqlBroker queue for later processing, well..it doesn't have to be 'later', but it is async so it releases the main thread to continue & return to the client.

Let's look at the code, first we'll start with the declaration stuff:

--Enable broker

SET @strsql = 'ALTER DATABASE ' + db_name() + ' SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE '
EXEC sp_executesql @strsql

-- Create Message Type 
CREATE MESSAGE TYPE SampleMessage
AUTHORIZATION dbo 
VALIDATION = WELL_FORMED_XML; 

-- Create Contract 
CREATE CONTRACT SampleContract
AUTHORIZATION dbo
(SampleMessage SENT BY INITIATOR); 

-- Create Queue 
CREATE QUEUE dbo.SampleQueue WITH STATUS=ON, ACTIVATION 
(STATUS = ON, MAX_QUEUE_READERS = 1, 
PROCEDURE_NAME = spMessageProcSample,   EXECUTE AS OWNER); 


-- Create init Service 
CREATE SERVICE SampleServiceInitiator
AUTHORIZATION dbo
ON QUEUE dbo.SampleQueue (SampleContract); 

-- Create target Service 
CREATE SERVICE [SampleServiceTarget]
AUTHORIZATION dbo 
ON QUEUE dbo.SampleQueue (SampleContract); 




The trigger will use the SampleServiceInitiator to send an xml message (SampleMessage message type), this message will reach the SampleQueue queue which will be retrieved by SampleServiceTarget, this service will run the stored procedure named spMessageProcSample to handle the message.

Let's see a sample trigger code:

CREATE TRIGGER [trigSample] ON [dbo].SampleTable FOR UPDATE As 
SET NOCOUNT ON; 

DECLARE @MessageBody XML
DECLARE @TableId int

--get relevant information from inserted/deleted and convert to xml message
SET @MessageBody = (SELECT col1, col2 FROM inserted
FOR XML AUTO)


If (@MessageBody IS NOT NULL)
BEGIN

DECLARE @Handle UNIQUEIDENTIFIER; 
BEGIN DIALOG CONVERSATION @Handle 
FROM SERVICE [SampleServiceInitiator] 
TO SERVICE 'SampleServiceTarget' 
ON CONTRACT [SampleContract] 
WITH ENCRYPTION = OFF; 

SEND ON CONVERSATION @Handle 


MESSAGE TYPE [SampleMessageType](@MessageBody); 

END


Sample code of the reciever sp:

CREATE PROCEDURE [dbo].[spMessageProcSample] 
AS 
BEGIN 

DECLARE @message_type varchar(100) 
DECLARE @dialog uniqueidentifier, @message_body XML; 
WHILE (1 = 1) 
BEGIN -- Receive the next available message from the queue 
WAITFOR ( RECEIVE TOP(1) @message_type = message_type_name,     @message_body = CAST(message_body AS XML),     @dialog = conversation_handle
FROM dbo.SampleQueue ), TIMEOUT 500    if (@@ROWCOUNT = 0 OR @message_body IS NULL) 
BEGIN 
BREAK 
END 
ELSE 
BEGIN 
--process xml message here...

select ref.value('@SampleColumn[1]', 'int'),
from @message_body.nodes('//SampleMessage') T(ref)

END
END CONVERSATION @dialog 
END

END


That's it...some new objects to know and a bit weird syntax..but it does the work :-)

have fun

Diego

12 comments:

  1. Added "AUTHORIZATION dbo" to messageType, contract & services creation script.
    Read: SqlBroker cleanup for more details.

    ReplyDelete
  2. I have tried this but the stored procedure is running twice.

    ReplyDelete
    Replies
    1. The procedure runs for every trigger call. Can you be more specific on what seem to be the problem?

      Delete
  3. Can I write the following code in the procedure and execute it in the procedure to execute a SSIS Package from it?:

    SET @MessageBody = 'EXEC xp_cmdshell ''dtexec /FILE "C:\Documents and Settings\Administrator\Desktop\Test\Test\Test1.dtsx" /decrypt saa'' ) '

    Or does this only work for Inserts/Updates into another table??

    ReplyDelete
  4. Sorry....I meant if I write the above code in the Trigger an Execute it in the Procedure.

    ReplyDelete
    Replies
    1. I am not sure what you are trying to accomplish by this design, but theoretically you can run any tsql in the 'spMessageProcSample' procedure. In your case it means that you would run this SSIS package for every trigger call - as I said - I don't understand what you are trying to accomplish but it sounds like a risky idea, in addition - allowing xp_cmdshell is a known security risk.

      Delete
  5. Hey, everything seems to have been created/configured correctly, but when my trigger fires, nothing goes into the queue, and the target stored proc is never called. I followed your example directly, and the service broker is enabled. Any thoughts on what I might be missing? Thanks!

    ReplyDelete
  6. if you implemented the spMessageProcSample it gets the messages from the queue..add some insert command in "--process xml message here..." to see if it works or not.

    ReplyDelete
  7. Hi guys im trying exactly as posted here, i see the message in the SampleQueue but the store procedure never gets called or executed, i placed a print on top of the SP to check it's getting hit but nothing gets printed, no errors, im lost, can you please help me out?

    ReplyDelete
  8. you execute the stored procedure and then it reads the queue, right? I don't see anything that automatically runs the stored procedure.

    ReplyDelete
  9. See the queue creation statement

    ReplyDelete