SQL Server Performance

Log Shipping Problem, Out of Sync

Discussion in 'SQL Server Log Shipping' started by LS007, Aug 21, 2007.

  1. LS007 New Member

    to all SQL 2000 Gurus, please kindly help...
    do you think below scenario will be out of sync ?
    1.setup log shipping, succeed.
    2.create transaction-log backup (run every 15minutes) (with no_truncate) using database maintenance plan
    3.backup transaction log (every 1 hour) using third party software.
    if yes could you please explain why, or point me to the link where i can get technical explanation regarding this LSN stuff.
    thank you so much
  2. Netic New Member

    The best practice is to use only one of the tools.
    Best Regards.
  3. LS007 New Member

    i need step 2, since the SAP apps need it
  4. satya Moderator

    Step 2 created the plan with DB maintenance plan and performing backup that will pickup available LSNs and also on step 3 you are doing third party tool backup, why.
    As suggested better to use only step 2 or 3 in thsi case.
  5. LS007 New Member

    let me try to rephrase
    i need DRC(data recovery center) ,i think log shipping will be suitable for this purpose.
    so i set up log shipping.
    on the other hand the SAP on primary server need the transaction log, incase i need to rollback to somepoint. and to make sure i have the backup for the primary server
    so i set up backup trx-log scheduled Job
    i also run third party backup tool to backup the database
    this is just for redundancy
    can i do all this onto my database as the whole package without causing the LS to out of sync
  6. satya Moderator

    The LSN mismatch is obvious due to 2 processes performing backups where your backups are invalidated.
    Either you have to control this using NATIVE SQL processes or use that third party tools to perform this action.
  7. ghemant Moderator

    As said by Satya it is obvious that SS will report LSN mismatch as two separate job is performing t-log backup, why don't you retain the same copy of t-log backup in your repositary from LS job.
  8. LS007 New Member

    Thank you Satya and all of you for taking your time responding
    one more question
    since this LS run on Win2000 server, can i assign a drive letter to a tapedrive, to T: and also share it on network, as \primary-server ape
    i just wonder if this is possible
    i would try to change the parameter of the LS, to backup the trx-log to T:
    maybe this will solve my problem
    best regards
  9. satya Moderator

    Bear in mind the log shipping will have severe performance affect in perform backup on that tape drive, better to perform locally and then use relevant application to backup to tape.
  10. LS007 New Member

    lets say the log shipping backup the TRN file in c:ackup folder , and there will be a lot of TRN files in this directory
    next i backup all these TRN files into tape drive, regulary/scheduled, so i will have my tape with TRN1, TRN2, TRN3 and so on until TRN100 in sometime
    then lets say my primary server fails, so i'm gonna take my tape to doing the restore on new server , lets say Server3
    1.I restore the full backup database on server3
    2.Then restore the log files, from my tape in form of TRN1, TRN2 .... TRN100 files.
    my question is , when i do restore from Enterprise Manager in server3, i dont get the history of this Trans log backup,
    in other words i must do the restore as many as the TRN files
    if i got 100 TRN, i have to do 100 times restore into Server3, which is painless offcourse
    the enterprise manager in server3 didnt now/has about the history of trx backup
    plz advice on this matter, am i missing something here
    how can i get the history of this backup process and put into server3, so i could restore the transaction log in just one click
    Thank you
  11. satya Moderator

    The method you mentioned is correct for point in time recovery where you write to a different log file every time, if not you could use the same tlog backup file without using WITH INIT clause. This way you can have one file to restore after the complete database backup, but bear in mind both multiple log backups & single log backup file will have severe affect if any of the file or track is corrupted.
    For the sake of sequence of tlog backups at your end you could use APPEND clause for the same file and perform FULL DATABASE every night or atleast once in 2 days in order to keep up the transaction log files usage in case of restore recovery.

Share This Page