SQL Server Performance Forum – Threads Archive

Setup log shipping with Tsql

I am trying to setup log shipping from location A to B that is 1000s miles apart. Log shipping were setup from A to B (by someone before me) and were running fine, but we have a new machine for our primary DB at A, so i have to setup log shipping again. I am able to setup log shipping from the primary DB to a secondary DB at A. But when i try to add another secondary DB (at B) to the log shipping, the wizard doesn’t respond when i click the OK button. It’s like i hasn’t press it. My environment:
Win 2003 STD, SQL Server 2000 ENT SP3a (same for location A and B)
A and B is on the same domain over VPN. My questions:
1) Anyone has a clue why the wizard doesn’t respond when i click the OK button?
2) Is it possible to setup log shipping using Tsql?
e.g. sp_add_log_shipping_plan; sp_add_log_shipping_plan_database … Any suggestion is appreciated. -jimmyjlli

Yes the referred SP can be used to setup log shipping using Tsql statements.
If the secondary server is not responding when using GUI then ensure the client network utility has the value for secondary server, check lmhosts etc. 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.

You can always use the GUI on the primary server and connect to the secondary remotely. As Satya suggested make sure both the primary and secondary server are registered in EM and that it can connect. EM requires both registrations to establish log shipping.

satya, Do you have a working sample of Tsql statements to setup log shipping?
My concern is in what order i should run the log shipping SPs.
Can i simply use the values from these tables in the already-setup secondary DB in location A to setup secondary DB in location B:
log_shipping_monitor,
log_shipping_plan_databases (with correct corresponding paths),
log_shipping_plans (i am a bit confused on how to get all the Job IDs. Are they auto generated when i run sp_add_log_shipping_plan?) It would be best if i could setup log shipping using EM. Note that locations A (LocA) and B (LocB) are in same domain over VPN.
[email protected] and [email protected] are running SqlServer and SqlServerAgent using same domain user (sqlUser) I can do the following (does this mean there is no networking problem in my environment?)
1) after logon to LocA with sqlUser:
a) i can connect, by UNC path, to \LocB with read/write without login.
b) i can connect to [email protected] using QueryAnalyzer using WindowsAuthenication.
c) when i try to add [email protected] as another log shipping destination, i can
choose the server from the ServerName drop down list and then
choose the already restored DB from the existing Database drop down list.
2) after logon to LocB with sqlUser:
a) i can connect, by UNC path, to \LocA with read/write without login.
b) i can connect to [email protected] using QueryAnalyzer using WindowsAuthenication. If this indicates i have no networking problem, what else should i look at? One more minor note, when i click the OK button in the Add Destination Database window, the mouse cursor changed to the loading-cursor for a second and turned back to normal cursor — like nothing happened.

I would go with Books online for thos SPs that are defind in order and looking at the steps I also agree with you about no occurence of network issues. It helps if you monitor the event viewer logs also and use log shipping monitor for the results. On the other side you must work out the unresponsive issue from EM when you’re trying to configure log shipping using wizards, refer what I’ve referred above. 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.

I found out what was causing EM unresponsive when i try to add another destination to log shipping – it was because there was an log_shipping_monitor entry, that is different from the monitor server i currently have, in [email protected]; after i remove that, i am able to use EM to add [email protected] as another destination log shipping server. And log shipping is working now…

Glad to know the solution and appreciate your response, that helps others. 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.

]]>