实时 SQL Server 驱动系统中的异常处理
我在 .NET Winforms 中开发了一个报告查看器（它只运行查询并显示结果）。
这适用于报告数据库。然而，上面是一个更大的应用程序的一小部分，它从另一个数据库获取数据。它看起来像这样：
受监控的系统状态发生变化（例如延迟增加）=>事件作为事务记录到SQL Server数据库（将此数据库称为A）=>这会触发一个触发器，将相同的事件写入报告数据库。
我不确定这两个数据库之间的差异，它们可能是针对不同的目标进行调整的，或者这两个数据库可能存在一些财务甚至政治原因。
不管怎样，提到了报告数据库“事务上依赖于”主数据库这一术语。这究竟意味着什么？报表数据库完全依赖于数据库A的事务？这让我想到了一些问题：
1）如何处理报告数据库没有磁盘空间，但数据库 A 仍在向报告数据库触发触发器的情况？排队好不好 2）链接到上面的内容，如果我对触发器进行排队并且它们的数据无法触发到报告数据库（不知道如何，但从概念上讲......），它会起作用吗？即使如此，这也使得系统不是实时的。
在这样的设置中，异常处理是否存在其他危险/问题？
谢谢
I have developed a report viewer in .NET Winforms (it just runs queries and displays results).
This works against a reporting database. However, the above is a small subset of a much larger application, which gets data from another database. It looks like this:
Monitored system has a change in state (e.g. latency increases) => Event is recorded into SQL Server database (call this database A) as a transaction => This fires a trigger to write the same event into the reporting database.
I am not sure about the differences between the two databases, they may be tuned for different goals or there may be some financial or even political reason for the two databases.
Anyway, the term was mentioned that the reporting database is "transactionally dependent" on the main database. What exactly does this mean? The reporting database depends entirely on the transactions of database A? This made me think of some questions:
1) How could I handle the situation that the reporting database has no disk space, but database A is still firing triggers to the reporting database? Would it be good to queue
2) Linked to the above, would it work if I queue the triggers and their data not able to fire into the reporting db (not sure how, but conceptually...)? Even then, this makes the system not real time.
Are there any other dangers/issues with exception handling in a setup like this?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这种依赖关系在生产中实际上是非常糟糕的。这一次，触发器和更新（远程）数据库肯定会降低性能。但更重要的是可用性问题。依赖于数据库 A 的应用程序现在与数据库 B 的可用性相关联，因为如果数据库 B 不可用，则触发器无法执行其工作，它将失败并且应用程序将遇到错误。因此，现在数据库 B 的 amdinsitrator 正在处理使用数据库 A 的应用程序的操作。解决
此问题的方法有很多，最简单的一种是从数据库 A 中的发布部署事务复制，并在这从事务角度隔离了两个数据库，允许依赖数据库 A 的应用程序在数据库 B 不可用或速度缓慢时不受阻碍地运行。
Such dependencies are actually very bad in production. For once, triggers and updating (remote) databases is a sure shot to kill performance. But more importantly is the issue of availability. The applicaitons that depend on Database A are now tied to the availability of Database B, because if database B is unavailable then the trigger cannor do its work, it will fail and the application will hit errors. So righ now the amdinsitrator(s) of database B are on hook for the operations of the applications using database A.
There are many approaches for this issue, the simplest one is to deploy transactional replication from a publication in database A with a subscription in database B. This isolates the two databases from a transactional point of view, allowing for application dependent on database A to go ahead unhintered when database B is unavailable, or just slow.
如果系统必须是实时的，那么触发器是唯一的方法。请注意，触发器是完全同步的 - 对报告数据库的操作必须成功完成，否则触发器将失败，并且您对事务数据库的操作可能会失败，因为它位于触发器中，即原始表上的语句将会失败，这可能会被捕获，也可能不会被捕获，但无论如何，事务数据库中该表的更改都不会发生。
这种情况有充分的理由，但它确实创建了事务数据库对报告数据库的依赖性，因为如果报告数据库发生故障，事务数据库实际上会变成只读或更糟。
这并不是你真正想要的。
如果您的数据库具有相同的结构，您可以查看复制。通常，当我想到报告数据库时，我想到的是具有不同结构的东西，该结构针对报告进行了优化，而不仅仅是出于性能原因而隔离的数据的另一个副本（这很好，但这基本上只是将硬件扔给停止报告用户伤害交易用户的问题）。
If the system has to be real time, then triggers are the only way. Note that triggers are fully synchronous - the operation on the reporting database will have to complete successfully, or the trigger will fail, and it's likely you will then fail your operation on the transaction database since it's in a trigger, the statement on the original table will fail, which may or may not be caught, but either way the change to that table in the transaction database will not occur.
There are valid reasons for this scenario, but it really creates a dependency of the transaction database on the reporting database, since if the reporting database is down, the transaction database effectively becomes read-only or worse.
That's not really what you want.
You can look at replication if your database have the same structure. Typically, when I think of a reporting database, I'm thinking of something with a different structure which is optimized for reporting, not just another copy of the data isolated for performance reasons (which is fine, but this is basically simply throwing hardware at the problem to stop reporting users hurting transaction users).