在还原 SQL Server 数据库之前等待连接关闭 [英] Wait for connections to close before restoring SQL Server database

问题描述

我有一个使用两个数据库的 Web 应用程序.DB1 用户执行他们的 CRUD(创建、读取、更新、删除)操作.数据库 DB2 是我用于报告目的的不同服务器上的只读数据库.我的 DB1 每小时都会保存事务日志，并且在 DB2 上我有一项工作在 DB2 上恢复它们​​以使其保持最新状态.

I have a web application that uses two databases. DB1 Users perform their CRUD (Create, Read, Update, Delete) operations. Database DB2 is a readonly database on a different server that i use for reporting purposes. Every hour my DB1 saves transaction logs and on DB2 i have a job that restores them on that DB2 to keep it ~up to date.

我面临的问题是，如果有用户在 DB2 上运行报告(经常发生)，当我获得恢复数据库的独占访问权限时，他们会与 sql 服务器断开连接.恢复每个日志所需的时间在 1-4 分钟之间.

Problem i am facing is that if there are users running reports on DB2 (happens quite often) they get disconnected from the sql server as i obtain exclusive access to restore the database. Time needed to restore each log ranges between 1-4 minutes.

如何实现让我们称之为等待-n-恢复功能，我的工作在将数据库切换到独占访问并恢复日志之前等待用户的查询完成?

How can I implement let's call it wait-n-restore functionality where my job waits for users' queries to finish before switching the database to exclusive access and restoring the log?

我的两台机器都运行 SQL Server 2008 64 位标准版

Both of my machines run SQL Server 2008 64 Bit Standard Edition

推荐答案

我的问题的很大一部分是使用池连接 - 在这种情况下，即使没有执行报告，连接也保持活动状态.我修改了我的连接字符串，将连接池设置为 false 并在循环中检查打开的用户连接到您的报告数据库，直到值为 0.幸运的是，我不必创建触发器来让用户退出.

Big part of my problem was using pooled connections - in this case even when no reports were executed connections were kept alive. I modified my connection string to have connection pooling set to false and check for open user connection to your reporting DB in a loop till the value is 0. Fortunately i do not have to create the trigger to bounce users off.

这篇关于在还原 SQL Server 数据库之前等待连接关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！