none
recovery model RRS feed

  • Question

  • Hi all,.

    why database mirroring not support bulk-logged recovery model and why log shipping support

    bulk-logged recovery mode ..?

    please give me simple example .

    Tuesday, June 4, 2013 9:32 AM

Answers

  • The BULK_LOGGED recovery model has the same transaction log truncation semantics as the FULL recovery model but allows some operations to be partially logged, which is called being minimally logged. Examples are an index rebuild and some bulk-load operations—in the FULL recovery model the entire operation is logged,

    So if transaction is not completely logged how it will be replyaed on Mirror server so you wont be able to generate same transaction on Mirror which will lead to inconsistency,and as you are aware mirror server tries to sync with pricipal by replaying logs...so this will lead to inconsistent data

    So bulk logged is not allowed in Mirroring..

    In database mirroring, only committed transaction data is sent from the principal to the mirror server; rolled-back transaction data is not sent to the mirror server. In log shipping, both committed and rolled-back transaction data are backed up.


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


    Tuesday, June 4, 2013 11:40 AM
    Moderator

All replies

  • Please see the link 

    http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/ee05954e-0934-4305-8936-b9226e231d06 

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Tuesday, June 4, 2013 9:44 AM
  • Hi all,.

    why database mirroring not support bulk-logged recovery model and why log shipping support

    bulk-logged recovery mode ..?

    please give me simple example .


    As far as I know - Log Shipping does support the Bulk_logged recovery model - As there are no bulk operations after the last log backup
    Tuesday, June 4, 2013 11:30 AM
  • The BULK_LOGGED recovery model has the same transaction log truncation semantics as the FULL recovery model but allows some operations to be partially logged, which is called being minimally logged. Examples are an index rebuild and some bulk-load operations—in the FULL recovery model the entire operation is logged,

    So if transaction is not completely logged how it will be replyaed on Mirror server so you wont be able to generate same transaction on Mirror which will lead to inconsistency,and as you are aware mirror server tries to sync with pricipal by replaying logs...so this will lead to inconsistent data

    So bulk logged is not allowed in Mirroring..

    In database mirroring, only committed transaction data is sent from the principal to the mirror server; rolled-back transaction data is not sent to the mirror server. In log shipping, both committed and rolled-back transaction data are backed up.


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


    Tuesday, June 4, 2013 11:40 AM
    Moderator