What are the main differences between transactional replication and log shipping?


  • Hello All,

    In our current environment now, we have a database that serves has an application and ad-hoc database. Our goal is to remove the ad-hoc piece and move it to our current SSRS environment. The question is which method would be the appropriate solution for this goal?

    I'm thinking a transactional replication solution would be the best option because it is real time and less effort to set up. Is Log shipping primary used for a failover solution?  If I'm correct, this solution will not work for our environment because we do not want to failover to the SSRS environment. Please advice.

    Wednesday, February 27, 2013 4:14 PM


  • With log shipping you can ship the logs as frequently as 1 minute. It is possible to make it shorter but not partical. The database is in read only mode on the destination server and must go offline when the log is restored. Because of this, log shipping is not a good solution to offload reporting.

    With transactional replication transactions are replicated to the destination server. You can replicate a portion of your tables and a subset of a table. Latency can be as low as several seconds but could be very high- serveral minutes or longer.

    looking for a book on SQL Server 2008 Administration? looking for a book on SQL Server 2008 Full-Text Search?

    • Marked as answer by sot2007 Friday, March 01, 2013 1:59 PM
    Wednesday, February 27, 2013 4:33 PM

All replies