Andrew Fraser DBA

11 Replication

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)

Three types of Replication exist:

1. Snapshot Replication – just like Oracle snapshot replication, data is refreshed periodically.
   This can have updating subscribers (updateable snapshots), where the subscriber can change data in the snapshot and this change will be replicated back to the snapshot.
2. Transactional Replication – this replicates data by monitoring transaction logs. Block changes for replication are specifically marked in the transaction logs. This is like snapshot replication, but with constant data copying. So it provides the same functionality as distributed database triggers, but with fewer potential problems.
   This can have updating subscribers, where the subscriber can change data and this change will be propagated back to the publisher.
3. Merge Replication – this uses triggers on each copy of the data. Conflicts are resolved at merge time by means of a “timestamp” column on each row.

Transactional replication is the most commonly used.

Three server types are involved:

1. Distributor – responsible for synchronizing data between publishers and their subscribers. The data to be synchronized is stored on disk files on the Distributor machine. A distribution database stores distribution history, and in transactional replication, also keeps the information culled from the redo logs for propagation.
2. Publisher – source of data
3. Subscriber – receiver of data

In Push Subscription the Publisher defines the subscription to the data.

In Pull Subscription the Subscribers define the subscriptions to the data.

To set up Replication, use the Configure Publishing and Distribution Wizard in Enterprise Manager.

SQL Server Enterprise Edition includes Oracle databases in its replication.

Lab 11 – Set up various types of replication on the desktop.

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)