SQL Server Performance Forum – Threads Archive

MS Log Shipping – can it help me?

Hi all, We currently have a manually coded version of log-shipping (inherited from SQL 7 days) that is relatively unstable, and yet fiarly critical in our environment <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> So naturally I want to move to a MS solution, with all the positives it would give us, but given my brief review of the BOL and articles I’ve found, I have some concerns. Speciifcally, I have a requirement for: >Varying restore intervals – basically, over night I would need less frequent refreshes, and specific point-in-time that need to be available for quite a while, while during the day I would like a fairly frequent refresh, say every 15 minutes >External triggering and pausing of the schedule – One of the night point-in-time is to be used for a critical extract that must be done a that specific point-in-time, and we need to stop/pause LS until this extract is successful. Also, there are certain extracts etc. that would run based on triggering from an external scheduling tool Reading the articles, I am also uncertain whether my scenario of 3 source server’s, and 1 destination server, would pose an issue (with a seperate server/instance running as monitoring). Our intention is not to use this log shipping to create a failover environment, but to create a Reporting/Extract/Ad Hoc "mining-type" query environment. So – any ideas? Panic, Chaos, Disorder … my work here is done –unknown

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.

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=’

‘ /> <pre id="code"> time lineSource Server1Source Server2Source Server3Target Server1Mon Server1 00:00 external scheduled job finished at 00:32ship log (db1)restore log (db1)Control & Mon Turn off LS on DB1 until Job X on TS1 is completed 04:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 06:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 07:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 08:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 08:15if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> every 15 minutes until 13:00 13:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 14:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> every 5 minutes until 18:00 19:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 22:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> 00:00 external scheduled job finished at 00:27ship log (db1)restore log (db1)Control & Mon Turn off LS on DB1 until Job X on TS1 is completed 04:00if enabled, if enabledif enabledif enabled, ship log ofship log (DB2,ship log (DB5,restore log (db1, DB1DB3,DB4)DB6,DB7,DB<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />db2,db3,db4,db5, db6,db7,db<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> … and so on… </pre id="code"> So, what do you think? Some of these database are external productions (i.e. we have no control or ability/right to changes structures – i.e. replication). 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. So – your thoughts? Panic, Chaos, Disorder … my work here is done –unknown

]]>