sql server Mirroring fail over scenarios & other faqs

  • Hi experts please answer these doughts. in sqlserver 2005/8R2

    1) If principle server down what willl happen to Mirror DB & Transactions

    2) If mirroring breaks what will happen to mirror server

    3) In BulkLogged can we take Point in time recovey?

    4) Suddely SP is getting slow how to trouble shoot?

    5) TempDb full how to trouble shoot?

    6) If log full in logshipping & mirroring how to trouble shoot ?

    Thanks in advance.

  • These all sound like interview / homework questions.

    If so what do you know about all of the topics you have detailed above.

  • jamessdba (9/2/2014)


    Hi experts please answer these doughts. in sqlserver 2005/8R2

    Interview questions? (btw, 'doubts')

    1) If principle server down what willl happen to Mirror DB & Transactions

    Depends on how the mirroring is configured. Read up on mirroring for the details, lots of good books

    2) If mirroring breaks what will happen to mirror server

    Depends on what you mean by 'breaks'. Read up on mirroring for the details, lots of good books

    3) In BulkLogged can we take Point in time recovey?

    Depends on the types of operations which have been run. Read up on recovery models, books online has a lot and there are a number of articles available

    4) Suddely SP is getting slow how to trouble shoot?

    Identify what changed, fix it

    5) TempDb full how to trouble shoot?

    Identify what's using TempDB, fix it.

    6) If log full in logshipping & mirroring how to trouble shoot ?

    Same way you would troubleshoot for cases where you don't have mirroring or log shipping. Again, lots and lots of good articles available.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • jamessdba (9/2/2014)


    1) If principle server down what willl happen to Mirror DB & Transactions

    Depends, if you have a witness and the session loses quorum with the mirror the principal will go offline. What mirror mode do you use, do you have a witness?

    jamessdba (9/2/2014)


    2) If mirroring breaks what will happen to mirror server

    Again, it depends but usually the session will be suspended

    jamessdba (9/2/2014)


    3) In BulkLogged can we take Point in time recovey?

    You don't, they're not supported

    jamessdba (9/2/2014)


    4) Suddely SP is getting slow how to trouble shoot?

    ??

    jamessdba (9/2/2014)


    5) TempDb full how to trouble shoot?

    ??

    jamessdba (9/2/2014)


    6) If log full in logshipping & mirroring how to trouble shoot

    Again, it depends but first thing would be to check that the t log backups have been successful

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle (9/2/2014)


    jamessdba (9/2/2014)


    3) In BulkLogged can we take Point in time recovey?

    You don't, they're not supported

    You can. It depends on whether or not there's been a minimally logged operation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (9/2/2014)


    Perry Whittle (9/2/2014)


    jamessdba (9/2/2014)


    3) In BulkLogged can we take Point in time recovey?

    You don't, they're not supported

    You can. It depends on whether or not there's been a minimally logged operation.

    True, but the whole point of putting a database into bulk logged recovery model is to minimally log a bulk logged operation. from my point of view its safer just to say no, also detailed here

    Books Online


    Can recover to the end of any backup. Point-in-time recovery is not supported.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Books online is again wrong. It is supported, providing there are no minimally logged operations in the log interval containing the time that the DB is being restored to, as stated elsewhere

    http://msdn.microsoft.com/en-us/library/ms179451.aspx

    This topic describes how to restore a database to a point in time in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL. This topic is relevant only for SQL Server databases that use the full or bulk-logged recovery models.

    Important note

    Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. The database must be recovered to the end of the transaction log backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • that's not surprising

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply