Replication is still a major component of SQL Server today even with Availability Groups as an alternative to certain scenarios. Availability Groups does not allow you to have more than one writable version of the data at any given time. Replication is the only native component or feature in SQL Server that will allow you to have multiple writable databases that maintain data in sync to any near-real-time degree.

Once you have determined that you need a solution that provides multiple writable databases to scale out the workload, your next step is to figure out which solution is right for you. This decision is critical as it will drive everything you do in the implementation. Here is some assistance to help you choose the right replication topology for your scenario. I have authored a new whitepaper published by DB Best Technologies on Deciding Between Merge and Peer-to-Peer Replication with SQL Server.

Merge replication has been part of SQL Server since version SQL Server 7.0, released in 1998. Peer-to-Peer replication (P2P) is a newer topology first available in SQL Server 2008. Peer-to-Peer was positioned as the preferred replacement for transactional replication with updatable subscribers which was deprecated in SQL Server 2008. Many people consider P2P to also be a replacement for merge replication, and in some cases they are right.

Merge and P2P replication can both be used to solve a lot of the same problems, but the implementations are vastly different. The real deciding factor between the two is often more about operational concerns than about the problems they are trying to solve. If the only concern was that you needed data to be updatable on multiple servers and kept in sync, either topology fits the bill.

The things you should weigh in your decision about which topology to use include performance, maintainability, and operational overhead. Cost may also be a determining factor in your decision.

Download the whitepaper to continue reading.

^*Reposted with permission from SQLSoldier.com.