Sign in

    Walkthrough: Setting up SQL Server High Availability

    By: Spotlight Cloud

    In today’s technology-driven world, there are few parts of our lives that don’t depend on software and IT-related functions. As we have become more reliant on technology, we have become less tolerant of performance issues in the applications we use regularly.

    One of the most critical performance criteria we have for applications is that we want them to always be available when we need them. Server and database downtime are unacceptable in today’s competitive business environment. To ensure users can access applications with little to no interruption, DBAs and IT teams must stay focused on maintaining high availability for their SQL Server databases.

    What Is SQL Server High Availability?

    SQL Server high availability is the practice of keeping SQL Server databases and instances up and running with minimal downtime in the event of a crash or failure. Many business applications rely on databases being up and running, so when databases are down, users get frustrated and you may lose their business. 

    High availability is especially crucial for mission-critical applications and e-commerce sites where downtime can have an immediate negative effect on welfare or revenue.

    SQL Server has several high availability technologies, including log shipping, transactional replication, and data mirroring, but for the sake of brevity, the focus of this post is how to achieve high availability using SQL Server Always On availability groups.

    What Are SQL Server Always On Availability Groups?

    Always On availability groups support a replicated environment for a defined set of availability databases that fail over together to an availability replica. 

    As explained in the Microsoft SQL Server documentation, “an availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases.”

    You also have the option of making the secondary databases available for read-only access and some backup operations. However, it’s important to note that secondary databases are not backups. You should continue to back up your databases and their transaction logs regularly.

    Benefits of SQL Server Always On Availability Groups

    SQL Server Always On availability groups provide numerous benefits that help organizations maintain high availability, including:

    Availability replicas: The availability replicas are hosted by a specified SQL Server instance, which maintains a local copy of each availability database belonging to the availability group. Each availability group can support one primary replica and as many as eight secondary replicas. 

    Alternative availability modes: Always On availability groups support two types of alternative availability modes: asynchronous-commit and synchronous-commit. 

    The asynchronous-commit mode is a good disaster recovery option when the availability replicas are highly distributed. 

    Synchronous-commit mode provides high availability and data protection, but can affect performance. An availability group can support up to three synchronous-commit availability replicas, including the primary.

    Availability-group failovers: Always On availability groups provide several forms of failovers and failover modes with varying probability of data loss: automatic failover (no data loss), planned manual failover (no data loss), and forced manual failover (possible data loss). 

    Availability group listener: Each Always On availability group provides an availability group listener, which is a server clients can connect to in order to access a database in the availability group’s primary or secondary replica. Listeners direct incoming connections to the primary or secondary replica, allowing fast application failover when an availability group fails.

    Security features: SQL Server Always On availability groups support automatic page repair to protect against page corruption as well as encryption and compression for secure, high-performing transport.

    Prerequisites and Restrictions for Always On Availability Groups

    Before deploying SQL Server Always On availability groups, it’s important to understand the prerequisites and restrictions associated with them. You can find a full discussion on SQL Server Always On availability groups in Microsoft’s SQL Server documentation, but we touch on some of the highlights here: 

    Availability Group Requirements and Restrictions

    The process of setting up availability groups is governed by certain prerequisites and restrictions. You can learn more about the specifics (and how setting up a virtual environment can help circumvent some of the restrictions) here:

    Availability Database Requirements and Restrictions

    Before adding a database to an Always On availability group, you need to be sure the database meets the requirements and restrictions outlined here:

    SQL Server Instance Prerequisites and Restrictions

    Availability groups fail over to a defined set of availability replicas that are hosted by SQL Server instances. These instances must be configured appropriately in order to effectively host the availability replicas. These resources provide specifics on the necessary prerequisites, thread usage requirements, and permissions needed: 

    Using a SQL Server Failover Cluster Instance to Host an Availability Replica

    A SQL Server failover cluster instance is a single SQL Server instance that is installed across Windows Server Failover Clustering nodes and provides recovery at the database level. There are some important things to know about hosting an availability replica on a failover cluster instance, so be sure to review the checklist and restrictions carefully:

    Getting Started with Always On Availability Groups

    Once the prerequisites and restrictions are sorted, you can move ahead with setting up and deploying SQL Server Always On availability groups. There are four primary steps in the initial setup and configuration of availability groups, each with several subtasks. The steps are listed below with the full details available in the Microsoft SQL Server documentation:

    1. Configure an instance of SQL Server to support Always On availability groups
    2. Create and configure a new availability group
    3. Manage availability groups, replicas, and databases
    4. Monitor availability groups

    SQL Server Always On availability groups are a straightforward, reliable way to maintain high availability for your enterprise. Always On availability groups maximize your database availability and provide users with the seamless, dependable application experience they demand.

    New call-to-action

    January 28, 2020 8:00:00 AM PST
    Spotlight Cloud

    Written by Spotlight Cloud

    Understand your SQL Server infrastructure health at a glance by monitoring SQL Server's relational engine, Analysis Services, Windows, VMware ESX and SQL Azure with Spotlight.