Asynchronous Transactions

Business Case: An organization frequently creates databases ad-hoc and wants them to be backed-up immediately upon creation. This reduces risk by automating what would otherwise be another step for the database creator to remember to perform. Additionally, the existing maintenance actions include a full backup daily and a log backup every fifteen minutes. The challenge is that a log backup will fail if a full backup has not already been performed on the database. Given the frequency and ad-hoc nature of database creation, it is important to ensure a full backup is taken as soon as a database is created.

Environment: On the surface, this business case seems like a good match for a server-level trigger on the CREATE_DATABASE statement, with the trigger action performing the backup. However, that will not work because a backup operation is not permitted within a transaction containing other statements; triggers are executed as part of the calling statement (because the calling transaction must be rolled-back if the trigger action fails). Thus, we will need to be able to perform a second action as reliably as if it were part of a database transaction, but it cannot actually exist within the same literal transaction.

Approach: To perform this sort of asynchronous transaction, we will use event notification and the service broker to perform a full backup whenever a database is created.

Solution: Let us start by creating a stored procedure that will read a yet-to-be-defined service broker queue whenever a message is placed in it. The procedure will consume the message, perform the database backup, and then end the service broker conversation.

You’ll notice that this stored procedure makes use of a procedure we created previously to perform our backup operations. It is available here.

Also note that this yet-to-be-defined queue will be created in the msdb database. We are creating it there because it roughly aligns with part of what msdb is intended to do (manage the SQL agent); also, because msdb is a system database, it is conceptually independent from the user databases for which this solution is intended to manage initial backup creation.

-- Create a procedure to consume service broker queued messages
CREATE PROCEDURE [dbo].[uspBrokerBackup]
AS
BEGIN
    -- Initialize variables for later use
    DECLARE
        @uiHandle UNIQUEIDENTIFIER
        , @strMsg NVARCHAR(MAX)
        , @sysMsgName SYSNAME
        , @sysDBName SYSNAME
    ;
    -- Consume a message from the queue, waiting no longer than one second
    WAITFOR (
        RECEIVE TOP(1)
            @uiHandle = [conversation_handle]
            , @strMsg = message_body
            , @sysMsgName = message_type_name
        FROM msdb.dbo.queBackupDB)
        , TIMEOUT 1000 -- in milliseconds
    ;
    -- Validate message type and contents
    IF @sysMsgName = N'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
    AND CHARINDEX(N'<DatabaseName>', @strMsg) != 0
    AND CHARINDEX(N'</DatabaseName>', @strMsg) != 0
    BEGIN
        -- Extract the database name from the message body
        SET @sysDBName = SUBSTRING(
            @strMsg
            , CHARINDEX(N'<DatabaseName>', @strMsg)
                + LEN(N'<DatabaseName>')
            , CHARINDEX(N'</DatabaseName>', @strMsg)
                - CHARINDEX(N'<DatabaseName>', @strMsg)
                - LEN(N'<DatabaseName>'))
        ;
        -- Perform the full backup; click here for uspMxBackup
        EXECUTE master.dbo.uspMxBackup
            @strBackupType = 'FULL'
            , @sysServerName = NULL
            , @sysDatabaseName = @sysDBName
        ;
        -- End the service broker conversation
        END CONVERSATION @uiHandle;
    END
END

Now let’s setup the service broker. We only need to create a couple of the service broker objects; we’ll use some system objects for the rest. We’ll need a queue to store and process notifications of database creation, a service to receive notifications and put them in the queue, and an event notification to catch the create database actions and send them to the service. Don’t worry: it only sounds easy because it is. 🙂

-- If the service already exists, drop it
IF EXISTS (
    SELECT *
    FROM sys.services
    WHERE name = N'//SQLEXPRESS/InternalAction/BackupDB')
DROP SERVICE [//SQLEXPRESS/InternalAction/BackupDB]
;

-- If the queue already exists, drop it
IF EXISTS (
    SELECT *
    FROM sys.service_queues
    WHERE name = N'queBackupDB')
DROP QUEUE [queBackupDB]
;

-- Create the queue to store and process the notifications
CREATE QUEUE [queBackupDB]
WITH
    ACTIVATION (
        PROCEDURE_NAME = [master].[dbo].[uspBrokerBackup]
        , MAX_QUEUE_READERS = 1
        , EXECUTE AS N'dbo')
;

-- Create the service to send notifications to the queue
CREATE SERVICE [//SQLEXPRESS/InternalAction/BackupDB]
ON QUEUE queBackupDB ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
;

-- If the event notification already exists, drop it
IF EXISTS (
    SELECT *
    FROM sys.server_event_notifications
    WHERE name = N'evntCreateDatabase')
DROP EVENT NOTIFICATION evntCreateDatabase
ON SERVER
;

-- Get the msdb service broker id
DECLARE @uiBrokerID UNIQUEIDENTIFIER = (
    SELECT service_broker_guid
    FROM sys.databases
    WHERE name = N'msdb')
;

-- Build the SQL to create the event notification
DECLARE @strSQL NVARCHAR(MAX) =
N'CREATE EVENT NOTIFICATION evntCreateDatabase
ON SERVER
FOR CREATE_DATABASE
TO SERVICE
    ''//SQLEXPRESS/InternalAction/BackupDB'' -- name of service broker service
    , ''' + CONVERT(CHAR(36), @uiBrokerID) + N''' -- service_broker_guid for msdb db
;'
;

-- Create the event notification
EXECUTE sp_executesql @strSQL;

And, there you have it. When the create database event occurs, a notification message is sent via a service to a queue, which then uses a stored procedure to process each notification message. If useful in your environment, feel free to add defensive coding, such as ensuring notification messages are processed successfully, retrying if they are not, notifying an operator if any piece fails, etc. Enjoy!

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: