Log Shipping Help Please
-
I'm a bit stuck here, I think I understand the principles of log shipping but there are still a few issues I am not sure about.
We want to have a fallback solution if the harware fails on a server, however my company don't want to pay for a full cluster solution, however much I tell them its probably the better solution. So I am stuck with getting lg shipping working.
Currently we dump the server which will be the primary with full DB backups every night. During the day we backup the TLogs either every 8 or 4 hours. The whole lot gets rolled to tape overnight.
I guess that if we turn on log shipping the normal TLog dumps will interfere with this. So I assume that what we have to do is to turn off the TLog backups and make sure we retain the log shipped TLog dumps on the primary and dump those to tape each night.
However my big worry is how we maintain the secondary. From my understanding this gets a full dump when you set up log shipping and that is what is used to create the DB initially, it is then maintained by applying the shipped TLog dumps. However what I am not sure about is how to maintain this DB. So I have several questions and please excuse me if I go off on the wrong track but I really am guessing
1) Does the shipped TLog dumps write to the secondary database TLog and therefore will that just keep increasing in size. If so can we do a TLog dump on it every 4 hours or so.
2) What would be the best method of restoring the secondary if we to reconstruct it and the primary was not available. ( Oh I hope that never happens.)
3) When can we delete the TLog Dumps on the secondary as we obviously cannot just keep them all
I would really be grateful if someone could give me some practical hints on this.
Thanks
-
Hi Terry,
on that topic i would recommend you read the following article
http://www.sql-server-performance.com/sql_server_log_shipping.asp
which explains logshipping in detail.
If you are going to implement logshipping you can either use the logshipping procedures that come with SQL Server 2000 Enterprise Edition or you can do the implementation by yourself.
I personally like the second approach, because that way i know exactly what is going on...
One of the most usefull features of logshipping is that your db- and logbackups are instantly tested by restoring them into the secondary server. If your logshipping works, you know that backup and restore is working!
So you use the secondary server as a secure place to hold the backups of perhaps the last 2 or 3 days the rest should go on tape and get deleted from your servers.
To set your standby server to active you have to do
RESTORE DATABASE @DBN WITH RECOVERY
EXEC SP_DBOPTION @DBN, 'read only', 'false'
EXEC SP_DBOPTION @DBN, 'dbo use only', 'false'
Afterwards you might have to do an "sp_change_users_login" on every user, but that is the advanced stuff... 🙂
regards karl
Best regards
karl -
I agree with Karl. Use your own procedures to implement log shipping. That way, you don't have to use the Enterprise version, nor are you stuck with SQL's implementation of log shipping.
The comment about the stand-by server also testing your backups is also inciteful.
Those two reasons are why I've implemented my own shipping procedures.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
Totally agree with Karl and Russel. Set up your own log shipping solution. I set up mine from scratch in the old days ( SQL Server 7.0) when log shipping was not part of SQL Server. Now I am using SQL 2000 but still use my own solution. I also have my own recovery scripts. I have had to fail over to the standby server about two years ago and I know my process works - so I trust it
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply