Microsoft® SQL Server® 2008 Bible

Chapter 36. Replicating Data

IN THIS CHAPTER

• Replication concepts

• Configuring replication

Replication is an optional native SQL Server 2008 component that is used to copy data and other database objects from one database or server to another.

Replication is used for many purposes, listed here in order from most popular to rarely used:

• Offloading reporting from an OLTP server to a reporting server

• Data consolidation—for example, consolidating branch office data to a central server

• Data distribution—for example, distributing data from a central server to a set of member servers to improve read performance

• Disaster recovery—replication can be used to keep a DR (disaster recovery) server synchronized with the main server, and clients can be manually redirected to the DR with minimal interruption

• Synchronizing data with a central server and a mobile sales force

• Synchronizing data with handheld devices (such as PDAs and smartphones)

Replication processes can be made to be highly scalable, and typically can synchronize data between servers/databases with acceptable latency. Latency reflects the lag of time between when data is sent (replicated) from the source server and received at the destination server.

Replication is not the only way to move data between servers. There are several alternatives, each with its own pros and cons:

• bcp utility

• SSIS

• Distributed transactions

• Triggers

• Copy Database Wizard

• Backup and restore

• Log shipping and database mirroring

Bulk copy program (bcp) is a command-line tool that ...