SQL Replication techniques

There are 2 types of SQL databases replication techniques available. SQL mirroring and log shipping, the following is the benefits and limitations between mirroring and log shipping:

Database Mirroring benefits:

·         - Instantaneous synchronization of changes

·         - Easy setup and maintenance

·         - Automated failover (in High Availability Mode)

·         - Easy to perform failover

·         - Fast failover. (if no transactions are queued)

·         - Applications can be mirroring aware using failover partner parameter in connection string

·         - Database mirroring can operate Synchronous or Asynchronous

Database Mirroring limitations:

·         - Limited number of databases can be mirrored per SQL instance. (however, this number is higher   than the 10 you see plastered everywhere, but really depends on your hardware)

·         - Mirrored database cannot be queried, backed up, or basically touched in any way (snapshot can be taken however)

·         - Only supports one to one server pairings

·         - Cannot mirror a database more than once

·         - Synchronous mirroring is intolerant to latency and bandwidth constraints (Asynchronous mirroring has no problems with latency or bandwidth)

Log Shipping benefits:

·         - Very flexible - Independent jobs govern backup/copy/restore behavior

·         - Delay of restore job can protect from database corruption

·         - Log shipped database can be queried. (in standby mode)

·         - Log shipping allows multiple standby databases

·         - Perhaps more tolerant to latency than sychronous mirroring.

·         - Doesn't affect performance of primary server

·         - Easy setup

Log Shipping limitations:

·         - Log shipping is always Synchronous

·         - No automated failover

·         - Cannot synchronize faster than once a minute

·         - Failover is more complicated than mirroring

·         - Harder to view status of log shipping

Database mirroring can operate synchronously or asynchronously. Synchronous replication provides zero data loss, but there is some negative impact on application performance due to synchronous replication. On the other hand Asynchronous replication has no performance impact on application but there will be a data loss during disaster recovery. Also the cost of replication is more in case of synchronous replication as this requires higher network bandwidth.

Synchronous Replication

The synchronous mode, also known as the hard synchronous mode of replication ensures that an update has been acknowledged by the Secondary host, before completing the update at the Primary. In case of a problem such as a network failure it ensures that the update fails at the Primary itself. The synchronous mode of replication is most effective in these application environments:

·         Which has lower update rates but require all the hosts to always reflect the same data.

·         Where delay in updates between the Primary and Secondary host is not acceptable.

Pros:

·         - Secure as there is no data loss.

·         - Manual reconciliation of data is less; hence less operational activities post DR

Cons:

·         - Response time degradation happens because of the network round-trip

·         - Distance between data centers shouldn’t exceed 200 kms

Asynchronous Replication 

 

In the asynchronous mode of replication, the application updates are immediately reflected at the Primary, but are sent to the Secondary with some delay. The updates are stored in the Replicator Log until they are sent to the Secondary. If the writing application experiences temporary increase in update rate, this delay may increase. If a disaster strikes during a period of peak update activity, it is possible that the most recent updates at the Primary host are not reflected in the data at the Secondary host. This is because of the lag between the Primary and Secondary data states, also called latency. To prevent this, we can configure the latency such that the data inconsistency will be acceptable in the event of a disaster. Asynchronous replication ensures that the lag never exceeds this configured maximum.

Pros:

·         - Performance impact on the host is minimal.

·         - Can span virtually any distance

Cons:

·          - May lose some data

·     - Some manual reconciliation is required by reapplying the most recent transactional logs to the point of failure.
 

Synchronous versus Asynchronous

In asynchronous mode, an application performing writes does not experience the response time degradation caused by each write incurring the cost of a network round-trip. Better response time comes at the cost of the secondary being potentially out of date. In synchronous mode, the response time is greater, but the secondary is always up-to-date.

The decision to use synchronous or asynchronous mode depends on business requirements, such as whether or not it is acceptable to potentially lose committed transactions, and if so, how many. It is also strongly determined by the available network bandwidth, network latency, the number of participating servers, and the amount of data to be replicated.