Choosing a replication type

By Chris Miller on 26 July 2000 | Tags: Replication

Steve writes "Working on SQL DBA exam, not finding clear difference between Transactional with Immediate-Updating Subscribers and Merge Replication. Can anyone provide a pair of scenarios that would require choosing one over the other?"

Transactional Replication is what we used to call "tightly coupled" replication, or realtime replication. Essentially, transactional replication uses the Microsoft Distributed Transaction Coordinator, and it requires all of the servers involved in replication to complete their transactions before they are finalized. In other words, if any of the servers that are involved in replication are not available, no transactions can complete. The upside is that there can never be any conflicts because all of the servers involved in the replication are always consistent with each other.

Merge replication is a little bit looser than that. If a server isn't available, the updates to that server are queued up, and when the server becomes available it will receive all of its updates. Unfortunately, if two users update a record at the same time, merge replication will result in conflicts which will need to be manually resolved.

Either one of these types of replication is valid when there are multiple servers which may update a database. The differences are in overhead and reliability. Transactional replication will have a higher overhead in terms of CPU utilization, network traffic, disk time, and record locks. Merge replication has more administrative overhead, because someone has to handle conflict resolution.

So, when do you use Transactional Replication? Well, if you can deal with the overhead (minimal if you are careful) and if you can guarantee that all of the servers will always be able to talk to each other, then you can use Transactional Replication. Transactional replication shouldn't be used for failover. Why not? Well, if one of the servers involved in the replication becomes disabled because of a hardware issue (It's never a software issue, after all, Windows 2000 never crashes, right?) then there won't be any writes allowed in the data involved in replication even on the server that is still online. A good example of a system that would work well with transactional replication is an accounting system that is used to generate a lot of reports with minimal updates. Then the reporting load could be balanced across the two boxes and the data could be guaranteed consistent.

Merge replication is great for doing things like load balancing for geographically redundant sites. For example, if you have an Internet order taking system, having multiple SQL Servers avaiable to replicate those transactions around is a good idea in case there is a fibre cut or some such nonsense. Then when full service is restored the servers will probably come back online with no issues except for a manual conflicts to resolve. Since it is an order taking system, chances are pretty good that the only conflicts will be related to orders that were being changed when the server failed.