Identify resources for HADR solutions
In this we will learn to Identify resources for HADR solutions.
Always On availability groups: a high-availability and disaster-recovery solution
The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012 (11.x), Always On availability groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together.
An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.
Benefits
Always On availability groups provides a rich set of options that improve database availability and that enable improved resource use. The key components are as follows:
- Firstly, supports up to nine availability replicas. An availability replica is an instantiation of an availability group that is hosted by a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group.
- Secondly, supports alternative availability modes, as follows:
- Asynchronous-commit mode.
- Synchronous-commit mode.
- Thirdly, supports several forms of availability-group failover: automatic failover, planned manual failover and forced manual failover.
- Next, enables you to configure a given availability replica to support either or both of the following active-secondary capabilities:
- Read-only connection access which enables read-only connections to the replica to access and read its databases when it is running as a secondary replica.
- Performing backup operations on its databases when it is running as a secondary replica.
- Then, supports a flexible failover policy for greater control over availability-group failover.
- After that, supports automatic page repair for protection against page corruption.
- Lastly, supports encryption and compression, which provide a secure, high performing transport.
Always On Failover Cluster Instances (SQL Server)
As part of the SQL Server Always On offering, Always On Failover Cluster Instances leverages Windows Server Failover Clustering (WSFC) functionality to provide local high availability through redundancy at the server-instance level-a failover cluster instance (FCI). An FCI is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. Further, on the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable.
Failover Cluster Instance Overview
An FCI runs in a WSFC resource group with one or more WSFC nodes. When the FCI starts up, one of the nodes assume ownership of the resource group and brings its SQL Server instance online. However, the resources owned by this node include:
- Firstly, Network name
- Secondly, IP address
- Shared disks
- Then, SQL Server Database Engine service
- SQL Server Agent service
- After that, SQL Server Analysis Services service, if installed
- Lastly, one file share resource, if the FILESTREAM feature is installed
However, at any time, only the resource group owner (and no other node in the FCI) is running its respective SQL Server services in the resource group. When a failover occurs, whether it be an automatic failover or a planned failover, the following sequence of events happen:
- Firstly, unless a hardware or system failure occurs, all dirty pages in the buffer cache are written to disk.
- Secondly, all respective SQL Server services in the resource group are stopped on the active node.
- Thirdly, the resource group ownership is transferred to another node in the FCI.
- Next, the new resource group owner starts its SQL Server services.
- Lastly, client application connection requests are automatically directed to the new active node using the same virtual network name (VNN).
Predictable Failover Time
Depending on when your SQL Server instance last performed a checkpoint operation, there can be a substantial amount of dirty pages in the buffer cache. Consequently, failovers last as long as it takes to write the remaining dirty pages to disk, which can lead to long and unpredictable failover time. Beginning with MicrosoftSQL Server 2012 (11.x), the FCI can use indirect checkpoints to throttle the amount of dirty pages kept in the buffer cache. While this does consume additional resources under regular workload, it makes the failover time more predictable as well as more configurable. However, this is very useful when the service-level agreement in your organization specifies the recovery time objective (RTO) for your high availability solution.
Elements of a Failover Cluster Instance
An FCI consists of a set of physical servers (nodes) that contain similar hardware configuration as well as identical software configuration. This includes operating system version and patch level, and SQL Server version, patch level, components, and instance name. However, identical software configuration is necessary to ensure that the FCI can be fully functional as it fails over between the nodes.
WSFC Resource Group
A SQL Server FCI runs in a WSFC resource group. Each node in the resource group maintains a synchronized copy of the configuration settings and check-pointed registry keys to ensure full functionality of the FCI after a failover. And only one of the nodes in the cluster owns the resource group at a time (the active node). The WSFC service manages the server cluster, quorum configuration, failover policy, and failover operations, as well as the VNN and virtual IP addresses for the FCI. In case of a failure (hardware failures, operating system failures, application or service failures) or a planned upgrade, the resource group ownership is moved to another node in the FCI.
SQL Server Binaries
The product binaries are installed locally on each node of the FCI, a process similar to SQL Server stand-alone installations. However, during startup, the services are not started automatically, but managed by WSFC.
Storage
Contrary to the availability group, an FCI must use shared storage between all nodes of the FCI for database and log storage. The shared storage can be in the form of WSFC cluster disks, disks on a SAN, or Storage Spaces Direct (S2D). This way, all nodes in the FCI have the same view of instance data whenever a failover occurs.
Network Name
The VNN for the FCI provides a unified connection point for the FCI. This allows applications to connect to the VNN without the need to know the currently active node. When a failover occurs, the VNN is registered to the new active node after it starts. This process is transparent to the client or application connecting to SQL Server. And this minimize the downtime the application or clients experience during a failure.
Virtual IPs
In the case of a multi-subnet FCI, a virtual IP address is assigned to each subnet in the FCI. During a failover, the VNN on the DNS server is updated to point to the virtual IP address for the respective subnet. Applications and clients can then connect to the FCI using the same VNN after a multi-subnet failover.
Log Shipping (SQL Server)
SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. TFurther, te transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations. Optionally, it raises alerts if these operations fail to occur as scheduled.
Log shipping consists of three operations:
- Firstly, back up the transaction log at the primary server instance.
- Secondly, copy the transaction log file to the secondary server instance.
- Thirdly, restore the log backup on the secondary server instance.
Further, the log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.
SQL Server Backup and Restore with Microsoft Azure Blob Storage Service
SQL Server supports storing backups to the Microsoft Azure Blob storage service in the following ways:
- Firstly, Manage your backups to Microsoft Azure. Using the same methods used to backup to DISK and TAPE, you can now back up to Microsoft Azure storage by specifying URL as the backup destination. Moreover, you can use this feature to manually backup or configure your own backup strategy. This feature is also referred to as SQL Server Backup to URL.
- Secondly, File-Snapshot Backups for Database Files in Azure Blob Storage. Through the use of Azure snapshots, SQL Server File-Snapshot Backups provide nearly instantaneous backups and restores for database files stored using the Azure Blob storage service. However, this capability enables you to simplify your backup and restore policies, and it supports for point in time restore.
- Lastly, let SQL Server Manage backups to Microsoft Azure. Configure SQL Server to manage the backup strategy and schedule backups for a single database. Or it can be several databases, or set defaults at the instance level. This feature is referred to as SQL Server Managed Backup to Microsoft Azure.
Reference: Microsoft Documentation, Documentation 2, Documentation 3, Documentation 4