Now it's time to see how all of the types of replication are actually implemented: how they process their work, how they relate to one another in processing, and the events they undergo. Here you will find how each replication is implemented internally in SQL Server 2000 and what events fire in a given situation.
To support and configure snapshot, merge, and transactional replication (as in the sub-objective, "Support merge, snapshot, and transactional replication models"), you need to know the internals of these three models.
Inside Snapshot Replication
So, how does snapshot replication work? How does the publication get from the Publisher to the Subscriber in this model?
The Snapshot agent copies snapshot files (containing published tables and database objects such as stored procedures) into the Snapshot folders, which by default reside in the Distributor.
The following events take place in snapshot replication:
You know how snapshot replication works. What about transactional replication? How is it internally different? The
Inside Transactional Replication
As you learned in the
The new agent used in transactional replication is the Log Reader agent. The Log Reader agent has a pretty straightforward job. Its job is to keep track of the Transaction log of the databases that are marked for Transactional replication and to copy those certain transactions (that are
Similar to the way the Distribution agent works in snapshot replication, the Distribution agent in transaction-based replication copies the tables containing the transactions from the Distributor onto the Subscriber. The snapshot transactions are held in a folder until the Distribution agent moves them to all Subscribers or a Distribution agent residing at the Subscriber calls for the transactions. The Snapshot folder contains schema and data of published tables; it is propagated to the Subscriber only once, to set up the replication for the first time. Copying the whole publication onto the Subscriber is called applying the initial snapshot . After the initial snapshot is applied at the Publisher, the Subscriber can then receive incremental changes made at the Publisher.
Transactional replication is best used in situations where you want changes to be delivered to Subscribers as they occur, when you need strict adherence to ACID properties (as defined in Chapter 2, "Data Modeling"), the network connection is reliable, or Subscribers connect frequently. This is usually the technique of choice because it requires little if any modification of the table structure and delivers changes with very little latency.
Another situation when transactional replication is useful is when the amount of data in the tables is huge and the number of changes,
The following events take place in transactional replication:
Now you have seen how transactional replication works, and have learned that it is a good solution when data needs to be sent and received in real time. In other instances, however, transactional replication is not the best alternative. Many scenarios require more flexibility, such as that
Inside Merge Replication
In SQL Server 2000, merge replication is made possible by using both the Snapshot and Merge agents. As in other types of replication, the Snapshot agent carries out the job of preparing initial Snapshot files (containing published tables and database objects such as stored procedures) and transfers them to the Snapshot folder.
As soon as you allow users in multiple locations to change their own copies of the data independently, data conflicts become inevitable. A conflict occurs when data has been changed at more than one location and then an attempt is made to synchronize the data. You have a choice whether to use row-level or
Timestamp column values are regenerated when the data is
If you are replicating columns that are defined as Foreign Keys, make sure to include the reference table in the publication so that integrity can be
The Merge agent is responsible for the detection of all conflicts. Conflicts are resolved by priority, by first synchronization, or by a custom resolver as defined through a stored procedure or COM object. The default mechanism used by SQL Server is priority based, where priorities are assigned to each Subscriber.
The Merge agent transfers modifications from the Publisher and applies them at the Subscriber and then takes modifications made at the Subscriber and applies them at the Publisher. If conflicts occur during synchronization between the Publisher and Subscriber, the Merge agent gets notified and takes the proper course of action. Taking the proper course of action depends on how resolution has been set up.
Merge replication allows for the most flexibility of all the replication models, but latency is the highest of the three models. This form of replication is appropriate in situations where the Subscribers need to be able to update the data they receive, network connectivity is not consistent, and conflicts have been minimized by application design or are
Merge replication enables each copy of data to be changed independently, but it is not the only solution if data needs to be changed at the Subscriber. The alternative is to allow updating subscriptions with transactional and snapshot replication.