Search This Blog & Web

Wednesday, March 7, 2012

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 Clusteringis 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)Replicationis 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)Mirroringwhich 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 Shippingis 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)Snapshotis 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.