July 19, 2005 at 3:05 pm
Hello all,
I'm investigating log shipping as a possible solution to offload reporting to a different server, and I had a question. What happens when a restore is scheduled to occur on the destination database, but a user is accessing the destination database with a report (or adhoc query)? Does the restore fail, wait for the user to disconnect and then proceed, or proceed with the restore without problems?
Thanks for your help!
July 19, 2005 at 3:18 pm
Yes the restore job will fail, hanging up all other restore jobs.
A destination database in log shipping cannot have any open connections during a restore, So attaching an application to this db is not a good idea.
July 20, 2005 at 8:11 am
Ray, thanks for the info. I wonder why BOL says that log shipping provides a way to offload query processing to a read-only database? I guess they assume you would be doing the restores during off hours when no one would be accessing the database.
July 20, 2005 at 10:49 am
Not sure why it says that in Books Online, in my opinion Log shipping is not a good solution for moving data to a Reporting database. It is best used for warm standby failover solution.
There are other solutions that work better, Ie DTS, Repliction (Transactional, or Snapshot). T-Sql Scripts/Stored procedures managed by sql agent.
July 20, 2005 at 12:41 pm
We have a pretty hefty db (over 900GB) and we use logshipping for the reporting server. Early in the morning, evening and night we use it for the logshipping; and morning and afternoon for the reports. So at night we have no users accessing the system.
July 20, 2005 at 12:58 pm
I unfortunately don't have that luxury since our report server will be used during all hours of the day. I've decided just to use transactional replication instead. I've used it before in similar situations, but I was hoping to use log shipping since it looked easier to implement and maintain.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply