SQL Server Performance

DB Mirroring, Load Balancing, and Fault Tolerant

Discussion in 'SQL Server 2005 Database Mirroring' started by garylou, Sep 19, 2006.

  1. garylou New Member

    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
  2. bradmcgehee New Member

    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
  3. garylou New Member

    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


    quote:Originally posted by bradmcgehee

    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
  4. satya Moderator

  5. bradmcgehee New Member

    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
  6. garylou New Member

    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.



    quote:Originally posted by bradmcgehee

    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

Share This Page