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

Question

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.

Answer 1

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? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Thanks for the response Hilary!