Skip Navigation LinksHome > Categories > Code from a Category

How to Choose a replication type

User Name: codelecturer
Name: Mike Chauhan
Contact Me:
Home Page:
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 1264
Add Date: 11/18/2013
In this article we will learn about how to choose a replication type.
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 available 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.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.