等待 ADO.NET 或 TSQL 中的事务复制
我的 Web 应用程序针对 SQL Server 2008 使用 ADO.NET。数据库写入针对主（发布者）数据库进行，但读取在主和辅助（订阅者）数据库之间进行负载平衡。我们使用 SQL Server 的内置事务复制来保持辅助数据库最新。大多数时候，几秒钟的延迟不是问题。
但是，我确实有一种情况，我想阻止直到事务在辅助站点提交。阻塞几秒钟是可以的，但向用户返回过时的页面则不行。 ADO.NET 或 TSQL 中有什么方法可以指定我要等待复制完成吗？或者我可以从发布者处检查事务的复制状态，而无需手动连接到辅助服务器。
[编辑] 99.9%的情况下，订阅者中的数据是“足够新鲜”的。但有一项操作会使其失效。我不能每次都从出版商处读取它，以防它变得无效。如果我无法在事务复制下解决这个问题，你能建议一个替代架构吗？
My web app uses ADO.NET against SQL Server 2008. Database writes happen against a primary (publisher) database, but reads are load balanced across the primary and a secondary (subscriber) database. We use SQL Server's built-in transactional replication to keep the secondary up-to-date. Most of the time, the couple of seconds of latency is not a problem.
However, I do have a case where I'd like to block until the transaction is committed at the secondary site. Blocking for a few seconds is OK, but returning a stale page to the user is not. Is there any way in ADO.NET or TSQL to specify that I want to wait for the replication to complete? Or can I, from the publisher, check the replication status of the transaction without manually connecting to the secondary server.
[edit]
99.9% of the time, The data in the subscriber is "fresh enough". But there is one operation that invalidates it. I can't read from the publisher every time on the off chance that it's become invalid. If I can't solve this problem under transactional replication, can you suggest an alternate architecture?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQL Server 没有这样的解决方案，但以下是我在其他环境中解决该问题的方法。
在应用程序中使用三个单独的连接字符串，并根据查询的需要选择正确的连接字符串：
如果您将应用程序设计为从一开始就使用这 3 个连接字符串，则扩展会容易得多，尤其是在您遇到的情况下。
There's no such solution for SQL Server, but here's how I've worked around it in other environments.
Use three separate connection strings in your application, and choose the right one based on the needs of your query:
If you design your app to use those 3 connection strings from the start, scaling is a lot easier, especially in the case you're experiencing.
您正在描述同步镜像情况。根据定义，复制不能支持您的要求。复制必须等待事务提交，然后才能从日志中读取事务并将其传递给分发者，再从分发者传递给订阅者，这意味着根据定义，复制有一个数据流出的机会窗口。同步。
如果您需要执行读取数据的权威副本的操作，那么您应该在客户端中做出该决定，并确保在这种情况下从发布者处读取数据。
虽然理论上您可以验证某个交易是否已分发给订阅者，但您不应该以此为基础进行设计。事务复制在设计上不提供延迟保证，因此您不能依赖“完美的一天”操作模式。
You are describing a synchronous mirroring situation. Replication cannot, by definition, support your requirement. Replication must wait for a transaction to commit before reading it from the log and delivering it to the distributor and from there to the subscriber, which means replication by definition has a window of opportunity for data to be out of sync.
If you have a requirement an operation to read the authorithative copy of the data, then you should make that decission in the client and ensure you read from the publisher in that case.
While you can, in threory, validate wether a certain transaction was distributed to the subscriber or not, you should not base your design on it. Transactional replication makes no latency guarantee, by design, so you cannot rely on a 'perfect day' operation mode.