SQL Server Performance

SQL Server Log shipping - structure changes?

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

  1. SQL_Guess New Member

    I've just started delving into this.<br /><br />I've got a wizard do a VERY simple log ship for a test database (VERY SIMPLE - 0 tables [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] ) - that is working fine.<br /><br />Now I've added my first table - and it seems that my beliefs are correct - i.e. table creation is not an operation than can be re-created via the "log" part of log shipping - a new full backup will be needed. Correct?<br /><br />FYI - this is a proof-of-concept (which I need to finish by Wednesday [B)] )at the moment, with the idea being to sue Log Shipping to create a reporting environment, and not a warn-failover environment...<br /><br />CiaO 4 now - and have a great weekend<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  2. satya Moderator

    CREATE TABLE is a logged operation and if it is completed on the primary server then the next set of log shipping process should affect the same on the secondary server during the restore.

    For further clarity I will have a test of the process and come back to you.

    (and you too.. enjoy the rest of cricket test matches with WI [8D])

    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

    Thank's Satya ... I will enjoy the cricket, especially the test cricket - it's been a while since we could smile about test cricket, especially with the mauling England gave us, not to mention the India tour [:/] ...

    I have done a create table, and at this point in time, it doesn't appear to have been shipped across - I've also inserted data.... no joy, even though LS alleges it has running with no problems for the:

    Log Shipping Alert Job - Restore
    Log Shipping copy for (local).TestLogShip_logshipping
    Log Shipping Restore for (local).TestLogShip_logshipping
    jobs...

    The Log Shipping Alert Job - Backup job has been complaining about 14420 errors - but that was since the creation - I susepct I did something stupid with the creation of the 1st maintenance plan, regarding the out of synch threshold - I don't believe it has any effect on what I am doing (per above)

    I've create a second job, with a second test db - I'll see if that has better luck (since I re-checked the out of synch thresholds for that one).

    BTW - it would "seem" that I might be able to use sp_update_log_shipping_plan to change the out of synch switch?

    Panic, Chaos, Disorder ... my work here is done --unknown
  4. satya Moderator

    As part of log shipping, alert messages 14220 and 14221 are generated to track backup and restoration activity. The alert messages are generated depending on the value of Backup Alert threshold and Out of Sync Alert threshold respectively.

    My test here is successful and I can see the table creation on the secondary server without any issues.

    I would suggest to recheck the maintenance plans created by the wizard (I never trust them) and better to go with pre-defined scripts to the installation.

    (On the lighter side, cricket is good game...I love it and plays too. I feel winning and losing is a part of it... it only matters in the world cup for me)

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

    Hmm - the wizard (I also mis-trust them, unless they are part of (a) the fantasy book I am reading, (b) the computer game I am playing [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] ) is just the very first step to get to know MS Log Shipping.<br /><br />In reference to :<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> and better to go with pre-defined scripts to the installation.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />What do you mean? Do you mean using the 21 or so log_shipping SP's on your own to create your own log shipping, using MS's stored proc's? Are pre-defined scripts elsewhere?<br /><br />(On the lighter side - my cricket days are behind me now - marriage tends to demand the end of long sporting activties, I've found, but I do miss those days, sometimes. Win and Lose it part of life, but currently it seems like we (South African's) have been experiencing a little too much of one side, as it were [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] ) Well, have a good weekend - it's closing on summer there, now, I think, so hopefully you get some sunshine... time for me to go home - another up-to-7pm work day *sighs* )<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  6. satya Moderator

    I mean to say use the MS scripts that are defined in the Log shipping process as explained in thishttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx article for reference.

    I tend to say better not to depend on the maintenance plans, rather create own log shipping jobs by referring to the created jobs.

    (Yeah, expecting a warm summer this time... good to play more)

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

    Found my mistake - errors in log shipping UNC directory - Transaction Lgo backups weren't being found and copied.

    Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page