Friday, December 8, 2017

SQL Server - High Availability & Disaster Recovery solutions

Following is the difference between 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.        
(2) This feature will be removed in a future version of Microsoft SQL Server. Use AlwaysOn Availability Groups instead.             
(3) Backup, Copy, Restore is appropriate for disaster recovery, but not for high availability.                                            
(4) Automatic failover of an availability group is not supported to or from a failover cluster instance.                            
(5) The FCI itself doesn’t provide data protection; data loss is dependent upon the storage system implementation.    
(6) Highly dependent upon the workload, data volume, and failover procedures.                       


Using availability groups for high availability and disaster recovery

                                              Figure - shows the HA and DR solution using availability groups.

                                          
                         

3 comments:

  1. This website can live streaming , you can join at my site :
    agen judi online terpercaya
    Prediksi Bola

    Thank you
    agenpialaeropa.club
    gamesonline.ga
    beritasemasaterikini.net

    ReplyDelete

  2. Nice material. Disaster recovery is one of the hot topics nowadays due to recent ransomware attacks and software solutions for such cases (like Nakivo protection from ransomware ).

    ReplyDelete
  3. Pretty descriptive pics, very helpful for understanding the basic principles of disaster recovery.
    protection against ransomware

    ReplyDelete