Database Reference
In-Depth Information
Use backup/restore. Even if this approach seems crude when compared
with the more elegant synchronize, it has some advantages. For a start, we're
going to be backing up our database anyway after it has been processed, so
using the backup to copy the database to another server will require little
extra development. Secondly, we can take the same backup file and copy it to
multiple destination servers, where they can be restored in parallel.
A problem with this approach is that if we try to restore over our existing
database on the destination server, we cannot query that database while the
restore is taking place. One way of working around this would be to restore
to a different database to the one that users are currently querying, and
then to redirect all new queries to this second database after the restore has
completed. This would be feasible if the only client tool used was Reporting
Services, where connection strings can be parameterized, but not if a client
tool like Excel was used. Another issue is that we have to copy the entire
database over the network even if there is only a small amount of new data,
and if the backup file is very large, this might take a relatively long time and
require extra disk space.
Use attach/detach. This method simply involves detaching the database from
the source server, copying the database files over to the destination server,
detaching the old version of the database on the destination server, and then
reattaching the new version of the database. Since attaching and detaching
a database is a very fast operation, this method involves the minimum of
downtime; it also has the advantage that if a database is attached to one
instance with its ReadMode property set to ReadOnly , the same database files
can be attached to multiple other instances in the same way.
The main problem with this approach is that when we detach a database, it
cannot be queried at all, and any open connections to it are closed. This may
lead to errors in some client tools and, at best, all users will have to reconnect
to the server once the attach operation has completed.
Our recommendation is to use the Synchronize command if it runs fast enough and
doesn't result in queries being blocked for too long. If we can't use Synchronize
because it takes too long, or we don't mind that connections will be dropped, then
attach/detach is the best option.
 
Search WWH ::




Custom Search