What is Always On SQL Server?

Enterprises are constantly exploring the number of ways in which they can secure their data in SQL Server and avail high availability. This is where the Always On SQL Server comes in. Always On SQL Server refers to the High Availability and Disaster Recovery solution introduced in the 2012 version.

Always On availability groups increase the availability of a set of user Databases for a business enterprise. Proving to be an alternative to Database Mirroring, Always On SQL Server basically extends a failover environment for Availability Databases that failover together.

What is the need for SQL Server, and where can it be implemented?

Many businesses rely on data for making informed decisions, and for this data to be valuable, it must be securely and efficiently stored. SQL Server is a robust, dependable, and secure relational database management system (RDBMS) widely employed by enterprises to store and manage their data.

So, what drives the need for SQL Server, and where should it be deployed?

Firstly, let’s examine the necessity for SQL Server. It is a potent and dependable RDBMS used by businesses of all sizes, boasting scalability to handle substantial data volumes and user-friendly features. SQL Server ensures a secure environment through built-in security measures, encryption, and data backup/recovery options. Its support for various languages, including T-SQL, makes it suitable for diverse applications.

Now, let’s consider where SQL Server should be added. The most common deployment is on an internal server, providing on-site data management with the flexibility and scalability required. Cloud hosting has gained popularity due to cost savings and scalability, which is particularly beneficial for businesses lacking the resources for an in-house server. A hybrid environment, combining on-site and cloud-based resources, offers flexibility and security, catering to businesses with diverse hosting needs.

SQL Server is a versatile RDBMS suitable for on-site, cloud, or hybrid deployment. Its scalability and feature-rich nature make it ideal for various applications, and its security measures ensure secure data storage and management.

Is SQL Server advantageous for users?

Certainly, SQL Server provides users with many benefits, making it an attractive choice for businesses of all sizes. Let’s explore some key advantages:

• Comprehensive Database Features: SQL Server offers stored procedures, triggers, and views, enabling users to customize databases. It supports traditional and new technologies like XML, simplifying data storage and querying.
• High Performance: Optimized for fast query results, SQL Server ensures quick and efficient data access. Its support for in-memory and disk-based technologies enhances flexibility and scalability.
• Security Features: With enterprise-level security features, including encryption, authentication, and access controls, SQL Server provides a secure environment. Database mirroring ensures data backup in case of system failure.
• Developer-Friendly: Designed to be developer-friendly, SQL Server provides tools and utilities for easy database creation and maintenance. A user-friendly query language simplifies the development process.
• Cost-Effective: SQL Server offers cost-effective pricing options in both on-premises and cloud-based versions. Its scalability and flexibility allow businesses to upgrade their database systems as needed.

In conclusion, SQL Server’s security measures, comprehensive features, developer-friendly design, high performance, and cost-effectiveness make it an excellent solution for businesses needing a powerful and reliable database system.

What is SQL Server AlwaysOn Availability Group?

An Availability Group provides a replicated environment for a specific set of user Databases called Availability Databases. This replicated environment comprises a set of read-write Primary Databases and corresponding sets of Secondary Databases. An Availability Group can be configured for either High Availability (HA) or read-scale. An HA Availability Group comprises Databases that failover at the same time.

The Always On Availability Group is a High Availability and Disaster Recovery feature. It doesn’t require shared disk storage for hosting the SQL Server. However, the Always On Availability Group is supported only by the Enterprise edition. The diagram below depicts an implementation of Always On SQL Server Availability Group for High Availability and Disaster Recovery.

Asymmetric storage refers to a cluster disc shared only by a subset of the nodes. Asymmetric disc capability enables a disc witness to be set up and accessible only to nodes in a single site, the primary site generally.

Setting up a SQL Server Instance to Enable Always On Availability Groups

Activate Always On availability groups by ensuring that each instance of SQL Server intending to join an availability group has the feature enabled. Before enabling, ensure the host computer meets the prerequisite of being a Windows Server Failover Clustering (WSFC) node.

Establish a database mirroring endpoint if not already created. Confirm that each server instance has a dedicated database mirroring endpoint, which is utilized for receiving connections from other server instances participating in Always On availability groups.

Creating and Setting Up a New Availability Group

• Availability group creation: Creating it on the SQL Server instance hosted by the target databases. At a minimum, designate the initial primary replica on the same SQL Server instance where the availability group is being created. Optionally, you can specify between one to four secondary replicas. It is highly recommended to set up an availability group listener.

Note: Before proceeding, ensure that the SQL Server instances hosting availability replicas for a specific availability group are on separate nodes within a single WSFC cluster. There is an exception during migration, where an availability group can temporarily span two clusters until the migration is complete.

• Associate secondary replicas with the availability group by establishing connections to each SQL Server instance hosting a secondary replica. Then, integrate the local secondary replica into the availability group.
• Set up secondary databases by restoring backups of the primary databases on each server instance hosting a secondary replica. Use the RESTORE WITH NORECOVERY option during the restoration process.
• Connect secondary databases to the availability group by linking each local secondary database to the availability group on every server instance hosting a secondary replica. Once joined to the availability group, each secondary database will commence data synchronization with its corresponding primary database.
• Establishing an availability group listener is a crucial task unless you previously configured the availability group listener during the initial creation of the availability group.
• Provide application developers with the DNS hostname of the listener, which they must include in their connection strings to route connection requests to the availability group.
• Configuration for the location of backup jobs. To enable backups on secondary databases, generate a backup job script considering automated backup preferences. Develop a script for each database within the availability group, covering all server instances hosting an availability replica for the group.

Let’s take a look at the different components of SQL Server Always On Availability Groups:

• Availability Databases
• Availability Replicas
• Availability Modes

Availability Databases

For a Database to be eligible to be added to an Availability Group, it must be an online, read-write Database existing on the server instance that hosts the primary replica. After adding a Database into an Availability Group, it becomes a Primary Database while being accessible to clients. Corresponding Secondary Databases cease to exist until backups of the new Primary Databases are restored to the server hosting the secondary replica. The new Secondary Database remains to be in the RESTORING state until it is added to the Availability Group.

After the Secondary Database is joined to the Availability Group, it goes into the ONLINE state and initiates the process of Data Synchronization with the corresponding Primary Database. Data Synchronization refers to the process of reproducing the changes from a Primary Database to a Secondary Database. The Primary Database sends transaction log records to the Secondary Database during Data Synchronisation.

Availability Replicas

Each Availability Group has 2 or more failover partners known as availability replicas. The Availability Databases in the Availability Group are replicated in the Availability Replicas. The Availability Replicas of a specific Availability Group must be hosted by separate instances of SQL Server running on different WSFC cluster nodes. Always On must be enabled for each of these server instances.

Each server instance can host only one Availability Replica per Availability Group. However, a given server instance can be used for many Availability Groups. A SQL Server instance can be either a stand-alone or a Failover Cluster Instance (FCI).

Each Availability Replica is given either a primary role or a secondary role, which is inherited by that replica’s Availability Databases. Depending on the role, it is determined whether a replica hosts read-write Databases or read-only Databases. The primary role is assigned to the Primary Replica which hosts read-write Databases (also known as Primary Databases), whereas the secondary role is assigned to at least one other replica, known as a Secondary Replica. The Secondary Replica hosts read-only Databases (also known as Secondary Databases).

Availability Modes

The Availability Mode is a property of every Availability Replica. Based on the Availability Mode it is determined whether the Primary Replica waits to commit Database transactions until the transaction log records have been written to the disc by a specific Secondary Replica. Always On SQL Server Availability Groups support 2 Availability Modes: Asynchronous-commit Mode and Synchronous-commit Mode.

• Asynchronous-commit Mode: Asynchronous-commit Replica uses the Asynchronous-commit Mode. The Primary Replica performs transactions without waiting for acknowledgment from Asynchronous-commit Secondary Replicas to harden their transaction logs in Asynchronous-commit Mode.
  
  The Asynchronous-commit Mode reduces transaction latency on Secondary Databases while allowing them to lag behind Primary Databases, potentially leading to data loss.

• Synchronous-commit Mode: Synchronous-commit Replica uses the Synchronous-commit Mode. A Synchronous-commit Primary Replica waits for acknowledgment from Synchronous-commit Secondary Replicas to harden their transaction logs before committing transactions in Synchronous-commit Mode.
  
  Synchronous-commit Mode makes sure that the committed transactions are fully secure once a given Secondary Database is synchronized with the Primary Database. However, this protection comes at the cost of increased transaction latency.

Managing Availability Groups, Replicas and Database

Managing existing availability groups includes performing one or more of the subsequent activities:

• Adjust the flexible failover policy for the availability group to manage the conditions triggering an automatic failover. This policy applies exclusively to situations where automatic failover is viable. 
• Execute either a planned manual failover or a forced manual failover, also known as a forced failover, which may involve potential data loss. 
• Utilize a predefined set of policies to monitor the availability group’s health, along with its replicas and databases. 
• Add or remove a secondary replica, suspend or resume an availability database. 
• Suspending a secondary database maintains it at its present state until resumed.
• Additionally, you can add or remove a database, reconfigure or establish an availability group listener, delete an availability group, and troubleshoot add file operations. 
• Such troubleshooting may become necessary when the primary and secondary databases have distinct file paths. 
• Finally, modify availability replica properties as needed.

Monitoring Availability Groups

The following tools are available for monitoring the properties and status of an Always On availability group.

Tool	Description
System Center Monitoring Pack for SQL Server	SQLMP – The SQL Server Monitoring Pack serves as the recommended solution for IT administrators to monitor availability groups, replicas, and databases. Key features relevant to Always On availability groups include The automatic discovery of availability groups, replicas, and databases across hundreds of computers, facilitates easy tracking of Always On availability groups inventory.SCOM- System Center Operations Manager: Alerting and ticketing for faster problem resolution.Health roll-ups from availability databases to availability replicas.Custom tasks within the System Center Operations Manager console for managing Always On availability groups.
Transact-SQL	The Always On availability groups catalog and dynamic management views in Transact-SQL provide extensive information about availability groups, replicas, databases, listeners, and the WSFC cluster environment.
SQL Server Management Studio	The Properties dialog boxes in SQL Server Management Studio allow you to view and, in some cases, modify the properties of availability groups, replicas, or listeners.
System Monitor	Availability replicas information is gathered from SQLServer: Availability Replica performance object in System Monitor features
System Monitor	The information about secondary databases on a given secondary replica is gathered from the database replica performance object of SQLServer. The Databases object of SQLServer monitors transaction log activities with relevant counters such as Log Flush Write Time (ms), Log Flushes/sec, Log Pool Cache Misses/sec, Log Pool Disk Reads/sec, and Log Pool Requests/sec.

Benefits of an Always On SQL Server

There are numerous benefits of deploying Always On Availability Groups in the SQL Server, let’s take a look at some of them.

• Always On SQL Server Availability Groups support up to 9 Availability Replicas. Breaking this down, every Availability Group supports 1 Primary Replica and up to 8 Secondary Replicas.
• Always On Availability Group supports different forms of Availability Group Failover, namely Automatic Failover, Planned Manual Failover, and Forced Manual Failover.
• Always On SQL Server ensures your data is always available, no matter what happens.
• It can improve the performance of your IT environment with faster access to data.
• Always On SQL Server is scalable and can easily grow with your organization.
• It requires less hardware and maintenance costs than a traditional database. This will help your organization save money.
• It supports Automatic Page Repair to prevent page corruption.
• It provides secure, high-performing transport of data as it readily supports encryption and compression.
• One can easily monitor the Always On SQL Server Availability Groups on the Always On Dashboard. It further monitors the Availability Replicas and Availability Databases and evaluates results for Always On policies.

Types of Failover

An availability group fails over to the level of an availability replica. Database difficulties, such as losing a data file, deleting a database, or corrupting a transaction log, do not cause failovers.

There are three types of failovers: automatic, manual, and forced (with possible data loss). The form or forms of failover enabled by a given secondary replica are determined by its availability mode and, for synchronous-commit mode, the failover mode on the primary and target secondary replicas, as shown below.

Planned manual failover (without data loss)

