Asynchrone T-SQL Prozeduren und Trigger

Der Service Broker bietet ab dem Microsoft SQL Server 2005 ein asynchrones Nachrichten-System. In diesem Artikel stellen wir auf der Basis des Message Brokers eine Methode vor, mit der sich beliebige T-SQL Prozeduren und Trigger asynchron ausführen lassen und so die die Geschwindigkeit von SQL Server Anwendungen maximiert.

Einleitung

Die Grundkomponenten des Service Brokers sind Dienste, Warteschlangen, Dialoge und Nachrichten:

Microsoft SQL Server Service Broker
Microsoft SQL Server Service Broker

Eine Nachricht wird innerhalb eines Dialoges über eine T-SQL Prozedur an den Initiatordienst (Sender) geschickt und in der Initiator-Warteschlange gespeichert. Der Initiator-Dienst schickt dann die Nachricht asynchron an den Zieldienst (Empfänger) der die Nachricht in der Ziel-Warteschlange speichert. Für jede Nachricht in der Ziel-Warteschlange wird eine weitere T-SQL Prozedur aufgerufen, die mit der Ziel-Warteschlange verknüpft ist.

Datenbank

Der Service Broker befindet sich innerhalb einer Datenbank. Für die Aktivierung des Service Brokers muss dieser eingeschaltet werden:

ALTER DATABASE [Name] SET ENABLE_BROKER

Die Aktivierung funktioniert nur, wenn keine offenen Bezüge mehr zu der Datenbank existieren. Darüber hinaus kann es zur Deaktivierung des Service Brokers durch das Hin- und Herspielen von Backups kommen, wie es typischer Weise während der Entwicklungsphase von Datenbankanwendungen kommt. Aus diesem Grund haben wir das Verfahren vereinfacht und in eine Prozedur ausgelagert:

EXEC spAsyncEnable 1

Dienste

Dienste stellen die Kommunikations-Endpunkte innerhalb des Service Brokers dar. Daher muss sowohl für den Initiator einer Nachricht, als auch für den Empfänger einer Nachricht ein Dienst existieren:

CREATE SERVICE [AsyncInitiatorService] AUTHORIZATION [dbo] → 
ON QUEUE [dbo].[AsyncInitiatorQueue]

CREATE SERVICE [AsyncTargetService] AUTHORIZATION [dbo] →
ON QUEUE [dbo].[AsyncTargetQueue] ([AsyncContract])

Die Dienste können sich in verschiedenen Datenbanken auf Verschiedenen SQL Servern befinden.

Warteschlangen

Jeder Dienst besitzt eine Warteschlange für Nachrichten. Die Initiator-Warteschlange muss Nachrichten nur entgegennehmen und weiterleiten:

CREATE QUEUE [dbo].[AsyncInitiatorQueue] WITH STATUS = ON, →
RETENTION = OFF ON [PRIMARY]

Die Ziel-Warteschlange leitet die Nachrichten an eine T-SQL Prozedur weiter:

CREATE QUEUE [dbo].[AsyncTargetQueue] WITH STATUS = ON, →
RETENTION = OFF ACTIVATION (STATUS = ON, → 
PROCEDURE_NAME = [dbo].[spAsyncReceive], →
MAX_QUEUE_READERS = 1, EXECUTE AS OWNER) ON [PRIMARY]

Der Name der Prozedur wird in PROCEDURE_NAME angegeben. Neben der Ausführungsberechtigung kann hier noch in MAX_QUEUE_READERS die maximale Anzahl von Instanzen der gespeicherten Aktivierungsprozedur an, die von der Warteschlange gleichzeitig gestartet werden. Der Wert 1 führt hier zum sequentiellen Abarbeiten der Warteschlange.

Nachrichten

Bevor Nachrichten transportiert werden können, müssen diese definiert werden:

CREATE MESSAGE TYPE [AsyncMessageType] AUTHORIZATION [dbo] →
VALIDATION = WELL_FORMED_XML

