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

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.

Answer 2

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;