SQL Server Performance

MS Log Shipping - can it help me?

Discussion in 'SQL Server Log Shipping' started by SQL_Guess, Apr 4, 2005.

  1. SQL_Guess New Member

    <br />Hi all,<br /><br />We currently have a manually coded version of log-shipping (inherited from SQL 7 days) that is relatively unstable, <br />and yet fiarly critical in our environment <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br /><br />So naturally I want to move to a MS solution, with all the positives it would give us, but given my brief review <br />of the BOL and articles I've found, I have some concerns.<br /><br />Speciifcally, I have a requirement for:<br />&gt;Varying restore intervals - basically, over night I would need less frequent refreshes, and specific point-in-time <br />that need to be available for quite a while, while during the day I would like a fairly frequent refresh, <br />say every 15 minutes<br />&gt;External triggering and pausing of the schedule - One of the night point-in-time is to be used for a critical <br />extract that must be done a that specific point-in-time, and we need to stop/pause LS until this extract is <br />successful. Also, there are certain extracts etc. that would run based on triggering from an external scheduling <br />tool<br /><br />Reading the articles, I am also uncertain whether my scenario of 3 source server's, and 1 destination server, <br />would pose an issue (with a seperate server/instance running as monitoring). Our intention is not to use this <br />log shipping to create a failover environment, but to create a Reporting/Extract/Ad Hoc "mining-type" query <br />environment.<br /><br />So - any ideas?<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  2. satya Moderator

    I don't see any issue in supporting the SQL 2000 log shipping to support your environment and act as warm standby and cater MIS purpose.

    In general 15 minutes is the accepted interval between the log restores and you can decide the time depending upon the database activity and time taking to complete the restore.

    You can pause the log shipping schedule until the external trigger process completes, but if any non-logged transactions are involved then the log sequence will be disturbed and you need to restore the full backup before starting the LS schedule.

    HTH


    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. SQL_Guess New Member

    Some good news, if I understand it. Let me try and sketch roughly what I'm looking at, and whether I understand you properly <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />time lineSource Server1Source Server2Source Server3Target Server1Mon Server1<br /><br />00:00<br />external scheduled<br />job finished at<br />00:32ship log (db1)restore log (db1)Control & Mon<br />Turn off LS on DB1 <br />until Job X on TS1 is<br />completed<br /><br />04:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br /><br />06:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />07:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />08:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />08:15if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />every 15 minutes<br />until 13:00<br /><br />13:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />14:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />every 5 minutes<br />until 18:00<br />19:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />22:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />00:00<br />external scheduled<br />job finished at<br />00:27ship log (db1)restore log (db1)Control & Mon<br />Turn off LS on DB1 <br />until Job X on TS1 is<br />completed<br />04:00if enabled, if enabledif enabledif enabled,<br />ship log ofship log (DB2,ship log (DB5,restore log (db1,<br />DB1DB3,DB4)DB6,DB7,DB<img src='/community/emoticons/emotion-11.gif' alt='8)' />db2,db3,db4,db5,<br />db6,db7,db<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />... and so on...<br /></font id="code"></pre id="code"><br /><br />So, what do you think?<br /><br />Some of these database are external productions (i.e. we have no control or ability/right to changes structures - i.e. replication).<br /><br />These database are related to each other, for example and external enigine's DB has specifc time relations with our nascent CRM store, and our extrenal worklfow store, etc. etc.<br />So - your thoughts?<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page