December 29, 2003 at 6:42 pm
Hello All,
My client won't pay for SQL Enterprise Edition, and so I have set up manual logshipping to a remote server, using a linked server, and running remote stored procedures.
Intermittently, the job steps that run the remote stored procedure (during testing, ANY stored procedure)hang forever. No error or failure, these job steps just get stuck.
Has anyone seen this behavour before?
I have worked around this by hard-coding in job times between the two servers, but I would prefer to run restores on the remote server exactly after a local backup and copy job has completed.
Any help would be much appreciated.
December 29, 2003 at 7:12 pm
I haven't seen that behavior, but I have bult manual log shipping programs with C#. But really, you could use almost anything. This might work better for you than having SQL Server take all the load.
December 29, 2003 at 10:48 pm
Backup log using sqlmaint, it prives cleaning up mechanism and naming convention.
Scheduled script to copy log files across and restore it.
In the event of error(warning), CDO mail is an option.
The script would be vbs or js.
December 30, 2003 at 12:44 am
Dude226,
Is your remote login timeout set to zero (infinite wait)? Or, is there a firewall between the servers?
A personal preference of mine is that the live server just does the log backups and that's all. Any issues with the standby server do not affect the live system at all.
The standby server is responsible for copying files over (I often use robocopy's mirror feature), checking that log backups files are recent enough, and applying the log backups in order. It is not under the control of the live server and goes by its own schedule.
It maybe does a time sync daily off the live server unless both servers are using a reliable ntp service.
And, I like having a bit of a time lag, anywhere from 5 to 15 minutes. On 2 occasions that I can think it's saved the day when data was dropped/deleted accidentally and, after stopping sql agent on the standby, we (oops, I mean they) were able to recover data with hardly anyone noticing.
BTW: Chris Kempster posted in this site a custom log shipping suite. It looks good and is well documented.
Cheers,
- Mark
Cheers,
- Mark
January 20, 2004 at 5:41 pm
Thanks very much for the replies! I've been out of action for a while.
I'm now running the logshipping via DTS packages, using the same stored procedures as I was when using a linked server. Basically, I was happy with the procedures, but running the remote procedures via the linked server was unreliable.
So now I'm using DTS's ODBC connections and all seems to be well so far.
Perhaps I should have thought of this in the first place since it is simpler and more efficient.
Your replies have shown how many ways there are to do this. Very interesting.
For others looking for info on their own problems: Mark: My remote timeout setting was not zero, and there were no firewall issues that I'm aware of. Your setup sounds much more thorough than mine. My standby server is strictly that only - no reporting on it - and I have no big problems re-synching the standby server if necessary, so all I have to do is copy backups to it and restore, using a single backup device that is reinitialised each time. A vbscript makes archived backups of the device file each night.
Cheers.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply