Following is the difference between
High Availability & Disaster Recovery solutions provided by SQL Server:
High Availability & Disaster Recovery solutions provided by SQL Server:
|
High Availability and Disaster
Recovery SQL Server Solution |
Potential Data Loss (RPO)
|
Potential Recovery Time (RTO)
|
Automatic Failover
|
Readable Secondaries(1)
|
|
|
AlwaysOn
Availability Group – synchronous-commit |
Zero
|
Seconds
|
Yes
|
0 – 2
|
|
|
AlwaysOn
Availability Group – asynchronous-commit |
Seconds
|
Minutes
|
No
|
0 – 4
|
|
|
AlwaysOn
Failover Cluster Instance |
NA(5)
|
Seconds -to-minutes
|
Yes
|
NA
|
|
|
Database
Mirroring(2) – High-safety (sync + witness) |
Zero
|
Seconds
|
Yes
|
NA
|
|
|
Database
Mirroring(2) – High-performance (async) |
Seconds(6)
|
Minutes
|
No
|
NA
|
|
|
Log
Shipping |
Minutes(6)
|
Minutes -to-hours(6)
|
No
|
Not during a restore
|
|
|
Backup,
Copy, Restore(3) |
Hours(6)
|
Hours -to-days(6)
|
No
|
Not during a restore
|
|
(1) An AlwaysOn Availability Group can have no more than a total of four
secondary replicas, regardless of type.
secondary replicas, regardless of type.
(2) This feature will be removed in a future version of Microsoft SQL
Server. Use AlwaysOn Availability Groups instead.
Server. Use AlwaysOn Availability Groups instead.
(3) Backup, Copy, Restore is appropriate for disaster recovery, but not
for high availability.
for high availability.
(4) Automatic failover of an availability group is not supported to or
from a failover cluster instance.
from a failover cluster instance.
(5) The FCI itself doesn’t provide data protection; data loss is
dependent upon the storage system implementation.
dependent upon the storage system implementation.
(6) Highly dependent upon the workload, data volume, and failover
procedures.
procedures.
Using
availability groups for high availability and disaster recovery
availability groups for high availability and disaster recovery
 Figure – shows the HA and DR solution using availability groups. |
|