How to build a multi-domain cluster with AlwaysOn availability groups – SQL Server 2016

Posted in: Microsoft SQL Server, Technical Track

SQL Server 2016 is making lots of news, and promises to be a revolutionary SQL Server version. In talking about AlwaysOn Availability Groups, a very good number of improvements were already announced as part of the Basic Availability Groups. By the way, Hemantgiri, my friend and former Data Platform MVP, already talked about this on his post.

One of the improvements that got my attention was the ability to set up the AlwaysOn Availability Groups sit in a multi-domain cluster, or even a domain-detached cluster. I tested both and indeed this works very well, but the maintenance is slightly different (read: more difficult).

We need to consider the following points:

  • The cluster manager is not supporting this, everything should be done by using PowerShell.
  • To perform the setup, you need to be connected as a local Administrator.
    • The “Administrator” user password should be the same on all involved nodes.
  • For some tasks, like SQL Server upgrade/update, you will need to run the setup using the local administrator account.
    • You don’t need use the Administrator account to log in. Use your regular account, select the Run as different user menu item, and press the SHIFT key while you right-click the file.
      sql_server_screenshot
  • You must be using Windows Server 2016 and SQL Server 2016.

The new capability is a group effort between SQL Server and the Windows development team and is, in my opinion, a very good option. I see the multi-domain capability as a better option than a detached-domain, and I already have customers who can benefit very well from this option.

The following approach is the same; it doesn’t matter if you are making a multi-domain setup or domain-detached cluster; however, there are some particular details:

For Domain-detached cluster

In this particular case, the Failover Cluster is created without any associated computer objects, and for this reason, each involved node needs to have a defined primary DNS suffix.

2

For Multi-domain cluster

Make sure that you can reach all the nodes, using IP, server name, and FQDN. Take care with firewall rules as this may influence the cluster creation. Make sure that the trust relationship between the domains is well defined.

 

Creating the cluster

In my case, I created a multi-domain cluster, which was also a multi subnet one. The following command is the base to create the cluster:

New-Cluster –Name <Cluster Name> -Node <Nodes to Cluster> -StaticAddress <Cluster IP> -AdministrativeAccessPoint DNS

The catch here is the -AdministrativeAccessPoint DNS. Check the PowerShell New-Cluster command for more options.

To perform this command, open the PowerShell console as Administrator (you can either log in as local Administrator or open the console using the method that I explained earlier in this article), and execute the command. That’s it!

In my test laboratory, I created a cluster containing three nodes:

  • SQLCAFE.local (192.100.100.17)
  • LISBON.local (192.100.200.18)
  • SQLCAFE.local (192.100.100.19)

I executed the following command:

New-Cluster –Name W2016CLT01 -Node W2016SRV06.SQLCAFE.local , W2016SRV07.LISBON.local , W2016SRV08.SQLCAFE.local  -StaticAddress 192.100.100.52,192.100.200.52 -NoStorage -AdministrativeAccessPoint Dns

 

After the cluster is created, use PowerShell to perform all the administrative tasks. The following commands are useful:

  • Get-Cluster
  • Get-ClusterNode
  • Get-ClusterGroup
  • Get-ClusterGroup <Group Name> | Get-ClusterResource

3

Creating an Availability Group

The process of creating the availability group is the same as mentioned previously. Actually, we are lucky, because the SQL Server Management Studio has all we need to manage the AG, without the need to touch PowerShell. For basic management, SSMS is enough.

After the cluster is created, you can go ahead and enable the AlwaysOn Availability Groups feature. To perform this, just go the SLQ Server Configuration manager and access the SQL Server engine properties. In the AlwaysOn High Availability tab, select the Enable AlwaysOn Availability Groups check box. Click OK and restart the SQL Server engine.

4

After enabling the feature, just follow the usual procedure and use the wizard in the SSMS, T-SQL, or PowerShell, to create the Availability Group.

 

More Details

As this is a complex configuration, a careful DNS and Quorum configuration is recommended to ensure the highest service availability. As a result, a DNS replication might be needed. For the quorum, the new Cloud Witness (supported from Windows Server 2016) is recommended.

 

Summary

When installed on Windows Server 2016, more flexibility is given to SQL Server 2016, including DTC support for AG, and Cloud Witness. Multi-domain and domain-detached cluster increases the architecture options when using SQL Server. This is just one of the improvements coming with SQL Server 2016. Stay tuned for more news!

 

email

Interested in working with Murilo? Schedule a tech call.

About the Author

Inspired by his father, Murilo has been passionate about IT since he was a child. When he broke their first PC at the age of 10, he was able to fix it on his own without his father finding out. Murilo’s passion for working with people coupled with his honesty have helped him establish effective, meaningful relationships with his clients. His colleagues can depend on him for any system-related issues, especially if it’s configuring a system from scratch. When he isn’t working, he can be found enjoying life with his family and watching football.

2 Comments. Leave new

With an odd number of nodes you already have sufficient quorum using Majority Node Set so you do not need a witness. If you plan to remove votes from nodes this could affect whether a witness needs to be introduced
Regards perry

Reply

While configuring AlwaysOn i get below error.. do i need to create endpoint using certificate.
TITLE: Microsoft SQL Server Management Studio
——————————

The Endpoints tab lists at least one endpoint that uses only Windows Authentication. However, the server instance might be running under a nondomain account. To use the listed endpoint, change the corresponding SQL Server service account to a domain account. To continue using the nondomain account, alter the endpoint to use a certificate.

Do you want to use the listed endpoints?

——————————
BUTTONS:

&Yes
&No
——————————

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *