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:
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_BROKERDie 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 1Dienste
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_XMLMit 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
ENDNachdem 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.