SQL Server Performance

Log Shipping vs. Replication

Discussion in 'SQL Server Log Shipping' started by harsh_sr, Oct 31, 2004.

  1. harsh_sr New Member

    I have a database with 60 tables. I want to create another database(separate server) with only 15 of these tables for reporting. I do not want to touch the existing table structure (replication adds additional row, which i do not want) on parent.

    The data lag should not be more than 2 minutes.
    Which is a better option, Log Shipping or Replication?
    Any other possible way?

    Thanks
    Harsh

    (moved from clustering)
  2. satya Moderator

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5348 for similar discussion earlier.

    I don't generally pitch replication or log shipping as a the solution for a hot standby.
    Overall clustering is the better one than these 2 options. Similarly, log shipping can provide a good option when you want a "warm standby" where both machines have all objects and data, but you manage the failover aspects yourself.



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

    Thanks Satya

    We are actually looking at creating a separate Reporting database (only for querying etc) but we need a part of database only (as i said, 15/60 tables). As I said earlier, replication induces extra column in Parent(actual data) tables, which we do not want.

    Any other solution?
    Thanks


    Harsh
  4. satya Moderator

    Then in this case use LOG SHIPPING and refer these links for more information:
    http://www.sql-server-performance.com/sql_server_log_shipping.asp
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx
    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx
    http://www.sql-server-performance.com/q&a37.asp - to handle users connected to the reporting database.

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

    I think replication seems to be the better option in this case, because:

    1. You want only part of the database to be synchronized. I don't think this is possible with log shipping.

    2. If you don't want the database lag to be more than 2 minutes, you need to take log backups every 2 minutes, which is possible but not recommended. With replication you can apply changes to standby database almost instantaneously.

    3. You want to use this database for reporting purposes, but you can not have anyone accessing this database while log shipping is restoring logs.

  6. satya Moderator

    I agree its a debatable topic and I feel its purely depends upon the individual database setup and amount of process to be defined. Overall I feel Log shipping is hassle less setup as compared to replication.

    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.

Share This Page