E-Mail-Versand asynchron via Service Broker

Klassisches Szenario: Du hast eine Stored Procedure, die irgendwas macht (Bestellung anlegen, User registrieren, was auch immer) und am Ende soll eine Mail rausgehen. Statt sp_send_dbmail direkt aufzurufen (was den Aufrufer blockiert, bis SMTP geantwortet hat), legst du die Mail-Anforderung in eine Queue und ein Worker schickt sie im Hintergrund.

Setup einmalig

-- Demo-DB
CREATE DATABASE MailDemo;
GO
ALTER DATABASE MailDemo SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
USE MailDemo;
GO

-- Message Type: einfach XML mit Mail-Daten
CREATE MESSAGE TYPE [//demo/MailRequest]
    VALIDATION = WELL_FORMED_XML;

-- Contract: Initiator schickt MailRequest
CREATE CONTRACT [//demo/MailContract]
    ([//demo/MailRequest] SENT BY INITIATOR);

-- Target Queue (hier landen die Mail-Anforderungen)
CREATE QUEUE MailTargetQueue;

-- Target Service (Empfänger)
CREATE SERVICE [//demo/MailTarget]
    ON QUEUE MailTargetQueue
    ([//demo/MailContract]);

-- Initiator Queue (für Antworten/EndDialog-Nachrichten)
CREATE QUEUE MailInitQueue;

CREATE SERVICE [//demo/MailInit]
    ON QUEUE MailInitQueue;
GO

Helper-Procedure zum Senden

So ruft deine Business-Logik den Mail-Versand auf – schnell, transaktional, ohne SMTP-Wartezeit:

CREATE OR ALTER PROCEDURE dbo.QueueMail
    @To      NVARCHAR(200),
    @Subject NVARCHAR(400),
    @Body    NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @h UNIQUEIDENTIFIER;
    DECLARE @msg XML = (
        SELECT
            @To      AS [to],
            @Subject AS [subject],
            @Body    AS [body]
        FOR XML PATH('mail'), TYPE
    );

    BEGIN DIALOG CONVERSATION @h
        FROM SERVICE [//demo/MailInit]
        TO   SERVICE '//demo/MailTarget'
        ON   CONTRACT [//demo/MailContract]
        WITH ENCRYPTION = OFF;

    SEND ON CONVERSATION @h
        MESSAGE TYPE [//demo/MailRequest] (@msg);
END;
GO

Worker-Procedure (wird automatisch aktiviert)

Diese Procedure liest Nachrichten aus der Queue und ruft sp_send_dbmail auf:

CREATE OR ALTER PROCEDURE dbo.ProcessMailQueue
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @h        UNIQUEIDENTIFIER,
        @msgType  SYSNAME,
        @msgBody  XML,
        @to       NVARCHAR(200),
        @subject  NVARCHAR(400),
        @body     NVARCHAR(MAX);

    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION;

        WAITFOR (
            RECEIVE TOP(1)
                @h       = conversation_handle,
                @msgType = message_type_name,
                @msgBody = CAST(message_body AS XML)
            FROM MailTargetQueue
        ), TIMEOUT 5000;

        -- Queue leer -> raus
        IF @@ROWCOUNT = 0
        BEGIN
            ROLLBACK TRANSACTION;
            BREAK;
        END;

        IF @msgType = N'//demo/MailRequest'
        BEGIN
            SELECT
                @to      = @msgBody.value('(/mail/to)[1]',      'NVARCHAR(200)'),
                @subject = @msgBody.value('(/mail/subject)[1]', 'NVARCHAR(400)'),
                @body    = @msgBody.value('(/mail/body)[1]',    'NVARCHAR(MAX)');

            BEGIN TRY
                EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = 'DefaultMailProfile',  -- musst du anlegen
                    @recipients   = @to,
                    @subject      = @subject,
                    @body         = @body,
                    @body_format  = 'HTML';

                END CONVERSATION @h;
            END TRY
            BEGIN CATCH
                -- Logging, damit du Poison Messages siehst
                INSERT dbo.MailErrorLog (ConversationHandle, ErrorMsg, MsgBody)
                VALUES (@h, ERROR_MESSAGE(), CAST(@msgBody AS NVARCHAR(MAX)));

                END CONVERSATION @h WITH ERROR = 50001
                    DESCRIPTION = N'Mail send failed';
            END CATCH;
        END
        ELSE IF @msgType IN
            (N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog',
             N'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
        BEGIN
            END CONVERSATION @h;
        END;

        COMMIT TRANSACTION;
    END;
END;
GO

CREATE TABLE dbo.MailErrorLog (
    Id                 INT IDENTITY PRIMARY KEY,
    ConversationHandle UNIQUEIDENTIFIER,
    ErrorMsg           NVARCHAR(4000),
    MsgBody            NVARCHAR(MAX),
    CreatedAt          DATETIME2 DEFAULT SYSDATETIME()
);
GO

Activation einschalten – das ist der Clou

Jetzt sagst du der Queue: „Wenn Nachrichten kommen, starte automatisch bis zu 4 Worker parallel."

ALTER QUEUE MailTargetQueue
WITH ACTIVATION (
    STATUS         = ON,
    PROCEDURE_NAME = dbo.ProcessMailQueue,
    MAX_QUEUE_READERS = 4,
    EXECUTE AS SELF
);
GO

Ab hier brauchst du keinen Windows-Service, keinen SQL Agent Job, kein gar nichts. SQL Server überwacht die Queue selbst und feuert die Worker-Procedure an.

Verwendung in deiner eigentlichen Stored Procedure

CREATE OR ALTER PROCEDURE dbo.RegisterUser
    @UserName NVARCHAR(100),
    @Email    NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRAN;

    INSERT dbo.Users (UserName, Email)
    VALUES (@UserName, @Email);

    -- Mail in Queue legen – kehrt sofort zurück
    EXEC dbo.QueueMail
        @To      = @Email,
        @Subject = N'Willkommen!',
        @Body    = N'<h1>Hallo</h1><p>Dein Account ist bereit.</p>';

    COMMIT;  -- Insert UND Mail-Anforderung sind atomar
END;
GO

Was hier richtig elegant ist

Transaktionale Konsistenz. Wenn das INSERT rollback'ed, verschwindet auch die Mail-Anforderung aus der Queue. Du kannst nie den Fall haben „User wurde nicht angelegt, aber Begrüßungsmail ist trotzdem rausgegangen". Das ist mit einer externen Queue (RabbitMQ etc.) nur mit deutlich mehr Aufwand zu erreichen.

Caller-Performance. RegisterUser kommt nach ein paar Millisekunden zurück, egal ob der SMTP-Server gerade lahmt oder 30 Sekunden braucht.

Automatische Skalierung. MAX_QUEUE_READERS = 4 heißt: Bei viel Last laufen bis zu 4 Worker parallel, bei wenig Last wird automatisch runtergefahren.

Poison-Message-Schutz eingebaut. Wenn die Worker-Procedure 5x hintereinander rollback'ed, deaktiviert SQL Server die Queue automatisch (STATUS = OFF), damit nicht endlos retried wird.

Monitoring

-- Aktuelle Nachrichten in der Queue
SELECT * FROM MailTargetQueue WITH (NOLOCK);

-- Hängengebliebene Outbound-Messages
SELECT * FROM sys.transmission_queue;

-- Offene Conversations
SELECT * FROM sys.conversation_endpoints
WHERE state <> 'CD'  -- CD = Closed/Disconnected
ORDER BY security_timestamp DESC;

-- Queue-Status (ist Activation noch an?)
SELECT name, is_activation_enabled, activation_procedure
FROM sys.service_queues
WHERE name = 'MailTargetQueue';

Test

EXEC dbo.QueueMail
    @To      = N'georg@example.com',
    @Subject = N'Test',
    @Body    = N'<p>Service Broker rocks</p>';

-- Sollte fast sofort verarbeitet sein
WAITFOR DELAY '00:00:02';
SELECT COUNT(*) AS RemainingInQueue FROM MailTargetQueue WITH (NOLOCK);

Das gleiche Muster funktioniert für alles, was du asynchron machen willst: HTTP-Calls über sp_OACreate oder CLR, Dateioperationen, Cache-Invalidierungen, Logging in externe Systeme. Du tauschst nur den EXEC sp_send_dbmail-Block in der Worker-Procedure aus.

Wenn du willst, zeig ich dir als nächsten Schritt das Pattern mit mehreren Message Types pro Contract (z. B. Mail + SMS + Webhook auf derselben Queue) oder wie man scheduled/delayed messages baut – Service Broker hat dafür BEGIN CONVERSATION TIMER, was nett ist für Retry-Logik.