locked
Failover when disk is down RRS feed

  • Question

  • We are running on SQL Server 2008R2 ent. as a VM on an ESX.

    We are planning to get a flash disk device (intel 910) to connect to our ESX and have the mdf/ldf files of our database migrated to it.

    Since flash disk is not as as reliable as a SAN/NAS raid we wanted to have some kind of HA in place so we decided to try SQL's mirroring feature.

    We set up mirroring with async mode since we do not want the slow disk of the mirror server to be a bottleneck.

    Manual failover works fine when done through T-SQL (ALTER DATABASE database_name SET PARTNER FAILOVER) or through SMSS.

    But the whole point of this is to be able to relatively quickly fail (manual or automatic) to the mirror server when hardware disaster occurs.

    3 scenarios in mind:

    1. only the DB (mdf/ldf) is on the flash disk and the disk crashes

    2. all the VM is on the flash disk and the disk crashes

    3. either DB only or whole VM is on disk but there is a different issue (networking/ESX server)

    In none of those scenarios it apears that I can somehow take advantage of the mirror server.

    when I tested offlining the flash disk while only the DB was on it and trying to manually failover I get this message:

    Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
    Msg 5120, Level 16, State 101, Line 2
    Unable to open the physical file "G:\xxxx.MDF". Operating system error 3: "3(The system cannot find the path specified.)".
    File activation failure. The physical file name "G:\xxxx_log.ldf" may be incorrect.

    Which makes sense since the disk is down but as far as SQL server sees it there is no reason to failover

    I think the other 2 scarious are no different as I cannot failover when the SQL server itself is not available.

    It seems like I am missing something fundamental about this feature and how it should be used.

    How exactly is this HA?

    Any help would be greatly appreciated.

    Thanks! 


    • Edited by TZCS Monday, February 17, 2014 1:11 PM
    Monday, February 17, 2014 12:48 PM

Answers

  • Thank you for the links. I have read them but still did not find answer to my questions.

    1. Does it make sense to do async mode since the principal has faster storage than the mirrored one?

    2. In the case the disk on which the mdf is on is down but instance is still up, what should my actions be?

    Thanks.

    1. Yes you can do if you are OK with some data loss.Actually answer depends on RPO and RTO fast disk means transaction will be fast and if disk on which mirror resides is slow it will create a more lag netween principal and mirror if you are OK with it you can go.

    2. You mean to say database is down but instance is up .In this case first try to take tail log backup and see if you succeed if yes you need to manually break the mirroring take this log backup and restore it on mirror server  and bring it up.

    if tail log backup fails you have no option but to run force service allow data loss.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Tuesday, February 18, 2014 9:39 AM
    • Marked as answer by TZCS Tuesday, February 18, 2014 2:52 PM
    Tuesday, February 18, 2014 9:07 AM

All replies

  • Tried to do it forcibly when disk is down:

    ALTER DATABASE mcafeegsl SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    got this error:

    Msg 1455, Level 16, State 106, Line 1
    The database mirroring service cannot be forced for database "xxxx" because the database is not in the correct state to become the principal database.

    although the DB is not really accessible

    This is a screenshot from the management studio of the principal server. 

    You can see there are no tables there and there should be.

    For some reason thats not a good enough reason to be classified as unreachable.

    Monday, February 17, 2014 1:21 PM
  • Hello,

    You can only do force service allow data loss when principal has no connectiono with mirror database.If it still has it wont allow you do to and would flash error whichi you just got.

    I would like you to read below article

    http://technet.microsoft.com/en-us/library/ms189977(v=sql.105).aspx

    Reg your first question please read Async Mirroring

    http://technet.microsoft.com/en-us/library/ms187110(v=sql.105).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, February 17, 2014 1:24 PM
  • Thank you for the links. I have read them but still did not find answer to my questions.

    1. Does it make sense to do async mode since the principal has faster storage than the mirrored one?

    2. In the case the disk on which the mdf is on is down but instance is still up, what should my actions be?

    Thanks.

    Tuesday, February 18, 2014 8:27 AM
  • Thank you for the links. I have read them but still did not find answer to my questions.

    1. Does it make sense to do async mode since the principal has faster storage than the mirrored one?

    2. In the case the disk on which the mdf is on is down but instance is still up, what should my actions be?

    Thanks.

    1. Yes you can do if you are OK with some data loss.Actually answer depends on RPO and RTO fast disk means transaction will be fast and if disk on which mirror resides is slow it will create a more lag netween principal and mirror if you are OK with it you can go.

    2. You mean to say database is down but instance is up .In this case first try to take tail log backup and see if you succeed if yes you need to manually break the mirroring take this log backup and restore it on mirror server  and bring it up.

    if tail log backup fails you have no option but to run force service allow data loss.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Sofiya Li Tuesday, February 18, 2014 9:39 AM
    • Marked as answer by TZCS Tuesday, February 18, 2014 2:52 PM
    Tuesday, February 18, 2014 9:07 AM
  • Thanks Shanky.

    Tuesday, February 18, 2014 2:52 PM
  • Thanks Shanky.


    You are welcome buddy.Thnaks for Visitng MSDN forum

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, February 18, 2014 3:04 PM