SQL Server存储过程与触发器实战：构建高可用数据审计系统
|
一个月之前，我接到个棘手需求——某金融企业要给核心业务系统加数据审计功能，要求零停机部署且审计日志不能丢。传统方案要么用CDC（变更数据捕获）要额外买许可，要么靠应用层打日志影响性能，这时候团队里有人提议：用SQL Server存储过程+触发器自己造轮子。说实话，我第一反应是“这老古董能行吗？”但实测下来，这套组合拳在成本、可用性上真把新技术按在地上摩擦。 先说触发器怎么扛住高并发。我们给订单表的INSERT/UPDATE/DELETE操作绑了AFTER触发器，核心逻辑就三步：用TRY-CATCH包住审计日志插入，失败时把错误码和原始SQL存到备用表，每5分钟用存储过程扫描备用表重试。测试时模拟了每秒2000笔订单的峰值，触发器延迟始终控制在50ms内——这比某些商业审计工具的100ms阈值还低。关键细节是触发器里没直接写INSERT，而是调了预编译的存储过程，光这一步就让CPU占用降了30%。 存储过程的设计更讲究。我们搞了个“审计日志轮转”机制：每天零点自动创建以日期命名的新表，旧表压缩后转存到廉价存储。有个坑差点栽了——最初用动态SQL拼表名，结果遇到权限问题卡了两天。后来改用参数化存储过程，配合EXEC AS OWNER切换执行上下文，问题秒解。现在这套系统跑了18天，生成了4700万条审计记录，存储成本比用Azure Monitor省了65%。 失败案例？当然有。第三周上线时，触发器突然漏记了2000多条更新操作。排查发现是有个存储过程用了NOCOUNT ON，导致触发器里的@@ROWCOUNT判断失效。这教训太深刻了——现在团队规范里明确要求：所有涉及审计的存储过程必须显式设置NOCOUNT OFF，触发器里必须用SCOPE_IDENTITY()替代@@IDENTITY。这些细节，网上那些“Hello World”级教程根本不会提。
文章配图，仅供参考 有人可能要问：现在都2024年了，为啥不用事件溯源（Event Sourcing）或者变更数据馈送（Change Feed）？实测下来，对于SQL Server 2019标准版这种没高级功能的场景，触发器+存储过程的组合反而更稳——不需要额外组件，出了问题直接查系统表就能定位。上个月微软官方文档还专门更新了触发器性能优化章节，这算不算官方背书？当然，这套方案也有局限。比如触发器是同步执行的，极端情况下可能影响主业务性能——我们测过，当单表并发超过5000时，延迟会跳到200ms以上。这时候就得用Service Broker搞异步审计，但配置复杂度直接翻倍。下一步我打算研究下怎么用内存优化表（In-Memory OLTP）来缓冲审计日志，理论上能把延迟压到10ms以内——不过这得先说服客户升级到企业版，难呐。 （编辑：站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
