Replication Concepts

SQL Server replication uses a publish-subscriber mode that may include a separate distribution server. It enables you to copy or move data and database objects to another database. Consistency is maintained by a synchronizing process executed by replication. There can be three types of servers in a replication topology:

  • Publisher: The source server.
  • Distributor: For transactional replication and peer-to-peer replication, the distributor is where the changes are stored until they are replicated to the destination server. For merge replication, the distributor is merely a repository for replication process history. Changes and historical information are stored in a database called the distribution database.
  • Subscriber: The destination server.

Types of Replication

SQL Server 2012 offers five basic types of replication, each serving a different purpose:

  • Snapshot replication: A point-in-time image of database objects (a snapshot) is copied from the source server to the destination server. This image generation and deployment can be scheduled at whatever interval makes sense for your requirements; however, it is best used when the majority of your data seldom changes, and when it does, it changes at the same time.
  • Transactional replication: Transactions occurring on the source server are asynchronously captured and stored in a repository (called a distribution database) and then applied, again asynchronously, on the destination server.
  • Oracle publishing: This ...

Get Microsoft SQL Server 2012 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.