Wait for connections to close before restoring SQL Server database

Question

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

Answer 1

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 (i.e., 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;

Answer 2

Can the reporting be redirected to a different database name? If yes you can create database snapshots of DB2 and have the reports run from these snapshots. After each log restore you create a new snapshot and mark it somewhere as 'the' current snapshot and all new reports start running against this snapshot. When a new log is shipped, a new snapshot is created and new reports go against the new snapshot, while old, running, reports remain in the previous snapshot. When the last report is done with the old snapshot and there are no longer users referencing it, it can be dropped. This way no report is ever interrupted, at the cost of extra storage: each new log will cause the old snapshot(s) to start do the 'copy-on-write' of affected pages.

Answer 3

Is the backup required to happen every hour, or would another method of replication meet your needs better than the custom process you've set up? You could do log-shipping, which can be configured to ship every hour, and SQL Server would take care of it natively, blocking users while the logs are restored.

I can see your problem, and I don't think SQL Server has the functionality you're looking for (blocking new connections while allowing existing connections to finish), but there are other work-arounds that would give you the same replication functionality and might accomplish you business requirements better.

Answer 4

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:

• Step 1: Enable Logon trigger

• Step 2: Check for open user connection to your reporting DB in a loop till the value is 0

  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')

• Step 3: Set DB to single user and restore your logs

• Step 4: Reset DB to multi-user and Disable Logon Trigger

Raj

Answer 5

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.