Replication

Transactional replication begins with an initial snapshot being applied to the subscriber to ensure that the two databases are synchronized. As subsequent transactions are issued against the publisher, the replication engine applies them to the subscriber. The incremental transaction flow from publisher to subscriber makes transactional replication a good choice for maintaining a secondary copy of a database for availability or to offload reporting operations. The most common configuration for transactional replication is in a server-to-server environment.

Transactional Replication uses Log reader agent to move transactions from the transaction log on the publisher to the distribution database. The distribution agent then moves transactions from the distribution database to each subscriber.

Transactional replication propagates data in one direction – from publisher to suscriber. You can configure transactional replication with two optional modes—immediate updating subscribers and queued updating subscribers—that enable transactions to be issued against a subscriber. Transactional Replication can be configured in three different architectures – default architecture is to have one publisher with one ore more subscribers. Alternatively, transactional replication can be configured by using either bi-directional or peer to peer architecture.

Immediate Updating Subscriber Option
The Immediate Updating Subscriber option allows changes to be made at the subscriber that are propagated back to the publisher. The change is then picked up by the transactional engine and sent to all other subscribers, but it does not get reapplied to the subscriber that originated the change. To prevent the change from being sent back to the originating subscriber, a timestamp column is required on each table that is participating in replication.

The process of applying changes at a subscriber confi gured with the immediate updating
subscriber option is the following:
1. The application issues the transaction at the subscriber.
2. The trigger is fired.
3. The trigger enlists the Microsoft Distributed Transaction Coordinator (MS DTC) to
connect to the publisher and reissue the transaction.
4. The transaction commits on the publisher.
5. The trigger commits on the subscriber.
6. The transaction commits on the subscriber.

The main issue in high-availability architectures with immediate updating subscribers is that changes must be applied to the publisher. If the publisher is not available, the distributed transaction fails. Because the distributed transaction is being executed from a trigger, the initiating transaction also fails and rolls back. Therefore, the immediate updating subscriber option is an incompatible replication option for high availability.

Queued Updating Subscriber Option
The Queued Updating Subscriber option also allows changes to be made at the subscriber and then propagated back to the publisher, but it does so via a much different mechanism than the immediate updating subscriber option.

The process by which a change is applied to a subscriber using this option is as follows:
1. The application issues a transaction.
2. The trigger is fired.
3. The trigger logs the transaction into a queue (a table within the database).
4. The trigger commits.
5. The transaction commits.
The Queue Reader Agent periodically transfers the queue back to the publisher and issues all the transactions. A change transferred to the publisher is not reapplied to the subscriber that initiated the transaction. This requires a timestamp column on the table.

Because of the asynchronous processing of a queued update, data confl icts can occur. To mitigate data confl icts, when you use the queued updating subscriber option in high- availability architectures, changes are applied to the subscriber only during a failure scenario. This ensures that changes are always made to a single copy of the data, thereby enabling normal application processing to handle confl icting changes. The advantage of the queued updating subscriber option is that when the publisher is brought back online, all the changes that occurred at the subscriber during the outage can be flushed to the publisher automatically. All the planning and processes required to fail back with other architectures is eliminated because the queued updating subscriber option has the built-in capability to bring the publisher up to date again following a failover. The only step
required to fail back to the publisher is to repoint the applications.

The push Distribution Agent requires the following permissions to function properly:

• dbo_owner of the subscription database,
• Read permission of the snapshot folder, and
• member of the publication access list (PAL).

Tracer tokens enable an administrator to gather timing statistics within the replication form publisher to distributor, and also from distributor to subscriber, to monitory point to point latency.

Source: MCTS Training Kit – Mike Hotek