Overblog Folge diesem Blog
Edit post Administration Create my blog
Blog von Olaf Helper

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

Repost 0

Kommentiere diesen Post