The process of copying and distributing data and database objects from one SQL Server database (publisher) to one or more destination databases (subscribers), enabling data distribution, read scale-out, and disaster recovery (DR) scenarios.
- Replication types: SQL Server supports three primary replication models: Snapshot Replication (a full copy at a specific point in time), Transactional Replication (continuous streaming of data changes), and Merge Replication (bidirectional synchronization with conflict resolution).
- Common use cases: Replication is widely used to distribute data to geographically dispersed offices, offload reporting workloads to read-only subscriber databases, and synchronize data across heterogeneous environments.
- Replication architecture: The replication framework consists of a Publisher (source database), a Distributor (manages the distribution database and replication agent jobs), and one or more Subscribers (destination databases).
- Monitoring and maintenance: DBAs must monitor replication latency, replication agent status, and distribution queue depth, as replication failures can lead to data divergence between publishers and subscribers.
- High availability considerations: Replication is not a replacement for high availability because it does not provide automatic failover. For HA/DR scenarios, Always On Availability Groups or Failover Cluster Instances are the recommended solutions.
- Related terms: Transactional Replication, Merge Replication, Always On Availability Groups, Replication Monitor.
