Asynchronous Triggers in SQL Server 2005/2008

Question

I have triggers that manipulate and insert a lot of data into a Change tracking table for audit purposes on every insert, update and delete.

This trigger does its job very well, by using it we are able to log the desired oldvalues/newvalues as per the business requirements for every transaction.

However in some cases where the source table has a lot columns, it can take up to 30 seconds for the transaction to complete which is unacceptable.

Is there a way to make the trigger run asynchronously? Any examples.

Answer 1

these articles show how to use service broker for async auditing and should be useful:

Centralized Asynchronous Auditing with Service Broker

Service Broker goodies: Cross Server Many to One (One to Many) scenario and How to troubleshoot it

Answer 2

From sql server 2008 you can use CDC feature for automatically logging changes, which is purely asynchronous. Find more details in here

Answer 3

To perform asynchronous processing you can use Service Broker, but it isn't the only option, you can also use CLR objects.

The following is an example of an stored procedure (AsyncProcedure) that asynchronous calls another procedure (SyncProcedure):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.Remoting.Messaging;
using System.Diagnostics;

public delegate void AsyncMethodCaller(string data, string server, string dbName);

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void AsyncProcedure(SqlXml data)
    {
        AsyncMethodCaller methodCaller = new AsyncMethodCaller(ExecuteAsync);
        string server = null;
        string dbName = null;
        using (SqlConnection cn = new SqlConnection("context connection=true"))
        using (SqlCommand cmd = new SqlCommand("SELECT @@SERVERNAME AS [Server], DB_NAME() AS DbName", cn))
        {
            cn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                reader.Read();
                server = reader.GetString(0);
                dbName = reader.GetString(1);
            }
        }
        methodCaller.BeginInvoke(data.Value, server, dbName, new AsyncCallback(Callback), null);
        //methodCaller.BeginInvoke(data.Value, server, dbName, null, null);
    }

    private static void ExecuteAsync(string data, string server, string dbName)
    {
        string connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, dbName);
        using (SqlConnection cn = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand("SyncProcedure", cn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@data", SqlDbType.Xml).Value = data;
            cn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    private static void Callback(IAsyncResult ar)
    {
        AsyncResult result = (AsyncResult)ar;
        AsyncMethodCaller caller = (AsyncMethodCaller)result.AsyncDelegate;
        try
        {
            caller.EndInvoke(ar);
        }
        catch (Exception ex)
        {
            // handle the exception
            //Debug.WriteLine(ex.ToString());
        }
    }
}

It uses asynchronous delegates to call SyncProcedure:

CREATE PROCEDURE SyncProcedure(@data xml)
AS
  INSERT INTO T(Data) VALUES (@data)

Example of calling AsyncProcedure:

EXEC dbo.AsyncProcedure N'<doc><id>1</id></doc>'

Unfortunatelly, the assembly requires UNSAFE permission.

Answer 4

You can't make the trigger run asynchronously, but you could have the trigger synchronously send a message to a SQL Service Broker queue. The queue can then be processed asynchronously by a stored procedure.