The high availability solutions bring down the effects of hardware and software failures and plus it maintains the availability of the applications so the downtime of the users can be minimized. Mostly SQL Server version from SQL Server 2012 has the support the better availability options than the previous versions of SQL Server.
Here, we will discuss the SQL Server high availability options.
Types of SQL Server High Availability Options
The types of high availability options in SQL Server are as follows:
- AlwaysOn Availability Groups
- AlwaysOn Failover Clustering
- Log Shipping
- Database Mirroring
- Snapshots of Database
- Fast Recovery
- SQL Server on Server Core
- Live Migration
The AlwaysOn Availability Groups is the next evolution of the database mirroring. It is designed for protection against planned and unplanned SQL Server downtime. Also, it replicates multiple SQL databases to as many as four replicas system and is mixed with both synchronous and asynchronous replication of the SQL transaction log. In addition, it supports automatic failover. This feature is only found in SQL Server 2012.
This option is designed to reduce the unplanned downtime of the SQL Server by allowing the clustered services such as SQL Server will automatically get failover to the backup node whenever the failure of software or hardware makes the primary node unavailable. The SQL Server editions which are limited to node failover clusters are SQL Server Standard and SQL Server 2012 Business Intelligence. The Enterprise Edition of SQL Server can have up to 16 nodes and Windows Server 2012 can have up to 64 nodes.
This option is designed to protect the SQL Server users against the disasters at the system and site level. It works by periodically running the stored procedures which forward the transaction log entries to one or more destination systems. But unlike database mirroring which works in real time and is also capable of automatically causing failover. The Log Shipping requires failback processes and manual failover. The Log Shipping is supported by all the editions of SQL Server 2012.
This option is just limited to two systems i.e. principal and mirror with the optional witness which enables the automatic failover. The database mirroring works by forwarding the SQL transaction log entries from the principal to a mirrored database and plus the SQL database can be setup either synchronously for the higher availability or asynchronously across the WAN for disaster recovery. Synchronous full safety mode is supported by SQL Server 2012 business intelligence and standard editions.
The database snapshots provide the metadata-based copy of the given SQL database and it can be used for forensic purposes or restore the SQL database to its previous state. Like for example, the snapshots of the database can be used to effectively protect the database against the corruption caused by the human error. The snapshots of the database are supported in only SQL Server 2012 Enterprise edition.
This option helps the database to be available more quickly following the restore option. Using Fast Recovery, the SQL database is available as soon the committed transactions are reapplied immediately after the restore option, with no waiting for the uncommitted transactions to be rolled back. The Fast Recovery option is only available in SQL Server 2012 Enterprise Edition.
The SQL Server 2012 supports running Server Core which boosts the SQL Server 2012 availability by reducing the amount of patching which is required by the host Windows Server operating system. All the editions of SQL Server support running on Windows Server core.
It was not included technically in SQL Server 2012. The Live Migration is a Windows Server technology which can help in reducing planned downtime for the SQL systems which are virtualized by allowing the virtual machines of SQL Server to be moved to the different host with no end-user downtime. Plus, it maintains the virtualization host to be performed.
Conclusion
These were the above mentioned high availability options. These options will definitely help the SQL Server users in recovering their lost database or server. So, instead of spending time on searching for the manual procedures user should look at these options to make their SQL database available very easily.
