locked
SQL 2005 Mirror Auto-Failover when principal reboots but does not fail back when up again RRS feed

  • Question

  • We have two 2005 SQL ENT Servers with a mirror DB and 2005 SQL Std witness.

    If we reboot the principal server (server 1) the witness picks up on this and switches the roles over so that the mirror server (server 2) becomes the principal - all good.

    However when the principal (server 1) comes back online the roles are not switched back so i am left with server 1 as the mirror and server 2 as the principal - bad

    I can fail back over but this is a manual thing. So (as what has happened in the past) when updates are applied over a weekend the server reboots and becomes unusable by software apps on a monday morning until i have to manually fail it back over.

    Is this by design or should Server 1 automatically fail back to principal once it has restarted??

    Thanks, Alex.

    Wednesday, November 10, 2010 10:25 PM

Answers

  • There is no automatic failback provided by SQL Server mirroring. You would have to set something up manually, you just have make sure when you failback the new mirror (principle pre-failover) is synchronized. There is a post that shows a way of doing this using a job that checks the status and then failsback. There is no way in mirroring to specify a prefered node and therefore failback when it comes online...

    http://www.sqlservercentral.com/blogs/robert_davis/archive/2009/02/22/Will-the-Mirror-automatically-fail-back-to-the-original-Principal-when-it-comes-back-online.aspx

    What is your reason for wanting to failback or for failing over in the first place?

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    • Proposed as answer by PrinceLucifer Thursday, November 11, 2010 6:29 AM
    • Marked as answer by Alex Feng (SQL) Thursday, November 18, 2010 10:02 AM
    Wednesday, November 10, 2010 11:47 PM
  • Hi Alex,

    As other said, this is the by design behavior. However, if your software applications connect to the database that is being mirrored with ADO.NET or the SQL Native Client, your applications can take advantages of the drivers’ ability to automatically redirect connections when a database mirroring failover occurs.

    To do this, you must specify the initial principal server and database in the connection string, and optional the failover partner server. For instance, server A as principal, server B as mirror and AdventureWorks as the database:

    “Data Source = A; Failover Partner = B; Initial Catalog = AdventureWorks; Integrated Security = True;”

    Please note that if you don’t specify Failover Partner in the connection string, your applications may fail to make initial connection to the database if server A is no longer the principal (failed over from A to B). In case of this issue, I would recommend specifying Failover Partner in the connection string.

    For more information, please refer to Making the Initial Connection to a Database Mirroring Session (http://msdn.microsoft.com/en-us/library/ms366348.aspx).

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, November 11, 2010 6:37 AM

All replies

  • There is no automatic failback provided by SQL Server mirroring. You would have to set something up manually, you just have make sure when you failback the new mirror (principle pre-failover) is synchronized. There is a post that shows a way of doing this using a job that checks the status and then failsback. There is no way in mirroring to specify a prefered node and therefore failback when it comes online...

    http://www.sqlservercentral.com/blogs/robert_davis/archive/2009/02/22/Will-the-Mirror-automatically-fail-back-to-the-original-Principal-when-it-comes-back-online.aspx

    What is your reason for wanting to failback or for failing over in the first place?

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    • Proposed as answer by PrinceLucifer Thursday, November 11, 2010 6:29 AM
    • Marked as answer by Alex Feng (SQL) Thursday, November 18, 2010 10:02 AM
    Wednesday, November 10, 2010 11:47 PM
  • This is by design,

    Failing back will cause all client connections to be dropped again, so you probably don't want SQLServer to decide when is a good time to failback,

    It's much better if you pick a time when change won't affect too many users.


    Vishal Gajjar
    Thursday, November 11, 2010 2:36 AM
  • Hi Alex,

    As other said, this is the by design behavior. However, if your software applications connect to the database that is being mirrored with ADO.NET or the SQL Native Client, your applications can take advantages of the drivers’ ability to automatically redirect connections when a database mirroring failover occurs.

    To do this, you must specify the initial principal server and database in the connection string, and optional the failover partner server. For instance, server A as principal, server B as mirror and AdventureWorks as the database:

    “Data Source = A; Failover Partner = B; Initial Catalog = AdventureWorks; Integrated Security = True;”

    Please note that if you don’t specify Failover Partner in the connection string, your applications may fail to make initial connection to the database if server A is no longer the principal (failed over from A to B). In case of this issue, I would recommend specifying Failover Partner in the connection string.

    For more information, please refer to Making the Initial Connection to a Database Mirroring Session (http://msdn.microsoft.com/en-us/library/ms366348.aspx).

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Thursday, November 11, 2010 6:37 AM