September 19, 2008 at 1:36 pm
Hi,
I am log shipping from server A to B. Management told me to direct all employees in merchandising to query the database on B instead of A because it was causing performance issues with the web server. We use PRTG to monitor the web server and it was sending alerts during the night and it was blamed by the web developers on locking on server A. Anyway my problem is long running stored procedures are failing due to a conflict with the log shipping restore on server B. Is there a way around this problem?
Thanks.
September 20, 2008 at 5:00 am
I've not actually used log shipping out of the box, although I have used a version of log shipping which was written using VBScript so I guess it runs along the same theme.
When restoring the logs, the database becomes unreadable, hence your timeout. There is little you can actually do about this.
How big are the logs on average? How frequent are they? Could you possibly reduce the frequency to make the logs smaller? You'll still have the issue where the SP's will take an age.
Is replication a possibility? I chose replication at my company in the end due to the fact that the logs could get quite large and various teams were getting frustrated with the delays.
September 20, 2008 at 6:43 am
There are a few issues, and Clive has talked well about them.
The more often you run log backups, the smaller they are. However, to apply them, you need to interrupt users more often. If you do less log backups, then the logs are larger, so less frequent interruptions, but the restores take longer.
For what you are trying to do, log shipping isn't really helping since users have to disconnect. Instead replication might work better, though that's not really a DR solution. Replication allows you to copy over some data for users to read on the secondary system. You could replicate everything, but that becomes an issue as you try to alter schemas and do work on your database.
September 21, 2008 at 5:29 pm
Another good solution that was not mentioned before is to use database mirroring and a snapshot of the mirror. Depending on how current the data needs to be, this can be a good solution. If a daily snapshot is good enough for reporting, this is the route I like to go.
Even cooler was a recent article on this website about having a database of all aliases that points to a snapshot (of a mirror). There was a stored procedure that would take a new snapshot, drop the old one and repoint all the aliases in the reporting database to the new snapshot. It was brilliant because there were none of the timeout issues from the other reporting database solutions like log shipping, and it doesn't suffer the time delay of traditional snapshot reporting databases.
wish I'd thought of it!!
~Craig
Craig Outcalt
MCITP, MCDBA
Craig Outcalt
September 22, 2008 at 1:34 am
SQLBOT (9/21/2008)
Another good solution that was not mentioned before is to use database mirroring and a snapshot of the mirror. Depending on how current the data needs to be, this can be a good solution. If a daily snapshot is good enough for reporting, this is the route I like to go.
But not on SQL 2000 which, if the user's posting in the correct forum, he's using.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2008 at 8:09 am
Thanks to all for their advice. I appreciate it.
Jerry.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply