Failover Techniques in SQL SERVER Snapshot vs. Log Shipping vs. Mirroring vs. Replication vs. Clustering

For a new person this is difficult to find differences in snapshot, log shipping, mirroring and replication. I have gone through multiple sites and videos last night and now summing up my findings for all 5 techniques. According to many expert persons I have found priorities in terms of failover & point in time recovery support of these techniques in SQL SERVERS, following are these priorities.

1-    Failover Clustering

2-    Replication

3-    Mirroring

4-    Log Shipping

5-    Snapshot

Here is the basic and major difference between all techniques

1) Failover Clustering is a high availability option used with clustering technology provided by hardware and Operating system. Data resides on SAN network storage instead of any particular server. Advantages of SAN storage is large efficient hot pluggable disk storage. You might use other technique like mirroring with failover clustering.

2) 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. There is no stand by server like in mirroring and publisher & subscriber both are active. There are different data updating types in replication

Type of replication	Use when…
Merge replication	·         There are a large number of Subscribers. ·         Data is replicated to mobile users. ·         Replicated data is frequently updated at the Subscriber. ·         Data filtering is needed so that Subscribers receive different partitions of data.
Peer-to-peer transactional replication	·         Replication is used to improve scalability and availability. ·         Minimal latency is required. ·         Data is not partitioned among Subscribers. ·         Conflicts typically do not occur, but they must be detected if they do.
Transactional replication with updating subscriptions	·         There are a small number of Subscribers. ·         Replicated data is mostly read-only at the Subscriber. ·         Subscriber, Distributor, and Publisher are connected most of the time (for immediate updating subscriptions).
Snapshot Replication	·         Provide initial data set for transactional and merge publications ·         Can be used by itself ·         It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time. ·         Replicating small volumes of data. ·         Snapshot replication is most appropriate when data changes are substantial but infrequent.

3) Mirroring 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 with the help of Witness, Additional advantages of Mirroring include support at .NET Framework level plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

4) 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. As this process will complete manually you can also jobs to copy backups to destination folder and other job use that backup to restore.

5) Snapshot is a static read only picture of database at a given point of time. It is used as snapshot replication. Data is updated to snapshot when you have mentioned SQL Agent to run snapshot job. You can perform this for to get offline database with latest data changes for reporting.

I will soon upload videos for each technique. Thanks.