SQL Server Performance Forum – Threads Archive

Log shipping setup

Hello everyone, I have to setup Log shipping for a enviroment with Multi Servers and Multi databases. I have got very quick doubts; some of them you might find elementary. 1) We are using SQL server 2000 SP3 Enterprise Edition, should I use Log shipping which SQL server2K implements itself using DB Maintenence wizard; or would you suggest me to make my custom log shipping system as outlined in: http://www.sql-server-performance.com/sql_server_log_shipping.asp</a> 2) In both the cases could I place my Monitor server on the Secondary (Stand-by) server; we don’t have an additional server which we can dedicate to monitor Log shipping. Also how much memory and CPU intensive this Monitoring Tool is, a rough idea <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> 3) First time once the Secondary server database has been restored from the Full back-up; we only need to restore Log backups, not the Full back-up again… anytime. Am I correct ? I got little confused here. 4) Where can I read more information to avoid contention as regards Time Interval between Log restores? 5) I have to setup Log ship for almost 30 Databases which resides on 5 different servers; but to implement them I have only 2 stand by servers inhand. This means 5 Primary servers v/s 2 Stand by servers. Will this cause any problems. I don’t see any other but at the time of role-change, I am most worried about. Any experience/help would be helpful for me. Thanks for all your help!

Forgot to mention one thing here,on two servers the databases are getting created and deleted dynamically so I guess this will prevent setting up the log shipping enviroment for those databases through built-in Enterprise manager utility. I should create custom Stored Procs and Jobs on my own. Thanks!

1) I would go with the link than depending upon the usual wizard as the process is clear with explanation. 2) No issues in keeping the monitoring server on the secondary server. SQL Server 2000 log shipping uses Enterprise Manager’s Log Shipping Monitor utility to monitor each log shipping pair. Microsoft recommends that you install this utility on a separate monitor server. 3) Yes as long as the tlog sequence restore is not disturbed. So ensure no network issues in between while the log shipping is working. 4) Keep an eye on event viewer, sql server error log and log shipping monitoring server. 5) Ensure the recovery of database doesn’t disturb the current log shipping process for other databases, any time during the recovery you need not require the SQL services restart, but its better to keep tested all the process. Custom made stored proceudres will give more grip and idea about the process, rather than going via DB maintenance plan wizard. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya for all this. Great help indeed! 1) Is it possible to Integrate SQL 2000 DB Maintenance Monitoring wizard along with the Custom made Stored procs Log Shipping solution. I guess not, as I will not be using msdb’s log shipping tables. 2) While implementing Log shipping through custom made stored procs, what kind of monitoring does one actual implement with; Is it just an alert message if any Step fails.

1) Once you’re struck with maintenance wizard means, SQL will override the manual jobs. So for complete understanding of what you’re performing you can deploy own log shipping. 2) The inital setup to know if it goes wrong is only by the way of using Alerts in SQL server and constant monitoring of SQL error logs until it is assured that the system is working without any issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, in terms of "own log shipping", are you talking about using the MS produced SP to create a controlled Log shipping that meets specific needs, or are you talking about writing your own SP’s to do the whole file copy, etc. etc. If you are talking about options 1 (using the MS SP’s) – do you have / know of any examples – it seems I need to go down that route…. Panic, Chaos, Disorder … my work here is done –unknown

You might want to check out Tara’s articles athttp://weblogs.sqlteam.com/tarad MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Thanks Satya, SQL_Guess and Derrick.I am following the path you suggested and so far I am happy<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Tara’s insight is much helpful in terms of managing the own log shipping.
Thanks Derrick. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Guys, I think I am done for the most part. But still I want to bug you <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> 1) For transferring the Logins/passwords/permissions to standby; will it be adequate to use sp_sidmap. SO far what I have read, I understand that this sp does include most of the manual work and there is no need to use DTS login xfr task/sp_revlogin/sp_change_user/sp_resolve_login etc…Am I on the right track? 2) What’s the approach for bringing back again the Primary Server online(as Production server) once I have failovered it to Standby server and my Primary server is up again. Do I need to implement the Log shipping process on Standby server now (exact reverse process). Thanks Much!

1) I use mostly SP_REVLOGIN and to fix them SP_CHANGE_UESRS_LOGIN as per this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;246133 link. 2) This information is explainedhttp://www.sql-server-performance.com/sql_server_log_shipping.asp here.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya once again. I didn’t get much information for 2) in that link. At the last of the article in the bullet point only below sentences are written, nothing much. "Another aspect of the failover process you will want to plan for is how you will make the move back from the standby server to the production server once the production server is working again." So I am wondering if this is a setup for reverse log shipping process on the standby server now. Thanks

Refer to the books online for the topic Log shipping –> Role changes and further information on LS too.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>