Replication newbie

Discussion in 'microsoft.public.sqlserver.replication' started by John, May 21, 2010.

  1. John

    John Guest

    We have 2 SQL 2005 servers. One in the local office and a Second at a co-lo
    in another state.
    What I am needing to do, to replicate the local one, to the co-lo. I am
    thinking that Transactional would be best as it would keep an almost realtime
    copy.

    The part I am not sure about, is if we add a table, or modify a table, will
    that change get replicated out as well?
     
    John, May 21, 2010
    #1
    1. Advertisements

  2. John

    Ben Thul Guest

    Before you embark down the replication path, what need are you trying
    to serve? Replication may or may not be the right answer depending.
    It sounds like your need may be to have a backup copy at your co-lo,
    in which case log shipping or mirroring might serve you better.

    But, to answer your question, some schema changes are handled
    automatically while others aren't. For instance, adding a new table
    to the published database isn't handled automatically, but changes to
    already replicated tables are (assuming you've configured it for
    this).
     
    Ben Thul, May 21, 2010
    #2
    1. Advertisements

  3. John

    John Guest

    it is so that we would have a backup copy. I havent heard of 'log shipping'
    before, but wouldn't mirroring take a lot of bandwidth?
     
    John, May 21, 2010
    #3
  4. John

    Ben Thul Guest

    Ah... so you are trying to accomplish some sort of high availability.
    In that case, I'd definitely suggest either mirroring or log-
    shipping. I'll answer your question about bandwidth with another
    question: how much is your data (and uptime) worth?

    Relevant BOL links:

    Log shipping: http://msdn.microsoft.com/en-us/library/ms187103(SQL.90).aspx
    Mirroring: http://msdn.microsoft.com/en-us/library/ms177412(SQL.90).aspx
    High availability: http://msdn.microsoft.com/en-us/library/ms190202(SQL.90).aspx

    HTH!
     
    Ben Thul, May 21, 2010
    #4
  5. John

    John Guest

    I am sure that as long as data was restored within an hour, that would be OK.

    Would Log-Shipping work if a table was modified by adding a field? or if a
    new table was created in a database?
     
    John, May 21, 2010
    #5
  6. John

    Ben Thul Guest

    Log shipping works by applying transaction log dumps from the primary
    to the secondary. So, any change that happens at the primary will
    happen at the secondary once the log that includes that change is
    applied. So, both ddl and dml should propagate.

    However, as with most things, I invite you to try it. You shouldn't
    be leveraging your business on what some crackpot on the internet said
    without testing it out. And yes, I realize I just called myself a
    crackpot. :) "Trust, but verify".
     
    Ben Thul, May 21, 2010
    #6
  7. John

    John Guest

    LOL ok, thank you for your help, I appreciate it.
     
    John, May 21, 2010
    #7
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.