SQL Replication captures changes to source tables and views and
uses staging tables to store committed transactional data. The changes are
then read from the staging tables and replicated to corresponding target tables.
The following figure shows a simple configuration in SQL Replication.
Figure 1. A simple configuration in SQL Replication
SQL Replication offers several advantages:
- Capture once
- With staging tables, data can be captured and staged once for delivery
to multiple targets, in different formats, and at different delivery intervals.
Both tables and views are supported as sources.
- Flexibility
- You can replicate continuously, at intervals, or for one time only. You
can also trigger replication through database events. You can replicate a
subset of the table by excluding columns and filtering rows. You can also
use expressions and other functions to transform data before it is applied.
- Hub-and-spoke configurations
- You can replicate data between a master data source and one or more replicas
of the source. Changes that are made to the master source are propagated to
the replicas, and changes that are made to the replicas are also propagated
to the master source.