locked
Transaction Log Shipping Question RRS feed

  • Question

  • Dear All,

    If possible can someone please advise how I can get round a Transaction Log Shipping Issue.

    I have third party SQL server that generates a full bak file of all databases at midnight and a transaction log file every hour. As such, since it is a third party application I cannot set up TLS on that database even although I have sysadmin rights to the SQL Server. If something was to go wrong they could blame me and may not provide support.

    Can I set up a ‘form’ of TLS where I schedule the trn files to be ‘imported’ from the folder on the original ‘master’ server into the 2<sup>nd</sup> SQL Server (obviously the names of the files will change) which I can change as I see fit?

    The third party do not provide a DR solution for their application.

    We are using SQL Server 2008 R2 on a virtualised environment.

    Cheers.

    Alastair

    Friday, June 5, 2015 11:25 AM

Answers

  • Stan210,

    Thanks again for the quick reply. How would the 2nd sever know the name of the transaction file to import and in what order? I presume one file per folder at an interval and it just imports the trn file of any name or is SQL 'intelligent' to know that this was not the next trn file in the sequence?

    One file generated is called ABC_Members_backup_2015_06_04_160001_5252057 for yesterday's 4pm trn file.

    Thanks again for your continued support.

    Alastair

    Alastair - this is where the loghshipping comes handy because it takes care of all those things does provide some fault tolerance.

    agian, all that could be mimiced, - heres the thought - on our master MSDB database, it will have report of all the backup information, inculding time, physical file location, you can use that info to get which backup you need to copy...

    so, the ideaa would be on your destination server - check the restore report table under msdb and see which ones have been restored and now, go to main server and check for the backup tables under msdb - see the list of backup taken after the latest restored log backup and copy all of those, now, restore them one by one, if you have more than one, you can import this info to a temp table, sorted by datetime, the log backup was taken and apply the latest oldest one in the chain first until you apply all of them.. 

     

    Hope it Helps!!

    Friday, June 5, 2015 12:20 PM

All replies

  • yes, you can do that. the only thing you will not get is the automatic logshipping monitor.. which again, you can write custom scripts to check the, if you need.

    log shipping applies backups on the database one after the other on regular basis: so, you can use your sql server agent to create these jobs..

    on high level, it would look like this. assuming you have backups ready in one locations

    (intial set up would be restoring the full backup and othe trxn to bring the database upto the current log backup level and leave the database either in recovering state or read only state). if you want users to connect to this database and query, make it read only state.

    now, in your sql server job agent -

    1. make sure you copy the files to the new server path,technically, you would not need this if your sql service account can access the files directly.

    2. and in second step, apply the log file to the database either in norecovery or read only mode, if you are using read only mode,  you need to kick all users out before you apply the backup.

    3. also, you can use msdb.dbo.restorereport to track which log backups were applied, which ones needs to be applied.

    4. you can use restore to check the lsn of the current log backup...


    Hope it Helps!!

    Friday, June 5, 2015 11:36 AM
  • You can very well do that. You just need to copy the transaction logs every hour and then move it to the DR server where you would want the logs to be restored. Schedule a job to restore these transaction logs every hour say 15 minutes after the copy is finished or just make the copy job to invoke the tlog restore job. This way you can have a DR environment without effecting anything in the production.

    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Friday, June 5, 2015 11:40 AM
  • Stan210,

    Thanks again for the quick reply. How would the 2nd sever know the name of the transaction file to import and in what order? I presume one file per folder at an interval and it just imports the trn file of any name or is SQL 'intelligent' to know that this was not the next trn file in the sequence?

    One file generated is called ABC_Members_backup_2015_06_04_160001_5252057 for yesterday's 4pm trn file.

    Thanks again for your continued support.

    Alastair

    Friday, June 5, 2015 12:05 PM
  • Ashwin,

    Thanks again. Can the  job do both parts of the process... copy the file and restore it as long as the agent had access to the folder where the file exists. I presume this is the account under which the agent runs?

    I am sorry if these questions seems very basic for such a seasoned professional as yourself.

    Cheers.

    Alastair

    Friday, June 5, 2015 12:08 PM
  • Stan210,

    Thanks again for the quick reply. How would the 2nd sever know the name of the transaction file to import and in what order? I presume one file per folder at an interval and it just imports the trn file of any name or is SQL 'intelligent' to know that this was not the next trn file in the sequence?

    One file generated is called ABC_Members_backup_2015_06_04_160001_5252057 for yesterday's 4pm trn file.

    Thanks again for your continued support.

    Alastair

    Alastair - this is where the loghshipping comes handy because it takes care of all those things does provide some fault tolerance.

    agian, all that could be mimiced, - heres the thought - on our master MSDB database, it will have report of all the backup information, inculding time, physical file location, you can use that info to get which backup you need to copy...

    so, the ideaa would be on your destination server - check the restore report table under msdb and see which ones have been restored and now, go to main server and check for the backup tables under msdb - see the list of backup taken after the latest restored log backup and copy all of those, now, restore them one by one, if you have more than one, you can import this info to a temp table, sorted by datetime, the log backup was taken and apply the latest oldest one in the chain first until you apply all of them.. 

     

    Hope it Helps!!

    Friday, June 5, 2015 12:20 PM