Best practice to replace the read-only SQL Server database gracefully

advertisements

I have a read-only database that has cached information which is used to display pages on the site. There are processes that run to generate the database, and those run on a different server. When I need to update the live database, I restore this database to the live server, to a new name and file. Then I drop the live database and rename this one to the original name. Currently I do this with a stored procedure, that kills all connections via 'sp_who' and 'kill', then the drop database command, then 'sp_renamedb'. Now when I kill all connections it will throw an error on the site for people and crawlers currently accessing those pages, is there a better methodology for performing this procedure?


Probably what you want to do is take the live database offline with the command:

ALTER DATABASE name SET OFFLINE

You can read more here, but it says:

The above command attempts to take the named database off-line immediately. If a user or a background process is currently connected to the database the command cannot be completed. In this situation, the ALTER DATABASE statement will be blocked and will wait until all connections are closed. This ensures that no transactions are rolled back unexpectedly. During the period of blocking, no new connections to the database will be permitted.

Once the database is offline, you should be able to do your restore/rename operations safely and bring it back online when done. You might have to do some playing around to see what is permitted while the database is in the offline state.

If you have trouble doing the restore/rename while it's offline, you'll want to bring it back online in single user mode.

Connection pooling or other long-running connections may cause problems in this scenario. You may want to set up a script to wait for a period of time after the ALTER DATABASE SET OFFLINE command has been issued (say 15 minutes), and if the database is still not offline yet, you can reissue the command with the WITH NO_WAIT option to force it offline.

If that isn't high-powered enough for you, you can also gracefully shutdown SQL Server...that will also wait until all work in the server is done before stopping.