Solutions For Microsoft SQL Server High Availability and Disaster Recovery
High availability is the procedure of measurement through which one can determine power of a system. It detects whether a machine is capable of working in a scenario where system component failure occurs. Well, the disaster recovery is the procedure through which a machine is brought back into its early stable state, after a man-made or natural disaster.High availability and disaster recovery both these increase availability in Microsoft SQL server.
HA and DR solutions strive to determine non-functional sort of requirements like system availability, data retention, user experience, business continuity, and fault tolerance. This post is going to give summary regarding SQL Server high availability and disaster recovery solutions.
Important Terminologies
Before proceeding further, it is essential to be known from some key terminologies. So, first begin with definition of them and then, proceed with HA and DR solutions!
Recovery Time Objective – It is the total time duration taken to accept the application downtime, either from scheduled maintenance or unplanned outage. The purpose of this is to regain back entire service to a point from where new transactions can occur.
Recovery Point Objective – It is the power to handle potential information loss from an outage. It is the time difference in between the most current data regained after the failure and the last committed data transaction prior to the failure. The actual information loss is dependent on the workload present in the server when failure occurred.
Recovery Level Objective – This terminology for SQL Server high availability and disaster recovery solution describes the granularity through which one can recover data. This recovered data could be any like particular table, database, or whole instance.
Quorum – It is the minimum number of officials of an assembly, who must be represented before they organize a business. The quorum requirement renders security against unrepresentative operation in the sake of body name via disproportionately less number of individuals.
Note – In terms of SQL Server 2012 high availability and disaster recovery, quorum ensures that the server database is owned by one partner or copied at a time.
Let’s Begin With Several HA And DR Solutions
1. Single Site SQL FCI (Failover Clustered Instance)
Following things are be considered before execution :
- In this, there is no concept related to the secondary database. It doesn’t keep replica of data and doesn’t render protection from I/O subsystem failure.
- A sudden outage like network failure or power failure on primary data center isn’t determined because all nodes are connected on one primary center.
- No extra requirements w.r.t the models of database recovery is needed.
- Ensure that the nodes are systematically arranged and patching levels should match.
This SQL Server high availability and disaster recovery solution is primarily a HA feature. It provides protection against :
- Machine failures
- OS failures, and
- SQL instance failures
2. Database Monitoring Solutions
Below mentioned points need to be considered in this solution for high availability and disaster recovery in SQL Server :
- Database must be using model of Full recovery
- Database on secondary is not readable and writeable
- Cross database transaction feature is not supported
- Extra management overhead of third witness server
Basically, this solution is for disaster recovery whose benefits are mentioned below :
- Can be utilized like HA solution with the option of synchronous/automatic failover
- Provide prevention against Input/Output subsystem failure on primary datacenter
- Renders an alternative and automatic method of redirection to secondary database
- Protection at the database level (RLO)
3. Transaction Log Shipping
Following requirements needs to be considered before implementing this SQL Server high availability and disaster recovery solution :
- No automatic redirection or failover is included in this
- Database should be in bulk or full model of Logged recovery
- Cannot collect a set of database to simultaneous failover
- Database on secondary is not readable at restoration time
This solution is primarily for disaster recovery and offers following advantages :
- Protect secondary from people error on main site
- Render use of compressed backup files
- Validate transaction log backups regularly
- Capable of handling several secondaries
4. Multi-site Clustering Via SAN Replication
It is both SQL Server 2012 high availability and disaster recovery solution that makes use of failover cluster integrated with SAN replication. In this solution, following considerations are included :
- Extra costing for SAN replication technology
- Extra configuration of SAN replication technology and complexity of managing it
- Quorum model and options for node vote should be configured to make sure that an outage at the disaster recovery site does not lead to the cluster losing quorum.
Below illustrated are the benefits associated with this two-in-one solution :
- Protection against input-output subsystem failure on main datacenter
- Data drives could be asynchronously or synchronously mirrored
- External dependencies are handled properly in a typical manner
- SQL 2012 eliminates need of VLA by giving support for multi-subnet platforms
That’s All For Today
Top 4 SQL Server high availability and disaster recovery solutions are illustrated in this post. Microsoft SQL server administrator can go through them either to check the system power or to regain back server at its stable state.
