Developing and Administering
Database Replication

This section explains the differences between Oracle and Microsoft SQL Server replication support.

SQL Server implements snapshot replication in place of Oracle’s read-only snapshot. As its name implies, snapshot replication takes a picture, or snapshot, of the published data in the database at a moment in time. Snapshot replication requires less constant processor overhead than transactional replication because it does not require continuous monitoring of data changes on source servers. Instead of copying INSERT, UPDATE, and DELETE statements (characteristic of transactional replication), or data modifications (characteristic of merge replication), Subscribers are updated by a total refresh of the data set. Hence, snapshot replication sends all the data to the Subscriber instead of sending only the changes.

SQL Server also offers transactional replication, a type of replication that marks selected transactions in the Publisher’s database transaction log for replication and then distributes them asynchronously to Subscribers as incremental changes, while maintaining transactional consistency.

SQL Server implements merge replication in place of Oracle’s updatable snapshots and Oracle’s multimaster replication model. Merge replication is a type of replication that allows sites to make autonomous changes to replicated data, and at a later time, merge changes made at all sites. Merge replication does not guarantee transactional consistency.

SQL Server offers heterogeneous replication, which is the simplest way to publish data to a heterogeneous Subscriber by using ODBC and creating a push subscription from the Publisher to the ODBC Subscriber. As an alternative, however, you can create a publication and then create an application with an embedded distribution control. The embedded control implements the pull subscription from the Subscriber to the Publisher. For ODBC Subscribers, the subscribing database has no administrative capabilities regarding the replication being performed.