Post reply

Sync with Backup : Transaction replication.

  • February 11, 2009 at 8:44 pm

    #199806

    Hi

    I am implementing transaction replication using the 'sync with backup' option so that I can still have point-in-time recovery available while scaling out the application to a separate reporting server.

    I understand that by setting this option the changes to the publishing database will only be propogated to the distribution DB once the publishing DB has been backed up.

    My question is what happens when the transaction log backup occurs between the times when the Log Reader Agent examines the transaction log for the publishing DB. Will those transaction since the last run of the Log Reader and the transaction log backup be lost or does the initiation of the backup cause the Log Reader to flush the trn log out prior to the backup occurring?

    Thanks in advance for you assistance.

    Matt

  • February 13, 2009 at 8:35 am

    #943672

    matt.ma (2/11/2009)

    Hi

    I am implementing transaction replication using the 'sync with backup' option so that I can still have point-in-time recovery available while scaling out the application to a separate reporting server.

    I understand that by setting this option the changes to the publishing database will only be propogated to the distribution DB once the publishing DB has been backed up.

    My question is what happens when the transaction log backup occurs between the times when the Log Reader Agent examines the transaction log for the publishing DB. Will those transaction since the last run of the Log Reader and the transaction log backup be lost or does the initiation of the backup cause the Log Reader to flush the trn log out prior to the backup occurring?

    Thanks in advance for you assistance.

    Matt

    I had the same question and with many many tries I figured that it was all about "when" you run sp_addsubscription !

    After you run that sp your Transaction log won get cleared by "backup log". In that case your LastLSN Number from the Full backup will be the starting point for the initialization job.

    If you run backup log "between" sp_addpublication and sp_addsubscription you could get into problems but sp_addsubscription will gracefully fail!

    Hope it helps.


    * Noel

Viewing 2 posts - 1 through 1 (of 1 total)

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