Updating Subscribers

Updating Subscribers, as the name implies, gives the Subscriber the power to update or modify data after it is replicated. This option can be used with snapshot replication and transactional replication. Data by default is replicated in one direction from the Publisher to the Subscriber. Transactional and snapshot replication enables the power to update replicated data at the Subscriber level by using subscriptions that are updateable. You can use two configurations to enable Updateable Subscribers: immediate updating and queued updating. Data is not read-only on the Subscribers. In any form of replication there is nothing in the data or database or server that requires or enforces read-only; however, if you make local changes without choosing Updating Subscribers, they might be overwritten.

Immediate Updating

Immediate updating allows replication to send changes and updates from a Subscriber back to the Publisher and then on to other Subscribers. An immediate connection must be available to use this type of Updateable Subscriber. This update of data made by the Subscriber is reflected directly back to the Publisher through a two-phase commit (2PC). Immediate updating uses triggers, stored procedures, and the Microsoft Distributed Transaction Coordinator (MS DTC) to accomplish this task. A special trigger on the Subscriber monitors the changes made to the replica on the Subscriber. When a change is made, the trigger fires and the Subscriber uses a remote stored procedure to gather the transactions and send them to the Publisher.

The Microsoft Distributed Transaction Coordinator (MS DTC) maintains a two-phase commit execution between a Subscriber and Publisher inside a stored procedure call.

The following steps are involved when immediate updating takes place:

1. A publication is enabled to support the Immediate Updating Subscribers option, and the initial synchronization takes place.

2. The Subscriber server is allowed to modify replicated data only when the transaction is executed using two phase commit (2PC) controlled by the Publisher.

3. When the Subscriber modifies the data, a trigger is fired and two-phase commit transactions are automatically sent to the Publisher without the prior knowledge of the Subscriber.

4. After the change is made and sent to the Publisher automatically, it is ultimately published to all other Subscribers who subscribe to that particular publication through normal replication methods .

Immediate updates provide for tighter control over data. To gain this control, you need to have a reliable network connection between the Publishers and Subscribers. If that reliable connection does not exist and there is still a need for Subscribers to update the data, the alternative would be Queued Updating.

Queued Updating

Queued updating is similar to immediate updating in that they both allow data to be changed from the read-only default into a modifiable replica. The major difference is that in queued updating an active network connection is not absolutely necessary; it allows snapshot replication and transactional replication Subscribers to modify published data without an active network connection to the Publisher.

The following events take place when queued updating is involved:

1. A publication is enabled to support the Queued Updating Subscribers option.

2. When the Subscriber modifies the data, a trigger marked NOT FOR REPLICATION is fired and the updates and modifications are stored in a SQL Server queue.

3. These changes in the SQL Server queue are stored in a table dedicated for queue storing called the MSreplication queue table.

4. The Queue Reader agent reads the MSreplication_queue table and applies the queued transactions to the appropriate publication. Conflicts, if any, are resolved based on the conflicts policy.

5. The changes made at the Publisher are ultimately published to all other Subscribers who subscribe to that particular publication.

With queued updates you can set up replications that allow for dial-up connections or more appropriate use of resources. Another mechanism that can be used to provide for better resource utilization is remote agent activation.

Remote Agent Activation

In SQL Server 2000, Microsoft disconnected the "who controls the schedule" part of replication from "where the work is done." Although there are default locations where each of the agents run, you may find that altering these locations provides a better load balance. You may have a push subscription that has the Distribution agent run on either the Distributor or Subscriber. You may have a pull subscription in which the Distribution agent runs on either the Distributor or Subscriber. Although this is something you are unlikely to see on the exam, it is worth noting that this option is available as a final option in replication configuration.

With remote agent activation, you can reduce the amount of processing on the Distributor or Subscriber by running the Distribution agent or Merge agent on another computer and then activating that agent remotely. You should set up regular push or pull subscriptions before configuring remote agent activation. You are not able to configure remote agent activation on a local computer (for example, when the Subscriber and Distributor reside on the same computer).

You have seen a lot of aspects of SQL Server replication and it's time to sit back and take a look at everything that is involved. The following Review Break summarizes the key points involved in replication, but so far, this chapter has only talked about SQL Server. In a lot of environments you find third-party data sources that you may also want to replicate to and/or from. After reviewing replication, you will see how every source can easily participate.

REVIEW BREAK: Aspects of Replication

You have seen a lot of the aspects of replication using Microsoft SQL Server. The different types of replication are merge, snapshot, and transactional, and you've seen what each has to offer. You have also seen several ways to make the data updateable at all servers and optimize the process through load distribution.

Merge replication is best suited to applications that need updateable information and the highest degree of site autonomy, such as in an environment that has only dial-up connectivity. Snapshot replication is more suited to situations that have relatively static data and require a periodic refresh of the data, such as with seasonal updates. Transactional replication is used in scenarios where data updates are more frequent, such as in a 24- hour order processing system.

Subscriptions can be made updateable through immediate, queued, or merge replication settings. Immediate is used in cases where the data needs to be as consistent as possible at all times and there is sufficient, reliable communication links to handle the process. The next few topics cover some of the remaining replication features that have not yet been discussed.

Several issues exist surrounding replication. Identities, constraints, available bandwidth, and conflict resolution strategies have to be thought out to provide for adequate resource utilization. Most of these issues are common to all models and are covered over the next several paragraphs; however, other issues specific to the model chosen are handled in the sections that follow.