Service Broker: Asynchrone Ausführung von Stored Procedures

23. Dezember 2008 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

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

Diesen Post teilen

Repost0

Parameter für „Benutzerdefinierte Berichte“

MS weist auf Sicherheitslücke im SQL Server hin (KB961040)

Zurück zur Home

Kommentiere diesen Post

2016
- April 1
- März 2
2013
- August 1
- Juni 2
2012
- November 1
- Juni 1
- April 2
- März 2
- Januar 1
2011
- Dezember 1
- November 2
- Oktober 1
- August 4
- Juli 1
- Mai 1
- April 5
- März 4
- Februar 5
2010
- Mai 1
- April 11
- März 5
- Februar 2
- Januar 14
2009
- Dezember 3
- November 10
- Oktober 11
- September 9
- August 15
- Juli 11
- Juni 6
- Mai 8
- April 9
- März 12
- Februar 19
- Januar 26
2008
- Dezember 27