August 3, 2010 at 8:40 pm
I am setting up LogShipping with the secondary DB being used as a Read-Only database.
There will be ad-hoc sql jobs running on this database througout the day, maybe every hour or two hours for maybe 5 or 10 minutes each.
I have set shipping so connections are killed when logs are being restored. This of course will stop any jobs that may be running.
My question is what is the best way to ensure Log Shipping is not stopped longer that necessary and that ad-hoc jobs are not affected?
I thought of removing the "kill connections before restoring option" but then in testing found that is a user had left an SSMS connection open then no logs would ever restore.
I am thinking the best way is to put a job step at the beginning of the ad-hoc job to stop and disable the restore log job only, do any processing and then have another step to enable the job again.
This should mean when the restore log runs at the next scheduled time it will restore any logs not yet restored.
Does this sound right?
Thanks
August 4, 2010 at 12:34 am
AFAIK in log shipping the database will be in restoring state and you wont be able to access the DB
August 4, 2010 at 1:39 am
it is definitely possible to access the DB, it can be in a Restoring state or a Read Only state, the catch of cause is the exclusive acccess needed by the Restore job on the Secondary database.
August 4, 2010 at 7:59 am
Yeah, as long as it doesn't take a long time (like a few days) and the other jobs start deleting the log backups, that should work. Log backups will queue up and once you run the restore job again later it will start at the top and apply them all.
And yes, if you do the inital DB restore in STANDBY instead of NORECOVERY log shipping can be read only.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply