SQL Server Always On Availability Groups is Microsoft’s latest technology for addressing the High Availability and Disaster Recovery needs of organizations that use SQL Server. One big advantage of AlwaysOn is the ability to address both HA and DR in one implementation. We experienced the following key benefits of AlwaysOn:
-
We can group related databases as part of a single Availability Group and have them failover together in case this is needed. This is especially useful for applications that depend on more than one database, such as Microsoft Office SharePoint, Microsoft Lync, and Sage.
-
When compared to SQL Server Failover Cluster Instances, we find that storage as a single point of failure has been eliminated since each instance which constitutes a replica is assigned its own storage.
-
With AlwaysOn, it is possible to configure HA and DR at once. This is achieved by creating a Multi-site Windows Failover Clusters as the foundations of your AlwaysOn configuration. Performing a Role Switch when using AlwaysOn is significantly simpler than doing it when using Transaction Log Shipping.
Computer Objects
Active Directory (AD) is a huge subject and we won’t delve into the details in this article. As you can guess, Active Directory is Microsoft’s directory service. In computer networking terms, a directory service is a facility that allows us to register, identify, and manage all objects within a network centrally. Entries in the directory services map the names of network devices to corresponding IP Addresses and other properties on the directory. The most common objects in a directory service (and in Microsoft’s AD) are Users and Computers. Just as each user on the domain can be registered and managed from Active Directory, every computer on the domain can also be managed from Active Directory. Just as each user is expected to have a unique account in Active Directory, so is each computer.
In Active Directory, not all Computer Objects map to a physical computer. A Computer Object can represent a physical computer (work station or server) but can also represent something that acts like a computer such as the representative name for a Windows Cluster or the virtual name for a Cluster Service (Role). Such representations are also unique in Active Directory just like regular computer names.
CNOs and VNOs in WSFC
When you install a Windows Failover Cluster, the installer creates an entity in Active Directory called a Computer Name Object (CNO). This CNO is the primary entity created in Active Directory for the cluster and represents the “Server Name” of the entire cluster. Subsequently, other objects known as Virtual Name Objects are created by the CNO when performing such activities as creating Cluster Roles, Availability Groups, or Availability Group Listeners. These CNOs and VNOs have IP Addresses associated with them. You can specify these addresses when installing the cluster or creating a new Cluster role or a Listener. For each cluster, role, and listener you create, you need a unique computer name and a unique IP Address. Fig. 1 shows the step during which you specify the Cluster Name Object and its IP Address when configuring a cluster.
The names you use when configuring a cluster are completely arbitrary. They just need to be unique. However, it is advised to follow your organization’s naming conventions for regular computers when creating CNOs or VNOs – this helps keep management easier. It also makes sense to use a specific block of IP Addresses which will cover all the addressing needs for your entire cluster – the CNO and all VNOs (roles) you intend to create.
Fig. 1 Name to Address Mapping in a Cluster
The Key Domain Permissions
The DBA or System Admin performing a cluster installation must have a permission to Create Computer Objects in the Active Directory domain. In turn, after creating the Computer Name Object, the domain administrator must grant the following permissions to the Computer Name Object so that roles (which result in Virtual Name Objects) can be successfully created in the Cluster:
-
Create Computer Objects
-
Read All Properties
Without these permissions, you are likely to get error messages similar to the one below when attempting to create a role (in the case of AlwaysOn FCI) or a Listener (in the case of AlwaysOn AG):
Error during MS SQL Server Cluster installation:
Cluster network name resource ‘SQL Network Name (EUK-SCCM-01)’ failed to create its associated computer object in domain ‘domainname.com’ for the following reason: Unable to create computer account.
The text for the associated error code is: Access is denied.
This error message is observed in Event Viewer since SQL Server would not be accessible at this time. You will also be able to see this if you navigate to the SQL Error Log files in the LOG folder which sits in SQL Server’s installation directory. The key phrase is “Access is Denied”.
Other Requirements
I should mention the concept of a Domain Controller. A domain controller, or more precisely, a set of domain controllers provide key services for Active Directory such as registering objects and authenticating users and computers. In order to successfully configure a cluster or an AlwaysOn Listener, a domain controller must be reachable from the computer where you are performing the configuration. This means communication from the computer to the domain controller must be opened on a range of TCP and UDP ports. Microsoft documents this in great detail in this article. This might be a given in most cases, but when troubleshooting connectivity issues, it helps to know what is actually needed.
In a previous article, I also mentioned that it is necessary to grant permissions to the CNO of a cluster when configuring a File Share Quorum.
Fig. 2 Permissions on a File share
A Note on Name Resolution
Being Computer Objects, CNOs and VNOs depend on Domain Name Service to resolve the name indicated when installing the cluster, creating a role, or creating an AlwaysOn Listener. Typically clients are given these computer names to establish a connection to the cluster. In other words, the IP Address is transparent to them, making client configuration simple and abstracting failovers from the end users.
Fig. 3 AG Listener Properties for Listener with Two IP Addresses
In a previous article, I mentioned a case where this scenario can cause problems. In our multi-site cluster, we had one listener for our AlwaysOn Availability Group. This listener was configured to resolve to two IP Addresses. This is necessary for a multi-site cluster spanning multiple subnets. In such a configuration, the name server will return both IP Addresses mapped to the listener upon issuing an nslookup check (see Fig. 4). However, when connecting, you can access only one of those IP Addresses at a time. Cluster Manager will show the other IP Address as Offline (see Fig. 5).
Fig. 4 AG Listener Properties for Listener with Two IP Addresses
Fig. 5 Properties for Cluster Role with Two IP Addresses in separate Subnets
This is normal. If there is a failover to the alternate site, the DNS Server begins resolving the alternate IP Address after a few minutes. The implication of this is that the clients’ communication with the alternate site must be possible. Fig. 6 and Fig 7 highlight this further.
Fig. 6 Communication Path with Primary Replica in Dakar
Let’s take a good look at the path that the packets will traverse from the client computers to the cluster. When the Primary Replica is in Dakar, the Listener name SQL-SVR-LNR is resolved to the IP address 192.168.1.20 and WFCS, in its turn, directs the request to the server 192.168.1.22 (note that this server also has its own computer name). In the event of a failover to Nairobi, we have the communication path going through 192.168.2.20 and then to 192.168.2.22. The implication is that for seamless customer experience, all clients in all data centers must have communication allowed on all firewalls involved using port 1433.
Fig. 7 Communication Path with Primary Replica in Nairobi
Conclusion
While Windows Failover Clustering, Active Directory, and Domain Name Service may be outside the role of the Database Administrator, it pays to have a basic understanding of how these technologies work to be able to build and troubleshoot AlwaysOn Failover Cluster Instances and AlwaysOn Availability Groups. Some organizations have a strict separation of duties between System Administrators and Databases Administrators, but where this is not the case, the Database Administrator needs to be able to wrap their head around these Windows concepts in order to succeed as a DBA.
