Mai 9

Written by:
09.05.2009 07:21 

Normalerweise läuft in einer DBMS alles immer synchron in einer Transaktion ab; so ist es gedacht.

In Ausnahmefällen kommt es aber auch mal vor, dass man eine SP anstarten will, die sehr lange läuft und man nicht unbedingt warten will, bis sie fertig ist.

Zeitgesteuert gibt es mehrere Möglichkeiten wie ein SQL Server Agent Job, der regelmäßig nachsieht, ob es etwas zu tun gibt oder mit dem Windows Taskplaner + SqlCmd.

Möchte man es aber asynchron direkt anstarten, stellt der Service Broker ein gut Möglichkeit dar.

Man legt eine Queue an, die bei Eintreffen einer Nachricht eine Stored Procedure startet, die dann die ToDo’s abarbeitet. Mit Beendigung könnte die SP dann eine Nachricht über Beendigung & Ergebnis in weitere Queue senden, um den Aufrufer zu benachrichtigen.

Hier mal ein kleines Bespiel, dazu wird die Demo-Datenbank „AdventureWorks“ verwendet, natürlich ginge es auch mit anderen DBs.

Zunächst erst mal die Vorbereitung, Service Broker aktivieren

 

USE master;

GO

-- Service Broker aktivieren

ALTER DATABASE AdventureWorks SET ENABLE_BROKER;

GO

USE AdventureWorks

GO

IF NOT OBJECT_ID('AsyncWorkerHist', N'U') IS NULL

    DROP TABLE AsyncWorkerHist

GO

-- Tabelle mit der Hist der erledigten Aufträge

CREATE TABLE AsyncWorkerHist

    (LogDate datetime,

     LogAction varchar(50),

     LogMessage varchar(50),

     LogBody varchar(50));

GO

-- Nachrichtentyp mit Uri-Adresse anlegen

-- WELL_FORMED_XML, auch wenn vorerst der Inhalt der

-- Nachrichten egal ist

CREATE MESSAGE TYPE [//AWDB/AsyncWork/Message]

    VALIDATION = WELL_FORMED_XML;  

GO

-- Vertrag festlegen

CREATE CONTRACT [//AWDB/AsyncWork/myContract]

      ([//AWDB/AsyncWork/Message]

       SENT BY ANY);

GO

-- Queue für Initiator anlegen

-- wird benötigt, aber nicht verwendet

CREATE QUEUE InitAsyncWorkerQueue;

GO

CREATE SERVICE [//AWDB/AsyncWork/InitService]

    ON QUEUE InitAsyncWorkerQueue

       ([//AWDB/AsyncWork/myContract]);

GO

 

Nun kommt das wichtigste, nämlich die Stored Procedure, die aktiviert und die Nachrichten abarbeiten soll; da sie für die Queue-Definition bereits vorhanden sein muss, kommt sie hier an der Stelle. Sie holt mittels des Befehls „RECEIVE“ eine Nachricht aus der Queue ab. Handelt es sich um eine bekannte Nachricht, wird die entsprechende Aktion ausgeführt.

Natürlich ist das hier nur ein Mini-Beispiel, man sollte alles in eine Transaction einbetten, Error-Handling fehlt, etc.

 

IF NOT OBJECT_ID('spQueueWorkerTest', N'P') IS NULL

    DROP PROCEDURE spQueueWorkerTest

GO

-- SP fürs abarbeiten der Warteschlange erstellen

CREATE PROCEDURE spQueueWorkerTest

AS

BEGIN

    DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;

    DECLARE @RecvReqMsg NVARCHAR(100);

    DECLARE @RecvReqMsgName sysname;

    -- Erste Nachricht abholen und je Lauf

    -- auch nur eine abarbeiten (alternative WHILE Schleife)

    RECEIVE TOP(1)

            @RecvReqDlgHandle = conversation_handle,

            @RecvReqMsg = message_body,

            @RecvReqMsgName = message_type_name

    FROM AsyncWorkerQueue

    --Nachricht auch für mich gedacht?

    IF @RecvReqMsgName = N'//AWDB/AsyncWork/Message'

    BEGIN

        -- Aktivität protokollieren

        INSERT INTO AsyncWorkerHist

            (LogDate, LogAction, LogMessage, LogBody)

        VALUES (GetDate(), 'Started', @RecvReqMsgName, @RecvReqMsg);

        -- 2 min so tun, als sein man mächtig beschäftigt

        WAITFOR DELAY '00:02'; 

        -- Aktivität protokollieren

        INSERT INTO AsyncWorkerHist

            (LogDate, LogAction, LogMessage, LogBody)

        VALUES (GetDate(), 'Finished', @RecvReqMsgName, @RecvReqMsg);

        -- Jetzt könnte man eine Nachricht zurück senden

     END

END;

GO

 

Zum Schluss der Definition noch die Queue und der Service:

 

-- Queue anlegen

CREATE QUEUE AsyncWorkerQueue

    WITH STATUS = ON,

         RETENTION = OFF,

         ACTIVATION(STATUS = ON,

                    PROCEDURE_NAME = spQueueWorkerTest,

                    MAX_QUEUE_READERS = 1,

                    EXECUTE AS Owner);

GO

-- und den Dienst dazu

CREATE SERVICE [//AWDB/AsyncWork/TargetService]

    ON QUEUE AsyncWorkerQueue

       ([//AWDB/AsyncWork/myContract]);

GO

 

Nun muss man nur noch eine Nachricht in die Queue stellen und abwarten, was passiert:

 

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;

DECLARE @RequestMsg NVARCHAR(100);

-- Nachrichtentext

SELECT @RequestMsg =

       N'<RequestMsg>StartAsyncJob</RequestMsg>';

-- Dialog beginnen

BEGIN DIALOG @InitDlgHandle

     FROM SERVICE [//AWDB/AsyncWork/InitService]

     TO SERVICE  N'//AWDB/AsyncWork/TargetService'

     ON CONTRACT  [//AWDB/AsyncWork/myContract]

     WITH ENCRYPTION = OFF;

-- Senden

SEND ON CONVERSATION @InitDlgHandle

     MESSAGE TYPE [//AWDB/AsyncWork/Message]

     (@RequestMsg);

-- Und beenden

END CONVERSATION @InitDlgHandle

GO

-- Gestartet/Fertig?

SELECT *

FROM AsyncWorkerHist

ORDER BY LogDate