How to switch between databases

Question

Our application uses a large product database that is updated once a day. Updates require a lot of time and resources. Thats why we update a backup of the product database and switch to it once the updates finished.

The only way to switch between databases right now is to misuse our loadbalancer. The application always uses the same ip to access the database server. The loadbalancer decides how this IP is resolved: After updates it uses the ip of the updated server.

This is a terrible hack. Is there any good way to switch between databases?

Answer 1

Some different options:

1)Modify the database your application connects to. Switch back and forth between the two.

2)Do your processing and updates into a staging DB, then replicate just the changes over to your live DB.

Answer 2

It sounds like you're only reading from the database. If that's the case, check out SQL Server 2005's snapshot capabilities. You can have a read-only snapshot of a database, and query it just like a regular database.

Instead of pointing at your live database, point to a fixed snapshot name, like ReadOnlyCopy. Do your normal loads in the live database, and when the loads are done, drop the snapshot and take another one. The snapshot process is quite fast.

This has some other advantages, too:

• It avoids the cost/latency issues of the load balancer.
• If your loads go horribly wrong, you can use the snapshot to insert/update records in the live database
• It requires much less disk space since you don't need twice the space for the two full copies
• It frees up your backup/restore schedule so you can do it at the right time for the app instead of the right time for the daily loads

Here's a good article from Simple Talk explaining the concepts of 2005's snapshots:

http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/