SQL Server Performance

Setup log shipping with Tsql

Discussion in 'SQL Server Log Shipping' started by jimmyjlli, May 18, 2005.

  1. jimmyjlli New Member

    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
  2. satya Moderator

    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.
  3. simondm New Member

    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.

  4. jimmyjlli New Member

    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.
    DB@LocA and DB@LocB 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 DB@LocB using QueryAnalyzer using WindowsAuthenication.
    c) when i try to add DB@LocB 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 DB@LocA 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.
  5. satya Moderator

    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.
  6. jimmyjlli New Member

    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 DB@LocB; after i remove that, i am able to use EM to add DB@LocB as another destination log shipping server. And log shipping is working now...
  7. satya Moderator

    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.

Share This Page