Log Shipping vs. Mirroring vs. Snapshot vs. Replication in Databases

Recently, I have been taking more interests and started learning in Databases. I will keep sharing my deep dive learning outcomes in my blog. Keep following..

Log Shipping	Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs. Usage Scenario: You can cope up with a longer down tim   e. You have limited investments in terms of shared storage, switches, etc. Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s).  Log shipping supports an unlimited number of secondaries for each primary database. Database mirroring is preferable to log shipping in most cases, although log shipping does have the following advantages: 1. it provides backup files as part of the process 2. multiple secondaries are supported 3. it is possible to introduce a fixed delay when applying logs to allow the secondary to be used for recovering from user error
Database Mirroring	Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Database mirroring also supports automatic failover if the principal database becomes unavailable. The mirror database is always offline in a recovering state, but you can create snapshots of the mirror database to provide read access for reporting which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs (actually logs are continuously merged in this scenario – no wonder it’s called Mirror ).
Snapshot	Snapshot is a static read only picture of database at a given point of time. Snapshot is implemented by copying a Page (8KB for SQL SERVER) at a time. For e.g. assume you have a table in your DB, & you want to take a snapshot of it. You specify the physical coordinates for storing snapshot & when ever original table changes the affected rows are pushed first to the the snapshot & then changes happen to the DB. Usage Scenario: You have a separate DB for report generation, and want to ensure that latest data for that is available. You can periodically take snapshot of your transactional database.
Replication	Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. Important thing to note here is, there are no standby servers. The publisher & subscriber both are active. Usage Scenario: A typical scenario involves syncing local / regional lookup servers for better performance with the main server in data center periodically, or sync with a remote site for disaster recovery.
Failover Clustering	Failover Clustering  is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering. Here’s a good article on adding geo redundancy to a failover cluster setup.
Few links for further Reference	http://blogs.msdn.com/b/mikewat/archive/2007/07/28/database-mirroring-and-log-shipping-which-is-better.aspx http://stackoverflow.com/questions/525637/what-are-the-scenarios-for-using-mirroring-log-shipping-replication-and-cluster http://sqldbpool.com/2010/02/15/database-mirroring-vs-log-shipping/ http://msdn.microsoft.com/en-us/library/ms187016.aspx http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/ee05954e-0934-4305-8936-b9226e231d06/