A manual failover happens when a database administrator issues a failover command, which causes a synchronized secondary replica to change to the primary role and the primary replica to switch to the secondary role. A manual failover requires both the primary and secondary replica to be in synchronous-commit mode, with the secondary replica already synchronized.

Automatic failover (without data loss)

An automatic failover occurs when a failure causes a synchronized secondary replica to take over the primary role. The former primary replica switches to the secondary role when it becomes available. Both the primary replica and the secondary replica must be running in Automatic failover synchronous-commit mode. Additionally, the secondary replica must be synchronized, have a WSFC quorum, and match the constraints provided by the availability group’s flexible failover policy.

Forced Manual Failover (with possible data loss)

Forced manual failover (with possible data loss) or forced failure is the only form of failover under asynchronous commit mode. It is considered a type of manual failover, as you can only initiate it manually. Forced failover is used as a disaster recovery option. It is the only failover that can occur when the target secondary and primary replicas aren’t synchronized.

Understanding Always On Failover Clustering Instances

Always On FCI requires shared storage, such as an iSCSI or Fibre Channel SAN, that can be accessed by all nodes in the cluster. However, you can also do this over Cloud or for virtual machines by using third-party Data Replication technologies. It allows multisite clustering across subnets, allowing SQL Server instances to fail over across Data Centres. However, this requires Data Replication across shared storage in every Data Centre.

Available on both SQL Server Standard and Enterprise Edition, Always On FCI imposes restrictions on SQL Server Standard Edition such as a 2-node limit. While installing SQL Server, you should select the “New SQL failover cluster installation” option. The diagram below depicts the implementation of a single-site two-node AlwaysOn FCI.

Elements of a Failover Cluster Instance (FCI)

One of the most significant technologies for high-availability deployments is failover cluster instances (FCIs). FCIs offer the highest level of security against outages and crashes, ensuring that your applications and services are available and protected even if the system fails.

To fully utilize a failover cluster, several pieces must be in place. Here’s a look at the primary components you should consider while establishing an FCI:

• WSFC Resource Group: The first component is the Windows Server Failover Cluster (WSFC) resource group. This resource group manages the cluster’s resources, such as computer and storage components.‍
• SQL Server Binaries: This is the software that handles the essential database functions, like query processing and transaction management. To maintain the database’s availability and security, SQL Server binaries must be on each cluster node.

• Storage: It is critical to select a storage system that is highly available and offers redundancy and failover capabilities, such as a SAN or NAS.
• Network Name: This is the network cluster’s name, for example, “server-cluster-1”. Clients need this name to find and connect to the cluster. Therefore, you should use a unique name that does not clash with other network resources.
• Virtual IPs: It is crucial to select IPs that would not conflict with other resources on the network.

Characteristics of Always On FCI and Always On AG

Both the technologies serve different purposes. However, you can combine Always On FCI and Always On AG as per your requirement of High Availability and Disaster Recovery. Some of your tasks may require local High Availability within a Data Center via Always On FCI and cross Data Center Disaster Recovery via Always On AG. This means the solution would hold a combination of shared storage and non-shared storage.

The below table summarizes the characteristics of Always On SQL Server AG and FCI to help you understand both the solutions.

Always On FCI	Always On AG
Shared Storage solution	Non-Shared Storage solution
Supports Instance-level HA	Supports Database-level HA
Provides Instance-level protection without data redundancy.	Secondary AG Database(s) are redundant copies of Primary Databases.
Have ActivePassive nodes. No concept of a secondary database.	DR replica can be Active Secondary for the backup, read-only workload.
The application connects via a virtual server name.	The application connects via the AG listener name.
A redundant copy of the data is not stored, hence it does not protect against an I/O subsystem failure.	Automatic Page Repair provides protection against an I/O subsystem failure.

Practical Distinctions: FCIs vs. AGs

Here are the factors to consider while deciding between an Always On Availability Group (AG) or an Always On Failover Clustering Instance (FCI) for your always-on SQL Server:

1) Type of Availability Solution

FCIs are intended to offer high availability for SQL Server instances, whereas AGs are meant to provide disaster recovery for databases.

If you prioritize minimizing downtime and maintaining database availability, an FCI may be the best option. An AG may be preferable if you are primarily concerned with data security and disaster recovery.

2) Number of Copies of the Database

FCIs include two database copies, whereas AGs can have several copies. If you need to make several copies of your databases for disaster recovery, an AG could be a better option.

3) Automatic Failover

FCIs and AGs both provide automatic failover, allowing the secondary instance to take over if the primary instance or replica goes down.

FCIs only enable automated failover inside a cluster, but AGs can provide failover across different clusters or servers.

4) Data Protection Level

FCIs offer excellent levels of data security since they use a synchronous data replication mechanism and store several copies of the database.

AGs offer great data security when set in synchronous commit mode but may not be as effective as FCIs in asynchronous commit mode.

Client Connections

Creating an availability group listener allows you to connect clients to the primary replica of a specific availability group. An availability group listener is a collection of resources associated with a specific availability group that guides client connections to the appropriate availability replica.

One or more virtual IP addresses (VIPs), a TCP port number, and a unique DNS name that functions as a virtual network name (VNN) are linked to an availability group listener. 

Active Secondary Replicas

Always On SQL Servers supports active secondary replicas. These include support for:

Performing Backups on Secondary Replicas

Secondary replicas are capable of making log backups as well as copy-only backups of an entire database, file, or filegroup. You can set the availability group’s choice for where backups should be done. It’s crucial to note that SQL Server doesn’t enforce the option therefore, it has no effect on ad hoc backups. The interpretation of this option is determined by the logic scripted into your back jobs for each database in a certain availability group. You can determine the priority of executing backups on an individual availability replica in comparison to other replicas in the same availability group.

Read-only Access to One or More Secondary Replicas

You can configure any active secondary replica to only allow read-only access to its local databases, though some operations are not completely supported. This restricts read/write connections to the secondary replica. It is also feasible to avoid read-only workloads on the primary replica by limiting read-write access.

Session-timeout Period

This period refers to an availability-replica property that specifies how long a connection to another available replica can be idle before being closed. The primary and secondary replicas ping each other to confirm that they are still active. Receiving a ping from the second replica during the timeout period confirms that the connection is still active and the server instances are talking. When an availability replica receives a ping, it resets the session-timeout counter associated with that connection.

The session-timeout period ensures that neither replica waits indefinitely for a ping from the other. If the other replica does not respond to a ping during the session-timeout interval, the replica times out. Its connection is terminated, and the timed-out replica moves to the DISCONNECTED state. Even if a disconnected replica is set to synchronous-commit mode, transactions will not wait for the replica to reconnect and resynchronize.

Each available replica has a default session timeout of 10 seconds. This value can be set by the user and must be at least 5 seconds. It is recommended to set the time-out period to 10 seconds or more. Setting the number to less than 10 seconds increases the likelihood of a heavily loaded system reporting a fake failure.

Automatic Page Repair

All availability replicas attempt to automatically retrieve from corrupted pages on a local database by resolving specific errors that prevent reading a data page. If a secondary replica is unable to read a page, it requests a new copy of the page from the primary replica. If the primary replica is unable to read a page, the replica sends a request for a new copy to all secondary replicas. If this request is successful, the unreadable page is replaced with a copy, which usually resolves the issue.

Compatibility and Integration with Other Databases Engine Features

The Always On availability groups are compatible with various features and components within SQLServer, including:

• Change Data Capture
• Change Tracking
• Contained databases
• Database encryption
• Database snapshots
• FILESTREAM
• FileTable
• Log shipping
• Remote Blob Store (RBS)
• Replication
• Service Broker
• SQL Server Agent
• Reporting Services

Conclusion

Always On SQL Server is a High Availability and Disaster Recovery solution introduced in SQL Server 2012. It increases the availability by providing a failover environment for Availability Databases that failover together. This article introduced you to MS SQL Server and later took you through various aspects of Always On SQL Server.

As you collect and manage your data across several applications and Databases in your business, it is important to consolidate it for a complete performance analysis of your business. However, it is a time-consuming and resource-intensive task to continuously monitor the Data Connectors. This is where Hevo comes in.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

visit our website to explore hevo

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of understanding Always On SQL Server in the comments section below.