Wait for connections to close before restoring SQL Server database

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.

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.

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?

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

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.

You probably have an alter Database Setting Single User or Admin mode that has a "WITH IMMEDIATE ROLLBACK" in it. That is what is kicking the users out. Take that clause out and it will wait for them to leave (but won't stop new ones from coming in also).

RE: Your Kill sProc: you might want to look at the "WITH IMMEDIATE ROLLBACK" option.

As for preventing new connects: What I've done in the past is to disable the Logins (Server Principal) of the application users, wait up to 10 minutes checking every minute to see if everyone is out. After that I do the ALTER DATABASE...WITH IMMEDIATE ROLLBACK and then onto whatever OPS function needs to be performed.

I've been fortunate in that the Logins were always single-use application user logins (ie, SQL Logins for this purpose only) If you cannot do that, then the only other thing that I can think of at the moment would be to deny the CONNECT permission to the DB Users (database principal). and then REVOKE the DENY later on. I've never done it like this, but it should go something like:

DENY CONNECT TO SomeDBUserName;

I assume that your restore is happening as a job. Then what you need is a logon trigger. Here is how you create a logon trigger:

Logon Triggers

A logon trigger fires when a session is established. At that point the LOGON event is raised.

The life-cycle of a logon trigger is very simple: a user connects to Sql Server, the trigger fires, an implicit transaction is opened and then it's up to you! If, for any reason, you want to deny the attempt to log in to Sql Server, just issue a ROLLBACK statement and you're done.

Here is a sample logon trigger:

USE master;
GO
CREATE LOGIN security_login WITH PASSWORD = 'P@ssw0rd'; 
GO
GRANT VIEW SERVER STATE TO security_login;
GO
CREATE TRIGGER connection_deny_trigger
ON ALL SERVER WITH EXECUTE AS 'security_login'
FOR LOGON
AS
BEGIN
<*Your conditional code goes here*>
    ROLLBACK;
END;

You could define your job to do this:

SELECT COUNT(*) from sysprocesses where spid in(
SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process = 1) AND
dbid= DB_ID('YourReportingDatabase')

Raj

上一篇: PHP的SOAP扩展有没有一个好的选择？

下一篇: 在恢复SQL Server数据库之前等待连接关闭