SQL Server 2005/2008中的异步触发器

您不能使触发器异步运行,但可以让触发器同步向 SQL Service Broker 排队。然后,存储过程可以异步处理队列。

这些文章展示了如何使用ServiceBroker进行异步审计,应该很有用:

Centralized Asynchronous Auditing with Service Broker

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

SQL Server 2014引入了一个非常有趣的特性,叫做 Delayed Durability .如果您能够容忍在发生灾难性事件(如服务器崩溃)时丢失几行,那么您就可以真正提高在Schenarios中的性能。

延迟的事务持久性是使用异步日志完成的。 写入磁盘。事务日志记录保存在缓冲区中,并且 当缓冲区填充或缓冲区刷新事件发生时写入磁盘 地点。延迟事务持久性降低了延迟和 系统内的争用

必须首先修改包含表的数据库,以允许延迟的持久性。

ALTER DATABASE dbname SET DELAYED_DURABILITY = ALLOWED

然后,您可以在每个事务的基础上控制持久性。

begin tran

insert into ChangeTrackingTable select * from inserted

commit with(DELAYED_DURABILITY=ON)

如果事务是跨数据库的,那么事务将被提交为持久的,因此只有当审计表与触发器位于同一数据库中时,这才有效。

也有可能将数据库更改为强制的,而不是允许的。这会导致数据库中的所有事务都变得延迟持久。

ALTER DATABASE dbname SET DELAYED_DURABILITY = FORCED

对于延迟的耐久性,意外的 关闭和预期的SQL Server关闭/重新启动。喜欢 灾难性事件,您应该计划数据丢失。有计划地 关闭/重新启动一些尚未写入磁盘的事务 可以先保存到磁盘,但不应计划使用它。计划作为 尽管关闭/重新启动(无论是计划内还是计划外)会丢失 数据与灾难性事件相同。

这种奇怪的缺陷有望在未来的版本中得到解决,但在此之前,最好确保在SQL Server重新启动或关闭时自动执行“sp_flush_log”过程。

我想知道您是否可以通过插入“太过过程”表(包括谁做了更改等)来标记更改跟踪记录。

然后,另一个过程可能会出现,并定期复制其余的数据。

显然,“工作做得很好”和“不可接受”之间存在着根本的冲突。

在我看来,您试图使用触发器的方式与在OO过程应用程序中使用事件的方式相同,imho无法映射这些事件。

我会称任何需要30秒的触发逻辑——不,超过0.1秒——都是不正常的。我认为你真的需要重新设计你的功能,然后用其他的方法来做。我会说“如果你想让它异步”,但我认为这种设计在任何形式上都没有意义。

至于“异步触发器”,最基本的冲突是在begin tran和commit tran语句之间永远不能包含这样的内容,因为您已经不知道它是否成功了。

创建历史记录表。在更新(/deleting/inserting)主表时,将记录的旧值(触发器中已删除的伪表)插入历史表;还需要一些附加信息(时间戳、操作类型,可能是用户上下文)。新值无论如何都保存在活动表中。

通过这种方式,触发器可以快速运行(ER),您可以将慢速操作切换到日志查看器(过程)。

要执行异步处理,可以使用ServiceBroker,但它不是唯一的选项,也可以使用CLR对象。

以下是异步调用另一个过程(SyncProcedure)的存储过程(AsyncProcedure)的示例:

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());
        }
    }
}

它使用异步委托调用SyncProcedure:

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

调用AsyncProcedure的示例:

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

不幸的是,程序集需要不安全的权限。

我不知道,但您是否在审计表中插入了一些值,这些值也存在于基表中?如果是这样,您可以考虑只跟踪更改。因此,insert将跟踪更改时间、用户、额外值和一组空值(实际上是before值)。更新只包含更改时间、用户等和更改列的before值。删除会更改at等和所有值。

另外,每个基表有一个审计表还是数据库有一个审计表?当然,当每个事务试图写入一个表时,后者更容易导致等待。

我怀疑您的触发器是由这些通用的csv/text生成触发器组成的,它们被设计成在一个地方记录所有表的所有更改。理论上很好(可能是…),但在实践中很难维护和使用。

如果您可以异步运行(这仍然需要将数据存储在某个地方以便稍后再次记录),那么您就没有进行审计,也没有要使用的历史记录。

也许您可以查看触发器执行计划,看看哪个位占用的时间最长?

你能把你的审计方式,比如说,改为按表审计吗?您可以将当前日志数据拆分到相关表中。