Niraj Bhatt – Architect's Blog

Ruminations on .NET, Architecture & Design

Snapshot vs. LogShipping vs. Mirroring vs. Replication vs. Failover Clustering

All these SQL SERVER terms were quite confusing for me. Luckily, I got to attend Vinod’s session last Saturday at BDOTNET’s UG meet. So I am going to jot down my understanding of them, & will look forward to read your comments on it.

1) 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. (N.B. There is also something called as Snapshot Isolation Level which is different from Database Snapshot).

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.

2) 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 time. You have limited investments in terms of shared storage, switches, etc.

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 (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 :) ). Additional advantages of Mirroring include support at .NET Framework level (read no switching/routing code – requires ADO.NET 2.0 & higher) plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

Usage Scenario: You want very less down time and also a cost effective solution in terms of shared storage, switches, etc. Also you are targeting a single database which easily fits in your disks.

4) 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.

5) 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.

You might want to look at the licensing options of SQL Server, various editions available and how they map to above features. You can find this information in detail here.

Hope that helps to some extent :) .

About these ads

17 responses to “Snapshot vs. LogShipping vs. Mirroring vs. Replication vs. Failover Clustering

  1. nirajrules December 9, 2008 at 9:59 am

    From Vinod :) – other difference between Log shipping to mirroring is Log shipping can be 1:n while DBM is 1:1

  2. Chakravarthy February 20, 2009 at 9:41 pm

    Hi Niraj,

    Many people also consider Failover clustering as one more of the high availability techniques. It is not specific to SQL, but that is the first thing customers seem to ask .

    Regards,
    Chak.

  3. nirajrules March 23, 2009 at 3:49 pm

    Hi Chak,

    Sorry I missed your comment. I will update my post to include fail over clustering as well. Though the focus of this post was more on DR than HA. Also motivation was to highlight techniques apart from Clustering.

  4. Pingback: .LDF – SQL Server « Niraj Bhatt – Architect’s Blog

  5. Pingback: May 2010 be your Best Year so far!!! « Niraj Bhatt – Architect’s Blog

  6. Pingback: Architecting Data Warehouse « Niraj Bhatt – Architect's Blog

  7. Pradeep December 17, 2010 at 10:39 pm

    Nice article, exactly what I wanted.

  8. Pingback: SQL SERVER – Guest Post – Architecting Data Warehouse – Niraj Bhatt « Journey to SQLAuthority

  9. Bill Ross May 10, 2011 at 1:51 am

    I currently have a job that restores last night’s backup to my reporting server, so my reporting server is always up to date as far as last night’s backup. This works fine for what I need. I’m just wondering if you can think of any reason why one of these other technologies would be advantageous to me *other than* being more up to date (which right now, is not important). Thanks.

  10. vinodkumar gupta May 16, 2011 at 3:03 am

    quite helpful ….dobts cleared. Thanks :)

  11. Sam August 1, 2011 at 8:33 am

    You may also want to include cons here for each

  12. Amit Trivedi August 24, 2011 at 2:57 am

    This Article clear my Confusion.
    Thank a Lot

  13. jimmy June 1, 2012 at 12:28 am

    Hey guys, i need help understanding one thing, if i have to migrate my ms sql database with oracle database, would the best way to do this be replication?

  14. Pingback: Diferencias entre Snapshot, LogShipping, Mirroring, Replication y Failover Clustering « dbasqlserver

  15. Pingback: RTO vs. RPO | Niraj Bhatt - Architect's Blog

  16. Pingback: SQL Server 2012 Log Shipping | Vijay Microsoft Technical

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 172 other followers

%d bloggers like this: