locked
Testing a DB failover RRS feed

  • Question

  • I have a DB mirroring session set up, but I suspect there may be a problem with the mirror. I want to try to initiate a failover, but its  a production database, so im reluctant to do so as Ive never done this before.

    If the failover doesnt work, will it stay connected to the Principal, or will it take the whole thing offline?

     

    Tuesday, April 27, 2010 11:05 AM

Answers

  • From the query results your mirror look fine.  However I would plan a test very soon as you ahve not yet.

    I have had similar problems with database mirror monitor while reading SQL Server 2005 mirrors from the 2008 version.  When in doubt, querying the DMVs and system tables is your best bet in these cases.  Your Mirror status in monitor of "not connected to server" may be simply a credentials issue or cache issue.  Have you tried removing and re-registering the mirror server?


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    • Marked as answer by Zoe.Ohara Tuesday, April 27, 2010 2:04 PM
    Tuesday, April 27, 2010 1:50 PM

All replies

  • First question is, why do you think there is a problem with the mirror?

    It is best practice to test your HA at a minimum of monthly.  Having it in place doesn't help if you are not confident in the abilities of it to act when it is needed. 

    To answer your question directly; No, if you issue a partner change to fail the mirror to the secondary, the principal will become the mirror and inaccessible.  Now that has some holes depending on if your mirroring session is having problems.  In that case I highly recommend providing all of the details to us before you issue the partner change.  A few things to provide...

    What type of mirroring are you using

    what is the problem you are seeing

    are they in sync

    are they local or remote

    what is your recovery plan if the mirror fails and you lose both databases

    is a cluster part of the landscape

    provide the landscape exactly as it is.  principal, mirror (witness?)


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    Tuesday, April 27, 2010 12:07 PM
  • ok thats for the help, guess I was a wee bit skimpy on the details.

    I have 2 servers (no cluster), each with three databases on them (all mirrored with no witness).

    All instances are 2005. when I issue the following statment on the mirror

     

    Select D.name,
    D.state_desc As DatabaseState,
    DM.mirroring_state_desc as MirroringState,
    DM.mirroring_role_desc As MirroringRole
    From sys.databases D
    Inner Join sys.database_mirroring DM On DM.database_id = D.database_id
    Where DM.mirroring_guid is not null

     

     

    I get these results

    Student RESTORING SYNCHRONIZED MIRROR
    Geneva  RESTORING SYNCHRONIZED MIRROR
    Alaman   RESTORING SYNCHRONIZED MIRROR

    Which looks like everything is ok (I think!). When I right click on the databases in SSMS and do Tasks > Mirror, it tells me under Status 'Synchronized: the databases are fully synchronized'

    Again - everything looks fine.

    But when I connect remotely from mt workstation through SSMS (2008 version) and launch the database Mirroring Monitor - in the status box it gives me the following values

    Server Instance    Current Role                         Mirroring State               Witness Connection

    Server(Principal)   Principal                               Syncronished                 

    Server(Mirror)      Not Connected to Server(M)   Not Connected to Server(M)    Not Connected to Server(M)  

     

    This is only for the GENEVA database - not the other 2. this 'Not connected to Server' is what makes me suspect the mirroring.  I hope this is clear.

    Thanks for your help

     

    Oh I nearly forgot - the recovery plan is to use differential backups - we take them every hour. I know its not ideal, but I guess they thought a DB mirror and regular backups would suffice.

    Cheers,

     

    Zoe

     

     

    Tuesday, April 27, 2010 1:13 PM
  • From the query results your mirror look fine.  However I would plan a test very soon as you ahve not yet.

    I have had similar problems with database mirror monitor while reading SQL Server 2005 mirrors from the 2008 version.  When in doubt, querying the DMVs and system tables is your best bet in these cases.  Your Mirror status in monitor of "not connected to server" may be simply a credentials issue or cache issue.  Have you tried removing and re-registering the mirror server?


    Ted Krueger Blog on lessthandot.com @onpnt on twitter
    • Marked as answer by Zoe.Ohara Tuesday, April 27, 2010 2:04 PM
    Tuesday, April 27, 2010 1:50 PM
  • Yes I have done - It initially brings it up as 'syncronised' but after a couple minutes I get the same 'not connected to server' issue.

    Thanks for your help!

     

    Tuesday, April 27, 2010 2:03 PM