This article describes considerations for deploying Always On availability groups, including prerequisites, restrictions, and recommendations for host computers, Windows Server failover clusters (WSFC), server instances, and availability groups. For each of these components security considerations and required permissions, if any, are indicated.
Important
Before you deploy Always On availability groups, we strongly recommend that you read every section of this topic.
.Net Hotfixes that Support Availability Groups
Depending on the SQL Server 2017 components and features you will use with Always On availability groups, you may need to install additional .Net hotfixes identified in the following table. The hotfixes can be installed in any order.
Checklist: Requirements (Windows System)
To support the Always On availability groups feature, ensure that every computer that is to participate in one or more availability groups meets the following fundamental requirements:
Important
Also ensure that your environment is correctly configured for connecting to an availability group. For more information, see Always On Client Connectivity (SQL Server).
Recommendations for Computers That Host Availability Replicas (Windows System)
- Comparable systems: For a given availability group, all the availability replicas should run on comparable systems that can handle identical workloads.
- Dedicated network adapters: For best performance, use a dedicated network adapter (network interface card) for Always On availability groups.
- Sufficient disk space: Every computer on which a server instance hosts an availability replica must possess sufficient disk space for all the databases in the availability group. Keep in mind that as primary databases grow, their corresponding secondary databases grow the same amount.
Permissions (Windows System)
To administer a WSFC, the user must be a system administrator on every cluster node.
For more information about the account for administering the cluster, see Appendix A: Failover Cluster Requirements.
Related Tasks (Windows System)
Change the HostRecordTTL (Using Windows PowerShell)
- Open PowerShell window via Run as Administrator.
- Import the FailoverClusters module.
- Use the Get-ClusterResource cmdlet to find the Network Name resource, then use Set-ClusterParameter cmdlet to set the HostRecordTTL value, as follows:Get-ClusterResource "<NetworkResourceName>" | Set-ClusterParameter HostRecordTTL <TimeInSeconds>The following PowerShell example sets the HostRecordTTL to 300 seconds for a Network Name resource named
SQL Network Name (SQL35).Import-Module FailoverClusters $nameResource = "SQL Network Name (SQL35)" Get-ClusterResource $nameResource | Set-ClusterParameter ClusterParameter HostRecordTTL 300TipEvery time you open a new PowerShell window, you need to import the FailoverClusters module.
Related Content (PowerShell)
- Clustering and High-Availability (Failover Clustering and Network Load Balancing Team Blog)
Related Content (Windows System)
SQL Server Instance Prerequisites and Restrictions
Each availability group requires a set of failover partners, known as availability replicas, which are hosted by instances of SQL Server. A given server instance can be a stand-alone instance or a SQL Serverfailover cluster instance (FCI).
In This Section:
Checklist: Prerequisites (Server Instance)
Thread Usage by Availability Groups
Always On availability groups has the following requirements for worker threads:
- On an idle instance of SQL Server, Always On availability groups uses 0 threads.
- The maximum number of threads used by availability groups is the configured setting for the maximum number of server threads ('max worker threads') minus 40.
- The availability replicas hosted on a given server instance share a single thread pool.Threads are shared on an on-demand basis, as follows:
- Typically, there are 3-10 shared threads, but this number can increase depending on the primary replica workload.
- If a given thread is idle for a while, it is released back into the general SQL Server thread pool. Normally, an inactive thread is released after ~15 seconds of inactivity. However, depending on the last activity, an idle thread might be retained longer.
- A SQL Server instance uses up to 100 threads for parallel redo for secondary replicas. Each database uses up to one-half of the total number of CPU cores, but not more than 16 threads per database. If the total number of required threads for a single instance exceeds 100, SQL Server uses a single redo thread for every remaining database. Serial Redo threads are released after ~15 seconds of inactivity.
NoteDatabases are chosen to go single-threaded based on their ascending database ID. As such, the database creation order should be considered for SQL Server instances that host more availability group databases than available worker threads. For example, on a system with 32 or more CPU cores, the first six databases (ordered by database ID) in an availability group or groups will use parallel redo mode, and all subsequent databases will use single redo mode. - In addition, availability groups use unshared threads, as follows:
- Each primary replica uses 1 Log Capture thread for each primary database. In addition, it uses 1 Log Send thread for each secondary database. Log send threads are released after ~15 seconds of inactivity.
- A backup on a secondary replica holds a thread on the primary replica for the duration of the backup operation.
For more information, see Always On - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases (a CSS SQL Server Engineers Blog).
Permissions (Server Instance)
Related Tasks (Server Instance)
Related Content (Server Instance)
Network Connectivity Recommendations
We strongly recommend that you use the same network links for communications between WSFC nodes and communications between availability replicas. Using separate network links can cause unexpected behaviors if some of links fail (even intermittently).
For example, for an availability group to support automatic failover, the secondary replica that is the automatic-failover partner must be in the SYNCHRONIZED state. If the network link to this secondary replica fails (even intermittently), the replica enters the UNSYNCHRONIZED state and cannot begin to resynchronize until the link is restored. If the WSFC requests an automatic failover while the secondary replica is unsynchronized, automatic failover will not occur.
Client Connectivity Support
For information about Always On availability groups support for client connectivity, see Always On Client Connectivity (SQL Server).
Prerequisites and Restrictions for Using a SQL Server Failover Cluster Instance (FCI) to Host an Availability Replica
In This Section:
Restrictions (FCIs)
Note
Failover Cluster Instances supports Clustered Shared Volumes (CSV). For more information on CSV, see Understanding Cluster Shared Volumes in a Failover Cluster.
- The cluster nodes of an FCI can host only one replica for a given availability group: If you add an availability replica on an FCI, the WSFC nodes that are possible FCI owners cannot host another replica for the same availability group. To avoid possible conflicts, it is recommended to configure possible owners for the failover cluster instance. This will prevent potentially causing a single WSFC from attempting to host two availability replicas for the same availability group.Furthermore, every other replica must be hosted by an instance of SQL Server 2016 that resides on a different cluster node in the same Windows Server failover cluster. The only exception is that while being migrated to another cluster, an availability group can temporarily straddle two clusters.
Warning
Using the Failover Cluster Manager to move a failover cluster instance hosting an availability group to a node that is already hosting a replica of the same availability group may result in the loss of the availability group replica, preventing it from being brought online on the target node. A single node of a failover cluster cannot host more than one replica for the same availability group. For more information on how this occurs, and how to recover, see the blog Replica unexpectedly dropped in availability group.
- FCIs do not support automatic failover by availability groups: FCIs do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can be configured for manual failover only.
- Changing FCI network name: If you need to change the network name of an FCI that hosts an availability replica, you will need to remove the replica from its availability group and then add the replica back into the availability group. You cannot remove the primary replica, so if you are renaming an FCI that is hosting the primary replica, you should fail over to a secondary replica and then remove the former primary replica and add it back. Note that renaming an FCI might alter the URL of its database mirroring endpoint. When you add the replica ensure that you specify the current endpoint URL.
No comments:
Post a Comment