SQL Server Performance

SQL Mirror Failover Problem

Discussion in 'SQL Server 2005 Database Mirroring' started by kevinngcc, May 19, 2006.

  1. kevinngcc New Member

    I test to use mirroring in SQL and it is very great. However, I consider my application would only point to one sever name. If the database failover to another SQL Server, my application will not point to another SQL Server automatically.

    So, would I must use Windows Server 2003 Enterprise with Cluster with SQL Server 2005 Standard Version? So, I can set my application to point to the cluster virtual server name. For failover, mirroring and cluster service can failover the database to another SQL Server. Am I correct?


    Thx
  2. satya Moderator

    Yes, but why not test & choose a tweak at application side to change the servernames when the failover is happened. I believe with a controlled manner tasks this can be achieved to avoid shambles of clustering setup in this case.

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

    Thx for your suggestion

    Since my application must point to one servername for database access, how can I change the servernames automatically when the failover is happened?

    thx
  4. satya Moderator

    Is it an internal developed application or a third party one?

    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.
  5. kevinngcc New Member

    internal developed application

    thx

    so, do I need to setup a windows cluster
    the windows cluster name points to the active mirror server
    then, my application can point to the windows cluster name, so it also points to the active mirror server.

    thx
  6. satya Moderator

  7. jerryhung New Member

    I'm new to this, so any help is appreciated

    We are considering setting up SQL 2005 Mirroring, but then obviously the mirroring is at the DB level and not SERVER level.

    Say if my setup is
    ----------------------------
    Application A
    Server P (primary)
    Server M (mirror)
    Server W (witness)

    Since A kinda hardcodes the server P in an ini, what can we do to make sure A can connect to M when P fails?

    1. Manually change .ini?

    2. Use Windows cluster <- not sure how this works really

    3. Client-side redirect using SQL Native Client and ADO.Net
    BUT our application is built in Sybase PowerBuilder, I don't think it's really an option

    4. Setup server (could be Virtual) to load balance/gateway to direct which SQL Server to use

    None of them seem really easy or transparent @@
    Thanks!
  8. satya Moderator

    If the application needs complete high-availbility on the terms of server then Clustering is best one, if not Database Mirroring is better one.

    In any case you must test the application to take care of server name changes when the failover is occurred, both clustering and mirroring is fast enough and seamless for application to find.
    For load balancing I would prefer from Operating system to take care.

    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.

Share This Page