December 2, 2010 at 9:41 am
Hi all,
i am trying to set up log shippping for the first time and have some questions:
We can only restore logs on the secondary node during 1am and 6am.
Also the primary and the secondary instances are in a clustered env., each instance located on each own server and each instance has separate mount point for transaction log backups.
The questions are:
1) Do i need to create a share folder on each instance for transaction log backup folders?
Can i make them cluster resource?
2) When i create log shipping using SSMS there is an option to schedule a job to copy logs.
What is the best and recommended frequency to copy files over? Should the job run every 1 minute?
i think the default is 15 minutes.
3) I think i will schedule the backup logs to run every 15 minutes but i am not sure what should i choose for the frequency to restore backup files. it probably depends on how long it takes to restore backups but if you can point me to the right direction that will be nice.
4) is there a tool available to setup monitoring for a log shipping?
Thank you.
December 2, 2010 at 10:06 am
1) Do i need to create a share folder on each instance for transaction log backup folders? yes
Can i make them cluster resource? never tried....not sure that you need to
2) When i create log shipping using SSMS there is an option to schedule a job to copy logs.
What is the best and recommended frequency to copy files over? Should the job run every 1 minute?
i think the default is 15 minutes.
---this basically comes down to how data can you afford to loose if you can afford to loose 15 then leave it.....other wise adjust as needed
3) I think i will schedule the backup logs to run every 15 minutes but i am not sure what should i choose for the frequency to restore backup files. it probably depends on how long it takes to restore backups but if you can point me to the right direction that will be nice.
--I dont worry about this to much....as long as the logs are there they can be updated via the job at anytime....so if you need to fail over at anytime you can just run the job and get it up to speed
4) is there a tool available to setup monitoring for a log shipping?
not really any tools....I generally set up database mail, set to send mail if the job fails. and another good one I use is to set up a job to run a script each morning hitting the sys table on the log shipping server. found out the last time the server has been updated and then send it to how ever is needed..then I know each morning that the log shipping server is up to date....I have had plenty of times that I havent done this and I had no idea the backu pserver was not up to date..
December 2, 2010 at 10:27 am
you set up a job and run the following each morning assuming that you have database mail already set up
DECLARE @PrintMessage nvarchar(50);
SET @PrintMessage = N'Here are your Database update results for '
+ RTRIM(CAST(GETDATE() AS nvarchar(30)))
+ N'.';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Email Account Profile',
@recipients = 'youremail@here.com;',
@query = 'use msdb;
select
DBRestored = destination_database_name,
RestoreDate = restore_date,
SourceDB = b.database_name,
SourceFile = physical_name,
BackupDate = backup_start_date
from RestoreHistory h
inner join BackupSet b
on h.backup_set_id = b.backup_set_id
inner join BackupFile f
on f.backup_set_id = b.backup_set_id
order by RestoreDate',
@subject = 'Update of last log ',
@body = @PrintMessage,
@attach_query_result_as_file = 1 ;
December 2, 2010 at 10:29 am
Thank you so much for your replies, i really appreciated!
December 7, 2010 at 2:12 pm
After testing for several days in dev (sql 2008 SP1 ) i decided to implement log shipping in production ( sql 2008 SP2).
The problem is that i don't see a .TUF file in production and for the first day it seems that the restore runs fine.
But i do see .TUF file in dev.
Is this is something that has been removed in SP2?
Second question i have is how the alert for the restore works exactly? let's say i do restore transaction logs only during 1am and 6am and i modifed the alert job to start at 2am with 45 mins threshold but still getting errors with latency starting at about 2:02 am.
Thanks
December 7, 2010 at 3:04 pm
i actually founded .tuf file
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply