One of the major responsibilities of a DBA, or any user deploying the SQL Server, is to ensure that all the mission-critical server instances and databases are available when needed for business continuity. Also, the user must minimize the impact of database downtime when the server crashes, or a disaster strikes. SQL Server provides several availability features for high availability and disaster recovery.

Architecture of SQL Server High Availability and Disaster Recovery

The High Availability (HA) and Disaster Recovery (DR) architecture help reduce downtime and maximize data availability, ensuring smooth business operation. Before discussing the architecture for high availability and disaster recovery, let’s first understand the HA and DR concepts in SQL Server.

An Overview of SQL Server High Availability and Disaster Recovery

‘High availability’ means to ensure that SQL Server instances or database is always available, with no downtime, in case of planned or unplanned outages. A system can be made highly available through redundant storage systems and servers or fault-tolerant components at the same location.

‘Disaster recovery’ is a plan to recover from a catastrophic event (like a flood or an earthquake, cyber-attack, etc.) while incurring minimal downtime. For disaster recovery, businesses need to be prepared with systems that can be brought up online in a different place (in a secondary site).

While some technologies can be used as both high availability and disaster recovery solutions, a few can be used only as a HA/DR solution. Some technologies can be used as both HA and DR solutions, and some can be used only as high availability or a disaster recovery solution. Here’s a comparison table:

For detailed information about HA and DR solutions, refer to this link. Now let’s discuss high availability and disaster recovery architectures.

Before We Proceed

Before discussing the proven high availability and disaster recovery architectures, it’s imperative for you to consider the RTO and RPO objectives of a business:

• Recovery Time Objective (RTO) – It is the “duration of acceptable application or system downtime” when a planned or unplanned outage occurs. The initial goal of this objective is to bring the system online in read-only mode to investigate the failure. However, its main goal is to restore SQL Server service to the point where new transactions can be written.
• Recovery Point Objective (RPO) – It is the “acceptable data loss from an outage.” It measures the time gap between the last committed transaction before the failure and the updated data recovered after the failure.

High Availability and Disaster Recovery Architectures

After evaluating your business’s high availability and disaster recovery needs, i.e., downtime and acceptable data loss, you can consider configuring any of the following architectures.

Failover Clustering for High Availability and Database Mirroring for Database Recovery

In this architecture, configure Failover Clustering for high availability along with Database Mirroring for the disaster recovery capability.

Using failover cluster as a high availability solution helps protect against server crash and SQL Server failure, but since this HA solution does not maintain a redundant copy of the database, it fails to protect the database against I/O subsystem failure, power outage, or other failures.

Using database mirroring as a disaster recovery solution provides a redundant copy of a database on a different physical server, where the server is in the same data center or on some other site.

You can use any of these configuration options for this HA and DR architecture as per your business requirements:

• Configure failover cluster on each data center with database mirroring between them: To prevent workload performance from getting impacted after a failover to the secondary data center, ensure that the mirror server has the same hardware configuration as the principal database on the primary data center.
• Synchronous/asynchronous database mirroring: Operate a database mirroring session synchronously to maximize data availability, with some possible workload performance degradation. Operate a database mirroring session asynchronously to avoid any impact on workload performance, but this does not guarantee zero data loss.
• Automatic failover to the secondary data center: Configure asynchronous database mirroring session with a third (optional) server, known as the Witness. The mirror server will perform an automatic failover to the secondary data center.

Database Mirroring for High Availability and Disaster Recovery Architecture

Use synchronous database mirroring for high availability and disaster recovery to maintain a redundant (mirrored) database copy – by sending active transaction log records from the principal database to the mirror database.

In this architecture, the mirrored database takes over as the new principal database when some failures happen. Implementing this architecture ensures zero data loss when a failover is required.

The configuration options for this high availability and disaster recovery architecture can vary depending on the business requirements:

• Configure a third server instance (Witness): Include a witness server in the synchronous database mirroring architecture to perform an automatic failover. Essentially, a failover is performed automatically when any failure is detected, ensuring the continuous availability of data.
• Configure asynchronous operating mode for database mirroring: If there is insufficient network link between the principal and mirror servers to send a log record synchronously, resulting in performance degradation, configure database mirroring to send the log record asynchronously. Doing so helps improve performance degradation with some possible data loss.
• Configure database mirroring and log shipping: Configure database mirroring to set up a single mirror of the primary database. And, for further redundancy, configure one or more log shipping secondary databases set up as warm-standby databases.

Peer-to-Peer Replication for High Availability and Disaster Recovery

This architecture uses peer-to-peer (i.e., a bi-directional) replication for high availability and disaster recovery. Peer-to-peer replication comprises multiple servers (known as nodes)—the architecture for 4 peer-to-peer node replication is as follows:

As you can see in the image, data is replicated on all the nodes. So, if one node fails, the application or system will still be functional. And when the faulty node is up and running, it can be brought in sync with the other nodes. This makes the peer-to-peer replication a high availability and fault-tolerant solution.

However, the downside to this architecture is that it involves some time gap between a transaction committing on one node and the change being replicated on all other nodes; this might not guarantee zero data loss. Another downside is that it does not support automatic failover. But it allows creating multiple redundant copies of the data, which one can use for reading and writing purposes.

You may consider using this architecture when you need a secondary data copy for reading or writing activity. Also, you can use it for maintaining multiple copies of the data.

Conclusion

Make sure to read about all the SQL Server HA and DR solutions before deciding to configure an architecture for SQL Server high availability and disaster recovery. This article discussed the three most deployed HA and DR architectures and explained which structure helps reduce downtime and ensures no/minimal data loss.

While the HA/DR solutions can help ensure business continuity by minimizing response times and zero data loss in the event of a disaster, you may still encounter database corruption. Using a SQL database recovery tool can help fix the corrupted database and recover all the data without any modification.

HussaiN

HussaiN is a full-time professional blogger from India. He is passionate about content writing, tech enthusiasts, and computer technologies. Apart from content writing on the internet, he likes reading various tech magazines and several other blogs on the internet. Email ID: [email protected]