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 for Snapshot: 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.
Log Shipping is an old technique available since SQL SERVER 2000 and is considered more of a redundancy technology. 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.
- You can log ship to multiple servers.
- Log shipping is only as current as how often the job runs. If you ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby.
- You can leave the database in read only mode while it is being updated. Good for reporting servers.
- Good for disaster recovery
Usage Scenario for Log Shipping:
- It can be used to provide a full copy of your primary environment, typically used as a warm standby that can be manually brought online. This can be used to provide additional redundancy to your backup strategy. Log shipping can also be used to offload reporting from a primary server by creating a read only copy of the production database at an alternative location/server.
- You can cope up with a longer down time. You have limited investments in terms of shared storage, switches, etc.
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.
- Database mirroring is limited to only two servers.
- Mirroring with a Witness Server allows for High Availability and automatic fail over.
- You can configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing.
- While mirrored, your Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode.
- Mirroring with SQL Server 2005 standard edition is not good for load balancing (see sentence above)
Usage Scenario for Mirroring: 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.
Replication is quite a diverse technology and can be used to cater for a number of different scenarios, the choice of which will determine the specific type of replication that is implemented.
And 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 for Replication:
- 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.
- Merge replication can be used to support distributed processing by spreading the workload of an application across several servers, i.e. distributed processing architectures. Often requires an application that is relatively aware of its environment. Techniques such as conflict resolution also have to be taken into consideration in order to ensure data consistency across the entire integrated environment.
- Transactional Replication can be used in a similar fashion to log shipping however you can limit the specific objects that are replicated to the subscriber. This can be useful if only a subset of tables is required for reporting purposes.
Failover Clustering is an availability technology that provides redundancy at the hardware level and is built on top of Windows Clustering technology, i.e. it is not specific to SQL Server.
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.
Usage Scenario for Failover Clustering: The processor blows up on Server A. Fortunately Server A is part of a SQL Server Cluster and so Server B takes over the job of providing the SQL Server Service, within a matter of seconds. All of this occurs automatically and is transparent to the database users and or application being served by the cluster.
The main difference between Database Mirroring and clustering is that SQL Clustering provides redundancy at the instance level whereas database mirroring provides redundancy at the database level.The following link provides a comparison between these two technologies that you may find of use.
http://msdn.microsoft.com/en-us/library/ms191309(SQL.90).aspx
I hope this clears things up for you a little. That said if you have any specific queries I would be happy to help so feel free to drop me line.