Mit dem Nachrichtentyp werden der Name der Nachricht und eine Validierung als XML definiert.

Verträge

Damit Nachrichtentypen in einer Konversation verwendet werden können, muss über einen Vertrag die Richtung und der Typ für eine Konversation festgelegt werden:

CREATE CONTRACT [AsyncContract] AUTHORIZATION → 
[dbo] ([AsyncMessageType] SENT BY INITIATOR )

Senden von Nachrichten

Das Senden von Nachrichten findet in der Form eines Dialoges statt, der am einfachsten in einer Prozedur gekapselt wird. In diesem Fall wird der auszuführende T-SQL Befehl als Zeichenfolge übergeben:

CREATE PROCEDURE [dbo].[spAsyncSend] 
      @cmd nvarchar(max) 
AS
BEGIN
     SET NOCOUNT ON

      -- Begin a transaction.
      BEGIN TRANSACTION

      -- Create the message.
      DECLARE @message XML
      SET @message = N'<command><![CDATA[' + @cmd + ']]></command>'
      
      -- Declare a variable to hold the conversation
     -- handle.
      DECLARE @dialog UNIQUEIDENTIFIER
      BEGIN DIALOG CONVERSATION @dialog
           FROM SERVICE AsyncInitiatorService
           TO SERVICE 'AsyncTargetService'
                ON CONTRACT AsyncContract
           WITH ENCRYPTION = OFF;

      -- Send the message on the dialog.
      SEND ON CONVERSATION @dialog
           MESSAGE TYPE AsyncMessageType
           (@message)

      -- Commit the transaction. Service Broker
     -- sends the message to the destination
     -- service only when the transaction commits.
      COMMIT TRANSACTION

      -- End conversation.
     END CONVERSATION @dialog
END

Nachdem ein Dialog mit dem betreffenden Initiator- und Zieldienst erstellt worden ist, wird das T-SQL Statement als XML verpackt gesendet und dann die Konversation beendet. Damit ist die Sende-Operation beendet.

Empfangen von Nachrichten

Nachrichten werden asynchron über die in der Ziel-Warteschlange angegebene T-SQL Prozedur verarbeitet:

CREATE PROCEDURE [dbo].[spAsyncReceive]
AS
BEGIN
      SET NOCOUNT ON;
       DECLARE      @message_body XML,
                   @message_type_name NVARCHAR(256),
                   @dialog UNIQUEIDENTIFIER,
                   @message_body_text NVARCHAR(MAX),
                   @command nvarchar(MAX),
                   @evtlogmsg nvarchar(MAX);

      -- This procedure continues to process messages in the
      -- queue until the queue is empty.
      WHILE (1 = 1)
      BEGIN

            -- Receive the next available message
            BEGIN TRANSACTION

                  -- just handle one message at a time
                  WAITFOR(RECEIVE TOP(1)

                        --the type of message received
                        @message_type_name = message_type_name,

                        -- the message contents
                        @message_body = message_body,

                        -- the identifier of the dialog this
                        @dialog = conversation_handle 
                             FROM syncTargetQueue),

                  -- if the queue is empty for two seconds,
              -- give up and go away
                  TIMEOUT 2000;

            -- If RECEIVE did not return a message,
            -- roll back the transaction and break out
            -- of the while loop, exiting the procedure.
                IF (@@ROWCOUNT = 0)
                  BEGIN
                  ROLLBACK TRANSACTION
                       END CONVERSATION @dialog ;
                       BREAK
                  END

            -- Check to see if the message is an end dialog message.
            IF (@message_type_name = 
             'http://schemas.microsoft.com/SQL/ServiceBroker/
              EndDialog')
                 BEGIN
                  END CONVERSATION @dialog;

                  -- Message processed
                  COMMIT TRANSACTION
                  BREAK
              END

         -- Check to see if the message can be processed.
           ELSE IF(@message_type_name = 'AsyncMessageType')
                 BEGIN

                 -- Excecute Stored Procedure
                 -- Message processed
                 COMMIT TRANSACTION

                 -- Run command
                 BEGIN TRY
                       SET @command = CAST(@message_body.query
                                                 ('/command/text()' 
                                       AS NVARCHAR(MAX))
                       EXEC sp_executesql @command
                 END TRY

                 -- Handle Error
                 BEGIN CATCH
                       SET @evtlogmsg = 'ERROR: ' + CAST(ISNULL
                                            (ERROR_NUMBER(), 0) 
                                            AS NVARCHAR(MAX) + CHAR(13) + 
                                           CHAR(10) + 'SEVERITY: ' + 
                                            CAST(ISNULL(ERROR_SEVERITY(), 0)
                                            AS NVARCHAR(MAX)) + CHAR(13) + 
                                            CHAR(10) + 'STATE: ' + 
                                            CAST(ISNULL(ERROR_STATE(), 0) 
                                           AS NVARCHAR(MAX)) + CHAR(13) + 
                                            CHAR(10) + 'PROCEDURE: ' +  
                                            ISNULL(ERROR_PROCEDURE(), '') + 
                                            CHAR(13) + CHAR(10) + 'LINE: ' + 
                                            CAST(ISNULL(ERROR_LINE(), 0) 
                                            AS NVARCHAR(MAX)) + CHAR(10) + 
                                  'LINE: ' + CAST(ISNULL
                                  (ERROR_LINE(), 0) 
                                            AS NVARCHAR(MAX))+ CHAR(13) + 
                                  CHAR(10) + ISNULL
                                  (ERROR_MESSAGE(), '')
                       INSERT INTO ztEvents (Event) VALUES(@evtlogmsg)

                  -- Remark:
                  -- For using the event log, the owner of the 
                  -- database must be member of the sysadmin role. 
                  -- This is the case for a user who is in the 
                  -- db_owner role of the master database 
                  -- (e.g. 'sa' who owns the master database)
                  -- EXEC xp_logevent 60000, @evtlogmsg, error
                 END CATCH
           END
     -- Unknown message type
     ELSE
           BEGIN
                 -- Let others process the message
                 ROLLBACK TRANSACTION
                 INSERT INTO ztEvents (Event)
                      VALUES(N'Message type ' + @message_type_name + 
                               ' <> AsyncMessageType for dialog #' + 
                               cast(ISNULL(@dialog,N'NULL') 
                               AS NVARCHAR(40)))
                 END CONVERSATION @dialog
             BREAK
           END
    END
END;

Bei jedem Aufruf der Prozedur werden alle Nachrichten der Warteschlange, die dem geforderten Nachrichtentyp entsprechen, in einer Konversation abgearbeitet. Im Fehlerfall wird eine Meldung in die Tabelle ztEvents geschrieben. Es ist ebenso möglich, eine Nachricht in das Windows Ereignisprotokoll zu schreiben, wenn die entsprechenden Berechtigungen für die Datenbank existieren.

Verwendung

Die hier vorgestellt Nutzung des Service Brokers kann z.B. innerhalb von Triggern verwendet werden, die eine aufwendige Berechnung anstoßen. Dies können z.B. Berechnungen zur Datenkonsolidierung, der Versand von E-Mails oder der Zugriff auf Dateien sein.

Es sollte jedoch klar sein, das die Verwendung von asynchronen Prozeduren und Triggern Transaktionsgrenzen überschreitet und nicht innerhalb einer Transaktion verwendet werden darf.

Zusammenfassung

Das hier vorgestellte Verfahren erlaubt die asynchrone Ausführung von T-SQL Prozeduren über den Message Broker.  Damit ist es möglich zeitintensive Prozeduren von dem eigentlichen Programmablauf zu entkoppeln und so ein schnelles Antwortverhalten von T-SQL Prozeduren zu erreichen. Bei unserer Methode handelt es sich um einen generischen Ansatz handelt, der beliebigen T-SQL Code verarbeitet und so die Funktionalität des Service Brokers geschickt kapselt.

Weitere Informationen