SQL Server Performance Forum – Threads Archive

DB Mirroring, Load Balancing, and Fault Tolerant

Hi All, We are going to upgrade to SQL 2005 in the next couple months. One of the the requirements is that we need to load balance the workload, and to be fault tolerant. We have internal applications runing against the SQL server, and public users running against the same database through web browser. We are thinking to implement DB mirroring and configure internal users to run against SQLSERVER1(internal), and web users to run against SQLSERVER2(external). Data update and insert will be in sync through the mirroring. However, there will be no fault tolerant. If SQLSERVER1(internal) offline, we will have to reconfigure the ODBC connection with client stations. If SQLSERVER2(external) offline, we will have to change the configartion file (web.config). My question:
1. Is the above solution will work for load balancing?
2. What is the best practice for Load Balancing and Fault Tolerant solution? Thanks. Gary

---

A mirrored database cannot be directly accessed by an application. It’s designed for fault tolerance. If you have Enterprise Edition of 2005, you do have the ability to do a database snapshot of a mirrored database which may or may not meet your needs. If you need load balancing, consider 2005’s new point to point replication feature. It’s not a perfect solution to load balancing, but it can work depending on your circumstances. If you want an ideal fault tolerant solution (lots of money), run two sets of clusters, with one having the active mirror and the other cluster (at a remote location) running the backup mirror database. There are no perfect load balancing options with SQL Server, most have to be custom-created, part SQL Server and part custom code, but the point to point replication comes close. —————————–
Brad M. McGehee, SQL Server MVP

---

Hi Brad, I am interested in the Peer-to-Peer (Point to Point) replication you suggest. Now I have more questions to ask. 1. Does Peer-to-Peer Replication handle record update conflicts automatically?
2. Can Peer-to-Peer Replication be a fail over solution as well? -Gary

---

Refer tohttp://www.microsoft.com/technet/prodtechnol/sql/2005/p2ptranrepl.mspx articles about PTP information and you can find more from SQL 2k5 Books online. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

---

Peer-to-peer does not work for fault tolerance, and the article that Satya suggests above talks about update conflicts. Wouldn’t it be great if there were one technology to handle all these needs? Unfortunately, we still have to piece meal things together to get everything we need done. —————————–
Brad M. McGehee, SQL Server MVP

---

Thanks for all the replies. Merge Replication is slower. We have two servers that are connected in a local LAN with 1 gbs speed. What is the latency in second for merge replication to replicate data between two servers? Many thanks.

---

]]>