locked
Will failover to another Mirror DB fail if using cluster? RRS feed

  • Question

  • Hi all,

    I got the answer that SQL Mirroring + Cluster is supported.
    http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/d104b76d-4ad5-48d1-91b5-0b8e6bad7b20

    I am wondering  failover problem highlighted.

    My scenario is simple, kindly correct me if I am wrong.

    SQL Cluster Active: SQL 1  Passive: SQL 2
    SQL Mirroring Active: sqlcluster   Pasive: SQL 3
     
    1. Primary Site (City A)   
    SQL Server cluster (SQL 1 and SQL 2), cluster name 'sqlcluster'
    The cluster 'sqlcluster' will have a mirror with SQL 3 in City B.

    2. DR Site (City B)
    Single SQL Server (SQL 3)
    Mirroring with City A SQL cluster  'sqlcluster'   
    if SQL 1 fail to respone, then cluster failover + Mirroring failover
    Cluster: SQL 1 -> SQL 2
    Mirroring: SQL 1 -> SQL 3 (Active)

    Actually SQL 3 should response SQL client request, which will cause problem,  actually SQL client points to 'sqlcluster', but SQL 2 is not active, if my understanding is right.  Should customize sth in source code or any action, in order to let SQL client point to SQL 3 when SQL 1 failover? How to do? Thanks.  

    Monday, July 16, 2012 9:10 AM

Answers

  • depending on which driver your sql client uses, you can use a "FailoverPartner" in your connection string, where you would specify SQL3 as Failoverpartner.

    More here:

    http://msdn.microsoft.com/en-us/library/5h52hef8.aspx

    this is also a good reference for connetionstrings :

    http://www.connectionstrings.com/

    • Proposed as answer by PrinceLucifer Monday, July 16, 2012 12:37 PM
    • Marked as answer by amber zhang Tuesday, July 24, 2012 1:25 AM
    Monday, July 16, 2012 10:12 AM
  • To accomplish this you need two things:

    1) Configure a Witness for your mirroring. Otherwise the connection will not failover.

    2) Add the FailoverPartner attribute to the connection string as M already pointed out. Most of the drivers support this nowadays, there are just some old legacy drivers around that don't.

    • Proposed as answer by MWagner1985 Monday, July 16, 2012 3:09 PM
    • Marked as answer by amber zhang Tuesday, July 24, 2012 1:25 AM
    Monday, July 16, 2012 12:37 PM

All replies

  • depending on which driver your sql client uses, you can use a "FailoverPartner" in your connection string, where you would specify SQL3 as Failoverpartner.

    More here:

    http://msdn.microsoft.com/en-us/library/5h52hef8.aspx

    this is also a good reference for connetionstrings :

    http://www.connectionstrings.com/

    • Proposed as answer by PrinceLucifer Monday, July 16, 2012 12:37 PM
    • Marked as answer by amber zhang Tuesday, July 24, 2012 1:25 AM
    Monday, July 16, 2012 10:12 AM
  • To accomplish this you need two things:

    1) Configure a Witness for your mirroring. Otherwise the connection will not failover.

    2) Add the FailoverPartner attribute to the connection string as M already pointed out. Most of the drivers support this nowadays, there are just some old legacy drivers around that don't.

    • Proposed as answer by MWagner1985 Monday, July 16, 2012 3:09 PM
    • Marked as answer by amber zhang Tuesday, July 24, 2012 1:25 AM
    Monday, July 16, 2012 12:37 PM