Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server 2000 Replication 101: Terminology, Types, and Configuration : Page 3

It's wise to make sure you have a clear understanding of replication terminology, types of replication, and exactly what is occurring when you install and configure replication.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Types of Replication
Before you get into the details of setting up replication, you should have a basic understanding of the types of replication. There are only three: snapshot, transactional, and merge replication. Three is a nice simple number, but the variations and options on transactional and merge can make the number of possible options much larger.
Replication can provide you with some interesting solutions for your data tier architecture because it allows you to use multiple database servers on the back end. You can publish to multiple servers, gather data from multiple servers, provide loosely connected clients with the ability to share data with a centralized database, and scale out database work in a horizontal fashion.
Because snapshot replication delivers a one-time-only copy of selected data, and both transactional and merge replication use snapshot replication to initialize, you'll hear most often about just transactional replication and merge replication.

Snapshot Replication
Snapshot is the simplest form of replication. It makes a simple copy of a data set and delivers it to a subscriber. The direction of the data is one-way only, with no updates allowed from the snapshot's subscribers back to the publisher. You can use a snapshot to send data once, or you can repeat a snapshot on a regular basis. All three types of replication use snapshot replication. You can use snapshot replication by itself, but SQL Server also uses it as the method to initialize both transactional and merge replication. Behind the scenes, snapshot replication uses a Snapshot Agent (snapshot.exe) in combination with the Distribution Agent (distrib.exe) to export all relevant information about a publication's data to a text file. The Distribution Agent then delivers that data to the subscriber.

Transactional Replication
Transactional replication is usually also a one-way-only incremental form to update subscribers. It gets its name because it uses an agent on the distributor that reads the transaction log of the publisher database (logread.exe.) Transactional replication is normally one-way only, and you treat the subscribers as read-only. Transactional replication is very sensitive to connectivity issues, and normally assumes continuous connections between publisher and subscribers. If a subscriber is disconnected, data will build up in the distribution database, to apply to the subscriber once it connects again. If the subscriber gets too far out of date, a snapshot will have to be run again.

When you use transactional replication to distribute data to read-only subscribers, it can be very fast, delivering data changes from publisher to subscriber in a matter of seconds. This can make it an ideal mechanism for reporting servers. If you have updating subscribers, however, the transaction delivery time can increase significantly. Merge Replication
Merge replication was introduced as a more efficient method of allowing updates across publishers and subscribers. It uses a Merge Agent (merge.exe) on each server to communicate between publisher and each subscriber. Merge replication requires a ROWGUID column on each replicated table that it will use for tracking changes.

In merge replication, SQL Server uses triggers on each replicated table to populate the MSmerge_contents system table in each database. It does not read the transaction log of the publisher and does not use the distribution server for store-and-forward. Merge replication has the remarkable feature that subscribers need only be occasionally connected to the publisher, and when the subscribers do connect, they can automatically catch up.

The extra metadata stored on each node in a merge replication application can cause a merge to sometimes be somewhat slow under heavy transaction loads. Choosing the Type of Replication
Before you dive into configuring or installing replication, you should decide which type of replication you need. You need to ask yourself several questions before you choose a type of replication.

First of all, what type of update scenario do you need between the publisher and the subscriber data? Do you need updates on the subscriber to replicate back to the publisher? Will the subscribers be read only? If you want to have updates made on a subscriber replicated back to the publisher, you must use merge or transactional replication with queued or immediate updating subscribers. You cannot use snapshot replication or one-way only transactional replication.

You also need to consider how transactionally consistent the data must be between the publisher and the subscriber. If you allow updates among subscribers, will having data converge to the same values across publisher and subscribers be sufficient, or do you need the subscribers to be more transactional? Snapshot replication is, by definition, out of date right after the application of the snapshot, until the next snapshot occurs. Transactional replication provides the maximum amount of consistency between publisher and subscribers. Merge replication uses a strategy of convergence, and the resulting convergent data may differ depending on the order that changes are applied.

Once you've decided on the type of replication you desire, you can move on to configuring replication.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date