Best practice for graceful replace of read-only SQL Server database

Question

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?

Answer 1

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.

Answer 2

+1 for Mike Sharek - I like your solution, it's very tidy and prevents users being disconnected abruptly (and very rudely).

Another alternative, if you don't want to sit around waiting for users to log off for hours. You could send an alert to connected clients through an AJAX mechanism you build into the site that basically sits "open" and waits for a notification from the server for a period before it times out and reconnects so essentially the XmlHttpObject is always waiting for a response. At the point of receiving the response, it displays it onscreen and then opens another connection. You then have an administrator interface that pushes out messages to the waiting AJAX component.

This would allow you to send a message in approximately realtime in a similar manner to the way Microsoft's Outlook Mobile Access works allowing users to be notified that in 10 minutes, the site is going down for maintenance.

I'm not sure if there's any prebuilt components out there that do this, but I'm sure it couldn't be too difficult to implement.

It doesn't help you out in the immediate sense, but it's worth thinking about for future releases of your site.

Answer 3

If you want to do something really seamless for your users. I would do something like this.

When you copy the new version of your database over give it a new name, maybe named with the datetime, depending on how often you update it. Once it is there tell your web application to start using the new database. This could be done by changing the connection string in the web.config, or having it a configuration database.

Once a certain amount of time has passed, based on the usage characteristics of your site remove the old database.

Answer 4

Set up transactional replication with your data generation server as the publisher and your live server as a subscriber. The client connections won't notice a thing.