729
Chapter 28: Replicating Data
28
Copy Database Wizard
The Copy Database Wizard can move or copy a database from one server to another. It is
intended for a single use move or copy. In the move mode, you can move only the database one
time. In the copy mode, the database can be copied multiple times if you specify the options
to delete the database and the database fi les that might exist on the destination server.Backup and Restore
Backup and restore copies the entire database to the destination server. The level of granu-
larity possible for the preceding options are tables (bcp and SSIS) and transactions (trig-
gers and distributed transactions).Backup and restore, log shipping, database mirroring, and the Copy Database Wizard rep-
licate entire databases. As the name suggests, backup and restore involves backing up the
database on the source server and restoring it on the destination server. This option is
not scalable for large databases, and the database must go offl ine while the database is
restored. It is not a good option in environments with real-time data requirements because
the data becomes progressively out-of-date until the latest backup is restored on the desti-
nation server.Log Shipping
Log shipping is continuous backup and restore. The log is backed up on the source server
and applied to a previously restored database backup on the destination server. Log ship-
ping is not considered to be scalable, especially for large databases or large numbers of
databases. The database on the destination server is not accessible with log shipping. There
are options to make it accessible, but it will be in read-only mode, and users need to be
kicked off when the next log is ready to be applied.Database Mirroring
Database mirroring is continuous log shipping. Changes to the database transaction log are
continually shipped from the source server to the destination server. The database on the
destination server is inaccessible while being mirrored. There are two modes of database
mirroring: high safety and high performance.With high safety, application writes on the source server are not committed on the source
server until they are also committed on the destination server. This can cause increased
latency for all writes on the destination server, which may make database mirroring not a
good fi t for your particular requirements.High-performance mode does not have this problem because changes occurring on the
source server are applied to the destination server asynchronously. However, the high-
performance option is only available on the Enterprise Edition of SQL Server 2005 and SQL
Server 2008.c28.indd 729c28.indd 729 7/31/2012 9:51:11 AM7/31/2012 9:51:11 AM
http://www.it-ebooks.info