High Availability and Disaster Recovery Technologies

Overview of High Availability and Disaster Recovery Technologies

The following table illustrates the types of technologies that are available and the pros

and cons of each.

Table 25-1 Comparison of SQL Server High Availability Technologies Solution Pros Cons

Database Backups        • Cheap and easy to do.

                                            • No additional technologies needed.

                                            • Should already be done.

  • Must be taken offsite or they  won’t help in the event of a disaster.

  • Slow to recover.

Log Shipping    • No single point of failure; separate disk and server usually.

                               • Databases can be in geographically dispersed areas.

                               • Secondary server can act as a reporting server.

                           • Simpler to administer; just another data-base in recovery mode.

                               • All objects in database are moved, not  just the data.

                               • Recovery is fast.

                               • Protects against logical corruption; for example, DBA       

                                      accidentally   drops a table.

• Failover is not automatic.

• Higher latency because log has to be copied over and applied.

• All or nothing; cannot specify tables, and so on.

• Will not copy logins from master database over.

• When log is being restored, users cannot access the data.

• In the event of a failover, you could use an entire log’s worth of data.

Mirroring              • Automatic fail over with witness in place.

                                  • Data immediately applied after commit.

                                  • All objects are moved over.

                                  • Easy to administer.

                                  • No single point of failure.

                               • Can use in conjunction with snapshots for reporting performance.

                                  • Works over large geographic distances.

• Only one mirror allowed.

• Synchronous mirroring can be slow on a WAN.

• Does not protect against  logical corruption

Replication        • Data moved to target server rapidly; less lag in access to it.

                  • Administrator controls exactly which data is moved between the systems.

                           • Access to data while the system is online.•

• Typically used for a subset of tables, not whole databases.

• Administrative burden for anything more than a few tables.

• Solution is hand crafted. Not  out of the box.

• Only data is moved between databases. Objects like stored procedures are not.

• Performance is adequate, but not as fast as a BACKUP/RESTORE operation.

• Failover is not automatic.

• Database server can take a performance hit on the CPU.

• Database is not guaranteed to be consistent with the publisher database.

Clustering              • Automatic failover.

                                  • Data in sync, shared disk solution.

• Single point of failure; shared disk.

• Servers physically close to each other and prone to exposure to same disaster.

• More complex to administer; for example, patching.

• DBA needs additional training.

• Solution is more expensive due to extra hardware and redundancy.