Professional SQL Server™ 2005 Administration

Chapter 16. Replication

Today's enterprise needs to distribute its data across many departments and geographically dispersed offices. SQL Server replication provides ways to distribute data and database objects among its SQL Server databases, databases from other vendors such as Oracle, and mobile devices such as Pocket PC and point-of-sale terminals. Along with log shipping, database mirroring, and clustering, replication provides functionalities that satisfy customers' needs in load balancing, high availability, and scaling.

In this chapter, we introduce you to the concept of replication, how to implement basic snapshot replication, and things to pay attention to when setting up transactional and merge replication. We also go over the newly introduced peer-to-peer replication in SQL Server 2005. Along the way, we go over some best practices.

Replication Overview

SQL Server replication closely resembles the magazine publishing industry, so we'll use that analogy to explain its overall architecture. Consider National Geographic. For each monthly issue, the publisher has to collect articles, photos, and interviews. These items then form a pool, from which the editors pick out items to be included in a monthly issue, called a publication. Once a monthly publication is printed, it is shipped out via various distribution channels to subscribers all over the world.

In SQL Server replication, similar terminology is used. The pool from which a publication is formed can be considered a database. ...