Azure SQL数据库触发器可将审核信息插入Azure表 [英] Azure SQL Database trigger to insert audit info into Azure Table

问题描述

我正在研究数据库审核解决方案，并且正在考虑让SQL Server触发器负责更改并将其插入审核表中.由于这是一个SQL Azure数据库，并且会很大，因此我担心由于审核而导致数据库不断增长的成本.

I am working on a database auditing solution and was thinking of having SQL Server triggers take care of changes and inserting them into an auditing table. Since this is a SQL Azure Database and will be fairly large I am concerned about the cost of a growing database due to auditing.

为了减少审核所需的成本，我正在考虑将审核表(或多个表)存储在Azure表而不是Azure SQL数据库中.因此问题就变成了，如何获取SQL Server触发器以将更改后的数据获取到Azure表中?

In order to cut down on the costs needed for auditing purposes, I am considering storing the audit table (or tables) in Azure Tables instead of Azure SQL databases. So the question becomes, how to get the SQL Server trigger to get the changed data into Azure Tables?

我唯一能想到的是在SQL数据库中有一个审核表(或多个表)，以便触发器可以在本地插入行，然后每隔X秒就有一个工作人员角色从中拉出任何行并移动它们.到Azure表并从SQL数据库表中删除，以免它变大.

The only thing I can come up with is to have an audit table (or tables) in SQL Databases so the trigger can insert the rows locally, and then have a Worker Role every X seconds pull any rows from that and move them to Azure Tables and delete from the SQL Database table so it doesn't grow large.

是否有更好的方法来进行此集成?我能以某种方式从触发器将消息放入队列吗?

Is there a better way to do this integration? Can I somehow put a message in a queue from a trigger?

推荐答案

Azure SQL数据库(以前为SQL Azure)不支持CLR(因此没有EXTERNAL NAME触发器参数)，因此触发器无法在外部执行任何操作T-SQL.如果希望审核内容进入表，则可以采用您想出的方法(暂时写入SQL表，然后将内容定期移至表).您还可以采用其他方法(这可能是观点/主观的，在此不予理))，但是由于您询问了队列并说明了如何使用Azure队列，因此请花一分钟的时间讨论队列概念:

Azure SQL Database (formerly SQL Azure) doesn't support CLR (hence no EXTERNAL NAME trigger parameter) so there's no way for your triggers to do anything outside of T-SQL. If you want audit content to go to a table, you could take the approach you came up with (temporarily write to SQL table, then move content periodically to Table). There are other approaches you could take (and this would be opinion/subjective, frowned upon here), but going with the queue concept for a minute, since you asked about queues, and illustrating what you could do with Azure Queues:

您可以使用Azure队列来指定要在SQL数据库中插入/更新的项目.然后，队列处理代码可以负责执行更新并写入Azure表.由于必须在处理后显式删除队列消息，因此，如果在执行过程中发生某些故障(例如，您写入SQL但在写入表存储之前失败)，则可以简单地重复队列消息处理.如果您没有在超时值之前将其删除，则该消息最终将变为可见以供再次阅读.只要您的操作是幂等的，就可以采用这种模式.

You could use an Azure queue to specify an item to insert/update in your SQL database. The queue processing code could then be responsible for performing the update and writing to the Azure table. Since the queue messages must be explicitly deleted after processing, you could simply repeat the queue message processing if something failed during execution (e.g. you write to SQL but fail before writing to table storage). The message eventually becomes visible for reading again, if you don't delete it before its timeout value. As long as your operations are idempotent, you'd be ok with this pattern.

这篇关于Azure SQL数据库触发器可将审核信息插入Azure表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！