Tackling Disaster Recovery With High Availability Options

👤 Andrew Jackson | Modified: May 8th, 2023|SQL | 5 Minutes Reading

Overview

SQL Server disaster recovery is a process to recover the database after a disaster. Disaster could occur due to power failure, hardware failure, accidentally, virus detected, etc. This can cause anything like data loss or any serious disruption. So, In order to prevent sudden data loss and recover database from failure, disaster recovery plan should be planned. Disaster recovery with “High Availability Options” in SQL Server is the term that a system needs maximum number of times.

In SQL Server Disaster Recovery high availability options improves the availability of databases. And prevents the effects of hardware or software failures. SQL Server provides many options for creating high availability, main high availability options are going to be discussed here as a solution for disaster recovery:

Failover Clustering.
Always On Availability Groups.
Database Mirroring.
Log Shipping.
Replication.

Describing High Availability Options In SQL Server

Failover Clustering: In clustering at least two servers are involved. They may be on a same subnet or across multiple subnet. Always On Failover Cluster Instances uses Windows Server Failover Clustering (WSFC) to deliver local high availability through redundancy at the server-instance level. A single SQL Server instance is installed across the WSFC nodes. This SQL Server instance is known as failover cluster instance (FCI). As, FCI is installed on every node, but at a time only one server is active. The active server holds the single shared copy of the data. And if failure occurs on active server (current node) FCI provides failover to another server (passive node).

Always On Availability Groups: AlwaysOn Availability Groups is an alternate option of database mirroring and is an enterprise level high availability option in SQL Server for disaster recovery This feature is a disaster recovery option that maximizes the availability of a set user database, known as availability databases, fail over together. AlwaysOn Availability Groups in SQL Server consist of a primary database available for read-write operation from the client and one to eight sets of Secondary databases available for read only operation from the clients. These secondary databases can also be made available for performing backup operation.

Log Shipping: Log shipping works at database level. A log shipping can be used for maintaining one or more secondary database (standby database) for a single primary database. In SQL Server log shipping three main operations are performed: creating transaction log backups on the primary database, copying this backup to one or more standby database and restoring log backup on secondary database.

Database Mirroring: In database mirroring two copies of a single database are maintained and copy of the database is on different SQL Server database instances. One instance is the principal server and another one is on standby mode, which act as a mirror server. The two instances act as a mirroring environment and the principal server sends active transaction log to the mirror server where all transactions are performed again.

It’s recommended not to use database mirroring in SQL Server as this disaster recovery high availability option in SQL Server will be removed from the future version.

Replication: Replication can be defined as copying and distributing data and data objects from one database to another. This includes a primary server referred as a publisher, distributes data to one or more secondary servers referred as a subscriber. That’s why this is called as a publish subscriber model. In SQL Server three types of replication occur:

Merge Replication: Both publisher and subscriber independently can make changes in the database (bi-directional replication). These changes are monitored and database is modified when needed.
Transactional: Transactions committed by the publishers are monitored and distributed to subscribers.
Snapshot: A snapshots are created by publisher and make it available for all subscribers.

These were the main Disaster Recovery High Availability Options in SQL Server which can be used for protecting data or for data recovery. Among these options one could implement any one of them according to their need, budget, scope, team level support, etc.

Recommended High Availability Options in SQL Server

Among these disaster recovery high availability options in SQL Server best option depend upon your SQL Server environment. Our Recommend to protect data from disaster:

Failover clustering can be used for protecting data through third party shared disk.
AlwaysOn availability Groups can be used for protecting data through SQL Server.
Log shipping option can be used in case SQL Server version does not support AlwaysOn Availability Groups.

Backup and restore should be performed as a basic option. Various types of backup available in SQL Server: full backup, differential backup, log backup and partial backup.

Conclusion

At the beginning of this article we discussed protection against disaster and then continued with disaster recovery high availability options in SQL Server. One can choose any high availability options recommended as per there situations. You can also go with third party SQL Database Repair solution If you have not set any disaster recovery plan for your database.