locked
Database Mirroring with automatic failover RRS feed

  • Question

  • Hello,

    I have SQL Server 2008 R2 database mirroring setup with witness. Below database connection string is used by the application

     

    jdbc:sqlserver://<Server_A>\MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>\MSSQLSERVER

     

    During some maintenance activity at the OS side , I tried to manually failover database from Server_A to Server_B. It took long time for failover  to Server_B. While failing over the Mirrored_DB database goes in In Recovery status and application is not able to connect to database. I waited for long time more than 40 mins for database to come online on Server_B but it did not work.So I have recovered database on Server_B using RESTORE DATABASE Mirrored_DB WITH RECOVERY.

     

    After database was recovered on Server_B application was not able to connect to database with error as "The database <Mirrored_DB> on server Server_B\MSSQLSERVER is not configured for database mirroring".

     

    My Question is

    1. Why manual failover did not get back database on secondary server up
    2. why the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring

     jdbc:sqlserver://<Server_A>\MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>\MSSQLSERVER



    Sachin Jain

    Monday, June 22, 2015 10:44 AM

Answers

  • Why manual failover did not get back database on secondary server up
    Can you check the SQL Server error log on both nodes after the failover was initiated and check for errors? We'd only be speculating otherwise.
    By the way I find it helpful in a mirrored situation to have a 'TestMirror' database on the server. On a planned failover I move this first to see if there will be any obvious issues.

    why the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring
    This is by design and is an expected error I'm afraid. 
    If the mirror is contacted and is found to be not part of a mirroring session it can't trust that it's connected to the correct database (or a current version of it). It protects against situations where you may have decided to mirror to another server but have forgotten to update the connection string, or you recovered the database on that node sometime in the past and didn't re-establish the mirroring session - resulting in queries against old data, or intitial setup was incorrect - restored with recovery rather than norecovery (etc etc).

    Monday, June 22, 2015 11:56 AM
  • Did you monitor the redo and send queue for your first question it took lot of time as it has to redo lot of changes before it can bring database online

    For second question as already noted this is bydefault. You would have to change connection string

    Please also note that Zero downtime is myth during failover depending on amount of transaction which needs to be replayed it can take some time to bring mirror online. Run sp_readerrorlog on mirror and post it here do this on principal as well if possible


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Monday, June 22, 2015 3:06 PM

All replies

  • Why manual failover did not get back database on secondary server up
    Can you check the SQL Server error log on both nodes after the failover was initiated and check for errors? We'd only be speculating otherwise.
    By the way I find it helpful in a mirrored situation to have a 'TestMirror' database on the server. On a planned failover I move this first to see if there will be any obvious issues.

    why the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring
    This is by design and is an expected error I'm afraid. 
    If the mirror is contacted and is found to be not part of a mirroring session it can't trust that it's connected to the correct database (or a current version of it). It protects against situations where you may have decided to mirror to another server but have forgotten to update the connection string, or you recovered the database on that node sometime in the past and didn't re-establish the mirroring session - resulting in queries against old data, or intitial setup was incorrect - restored with recovery rather than norecovery (etc etc).

    Monday, June 22, 2015 11:56 AM
  • Did you monitor the redo and send queue for your first question it took lot of time as it has to redo lot of changes before it can bring database online

    For second question as already noted this is bydefault. You would have to change connection string

    Please also note that Zero downtime is myth during failover depending on amount of transaction which needs to be replayed it can take some time to bring mirror online. Run sp_readerrorlog on mirror and post it here do this on principal as well if possible


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Monday, June 22, 2015 3:06 PM