April 29, 2009 at 11:55 am
I have configured log shipping as below
1.Take the full backup and restored in secondary with norecovery option.
2.Used log shipping configuration wizard and scheduled log backups every 15 mins to a network folder(working fine)
3.Scheduled copy job to run every 15 mins, copies tlogs to secondary server from network folder (working fine)
4.Scheduled restore job every 15 mins and selected the option 'delay restoring backups at least' to 30 mins and selected alert if no restore occurs with in as 90 mins
5.Lets say I configured all above steps using wizard at 9:00am,
then every 15 mins, backup, copy and restore jobs running at the same time 9:00, 9:15, 9:30 etc.
BUT the restore job ran succesfully from 9:00am to 10:30am WITHOUT RESTORING THE LOG BACKUPS. It saying could not fing log back file to restore eventhough log backup file exist on the server
after 90 mins, its giving the error in error log as:
Error: 14421, Severity: 16, State: 1.
2009-04-29 10:24:00.63 spid58 The log shipping secondary database SQLDBA\INS1.abc has restore threshold of 90 minutes and is out of sync. No restore was performed for 91 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
Could you please correct me where Iam going wrong?
is this the correct way to configure log shipping?
April 29, 2009 at 12:08 pm
check service permissions on the share.
* Noel
April 29, 2009 at 3:14 pm
we are using the same service account on all the 3 servers.
Now I let the wizard to do all the steps starting from full backup, copy, restore in secondary server and thereafter restoring the tlogs. Now its working fine and database status showing as databasename(standby/read-only).
Whythe database showing both standby/read-only?
when I configured by first taking the full backup and restoring it using norecovery manually its showing readonly.
is that fine if the database showing both modes standby/read-only?
please advice me
April 29, 2009 at 3:32 pm
please post the exact error from the restore job.
Is it claiming LSN is out of sequence? Are all the log backups in the share AFTER the full backup you used to initialise log shipping?
---------------------------------------------------------------------
April 29, 2009 at 4:43 pm
Why the database showing both standby/read-only?
If it is in stand-by, it has to be in read-only mode so that next Transaction log backup can be applied.
And that is the expected status while it is not in "restoring" mode.
* Noel
April 29, 2009 at 5:25 pm
We have any good White paper/Article on Configuring Log Shipping In SQL Server 2005?
April 29, 2009 at 11:03 pm
You configured it well.No issues.
Log shipping has two options one is stand-by and the other is norecovery mode for the secondary database.
When you select the first option,the database will be in stand-by/readonly mode and the DB will look in Grey colour in the object browser.In this mode users can query the database and cannot do any write operations on it.After every 15 min(in your case) the users will be disconnected from the database and the restore happens.This option is used for reporting purposes generally.
The second option is the norecovery mode where the database will always be in restoring mode and no users can connect to this DB.You you want the database for fail-over purpose or availability then you go for this option.If the primary server fails,backup the tail log on the primary and restore it on the secondary with recovery option and the secondary will be in online mode.
As you said that your DB is in standby/readonly mode ,no problem with it.Keep checking the restore job and also the log shipping system tables in the msdb database for the status of your log shipping.
select * from log_shipping_secondary in msdb will give you the last restored logbackup file.If any problem comes then query
select * from log_shipping_monitor_history_detail and
select * from log_shipping_monitor_error_detail
you'll get the detailed description of the error.
April 30, 2009 at 3:34 am
AAkula (4/29/2009)
We have any good White paper/Article on Configuring Log Shipping In SQL Server 2005?
google log shipping.
You say you restored the database in norecovery mode but it is now in standby\read only. that means you must have had successful log restores to put it in that mode, so sounds as if it is working.
Any successful log restores will be recorded in the errorlog as well as the msdb log shipping tables.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply