SQL Server Performance

Hot standby with log shipping

Discussion in 'SQL Server 2005 Log Shipping' started by dba247, Sep 27, 2007.

  1. dba247 New Member

    Why google when you have sql-server-performance.com [:D]
    Anyway here are my questions --
    1) We have a 2 node production active/active cluster and I want to set up a remote hot standby.
    I want to use a single hot standby database server to host all the databases from both the nodes in the production cluster - is that possible ?
    2) Can somebody point me to some documentation on how to set up a hot standby with log shipping ?
    Thanks in advance.
  2. dba247 New Member

  3. dba247 New Member

    Folks - are there any distance constraints to the hot standby with log shipping ?
    Meaning - can the standby be a 3000 miles apart from the primary ?
  4. satya Moderator

    Network connection between these 2 sites should be intact and no downtime should be expected, this will ensure the transaction log shipping will work without any issues.
    Also think about service pack and hotfixes application, maintaining similar levels of versions between these SQL instances.
  5. dba247 New Member

    Ok - so there are no distance constraints or restrictions between a primary and secondary server ?
    The primary and secondary are in different domains and are almost 2000 miles apart - do we have to use ftp in this case to manually ship the logs
    over to the secondary ? How exactly do logs get picked up by the secondary server in "log shipping" ?
  6. satya Moderator

    For the discussion sake say if you do not have any network contention or no downtime between the link, then you could perform LOG SHIPPING to keep up the hot standby server.
    In this case if the both domains are different then you have to use a similar named account on primary and secondary with necessary admin privileges on server & SQL Server. KBA http://support.microsoft.com/kb/314515 on FAQ and http://support.microsoft.com/kb/321247 configure security.
  7. dba247 New Member

    The problem is I cannot use file sharing / network name share for log shipping.
    Looks like I have no choice but to use this approach unless you have a better suggestion --
    1) full backup of primary (in full recovery mode) and restore (with recovery) on standby
    2) restore transaction log from primary onto the standby create a 'maintenance plan' to backup transaction log on the primary every so often (lets assume every hour for now) to a local drive
    3) create a windows scheduled job on the secondary server to 'pull' the transaction log from the primary
    4) create and schedule a sql server 'job' on the standby (that will call a T-SQL script to restore and recover the standby)
    Do you have a better suggestion or any improvements to this logic ?
  8. dba247 New Member

    Anybody ?
  9. satya Moderator

    Its long time I believe you have posted above, so have you tested this approach or not?
    If so do you see any constraints or issues?
  10. dba247 New Member

    I haven't automated this process yet (synching up the standby manually and shipping the log every day manually via sftp) but I will definitely keep you all posted if and when I automate this one- especially if I run into any issues. But I am pretty confident that this will work.
  11. satya Moderator

    If you have the development platform or using virutal servers approach you can test the automated approach without any further doubts to deploy on the live platform.

Share This Page