Rajendra Gupta
database master key

Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster

August 13, 2020 by

This article continues from the series on SQL Server Always On Availability Group. This is the 11th article in this series.

Introduction

In the previous article, Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups, we learned the new capability in Windows Server 2016 to configure a domain-independent Windows failover cluster. We did the following steps in the previous article.

  • Configure the primary DNS suffix on both SQLAG1 and SQLAG2
  • Configured a domain-independent Windows failover cluster

In this article, we will configure an Availability Group on the domain-independent cluster.

Configure a domain-independent SQL Server Always On Availability Group

Step 1: Install SQL Server 2019 on SQLAG1 and SQLAG2 using the built-in service account

First, you should install the SQL Server database engine on all nodes participating in the failover cluster. I do not cover all steps in SQL Server 2019 installation in this article, and you can explore the previous article in the series for detailed instructions.

As you know, we do not have an active directory configured for both nodes in my failover cluster. You should use the built-in service accounts(NT Service). As per best practice, you should grant volume maintenance task privilege for the SQL Server Database engine service, as shown below.

Install SQL Server 2019

Step 2: Enable Always On Availability Group feature on SQLAG1 and SQLAG2

Open the SQL Server Configuration Manager and put a tick on the Enable Always On Availability Group. You can see the name of the domain-independent failover cluster on this page. You must restart SQL Services to make the changes effective.

Enable Always On Availability Group feature

Similarly, enable the Always-on availability groups on the SQLAG2 node and restart SQL Services.

Enable Always On Availability Group feature on SQLAG2

Step 3: Create a database master key on the primary replica SQLAG1

A domain-independent uses the database master key for SQL Server Always On Availability Group. You require a password to encrypt the database master key.

Image Reference: Microsoft docs

database master key

We configure the SQLAG1 as the primary replica for this article.

Create master key

Step 4: Create a certificate to encrypt the endpoints in Always On

In this step, we create a certificate to secure the availability group endpoint in SQL Server Always On. This certificate is also useful to secure the inbound traffic on the secondary AG. SQL Server uses these certificates for authentication purposes as well in a domain-independent cluster.

Execute this script on the primary replica SQLAG1.

Create a certificate to encrypt the endpoints

Step 5: Backup the Certificate

We need to take a backup of the certificate created in step 4. This certificate is required later in the secondary replica user authentication.

Backup the Certificate

Step 6: Create an endpoint for the AG communication

In this step, we create an endpoint on the primary replica SQLAG1 with the following information.

  • It uses the default port 5022 for the AG communications
  • In the authentication mechanism, it uses the certificate that we created in step4
  • It uses the AES encryption algorithm mechanism

Create an endpoint for the AG communication

Step 7: Perform step 3 to step 6 on the second node of the domain-independent failover cluster

You need to perform steps 3 to 6 in the secondary replica SQLAG2 using the t-SQL. You can copy the script from the attachment in this article.

Steps for secondary AG node

Step 8: Create a SQL Login on the SQLNode1

In this step, we create a SQL Login and user in the master database. This user will be used to authorizing the user on the public key portion of the certificate from the SQLAG2 node.

Create a SQL Login on the SQLNode1

Step 9: Import the public key portion of the certificate from the SQAG2 node

You should copy the certificate from the SQLAG2 node into the SQLAG1 node. Now, create another certificate in the SQLAG1 node from the SQLAG2 node and authorized SQLAG2User to access it. If you have multiple secondary replicas in your environment, you need to perform for all secondary replicas certificate.

Import the public key portion of the certificate from the SQAG2 node

Step 10: Grant permissions to connect to the endpoint for the SQLAG2Login

The login should have permission to connect to the HADR endpoint for communication between primary and secondary replicas.

In this step, we assign permissions to connect to the endpoint.

: Grant permissions to connect to the endpoint

Step 11: Repeat steps 8 to 10 on the SQLAG2 node

You need to repeat steps 8 to 10 on the secondary AG node SQLAG2 with the scripts attached to this article.

Steps 8 to 10 on SQLAG2

At this point, your security configurations for the domain-independent SQL Server Always On Availability Group are complete. Now, we can deploy the AG group in the next section.

Configure a domain-independent SQL Server Always On Availability Group

Before you want to deploy an AG group, make sure you have met the following prerequisites.

  • You should create a database into the primary AG replica
  • You should use a similar data file and log file directories in both AG nodes
  • Perform a full and transaction log backup on the primary replica
  • Copy the full and transaction log backup files to the secondary replica nodes
  • Restore the full and log backup on the secondary replica in NORECOVERY mode

Once you start creating a new availability group in SSMS, you see that it uses the built-in service account for the endpoint owners. Usually, in a traditional AG, we use an AD service account for endpoint authorization, but we cannot use these built-in service accounts in a domain-independent AG. We use the SQL logins created earlier in this article. We cannot change these endpoint owners in this SSMS wizard.

Configure a domain-independent SQL AG

If you try to configure domain-independent AG using SSMS wizard, it fails with the below error message.

AG failure

Click on Error hyperlink to get the detailed error. As per the error message, it says it can’t find the built-in service account ‘NT Service\MSSQL$INST2’

View the error message

You can script out the AG configuration from the summary page.

Script AG configuration

Now, change the endpoint owners as the SQL users in the generated AG script.

  • Modify endpoint owner for the primary replica

    Change endpoint owner

  • Modify endpoint owner for the secondary replica

    Change endpoint owner on secondary replica

Now, execute the generated AG script in SQLCMD mode and your domain-independent AG Availability Group is available now as shown below in the AG dashboard.

View AG dashboard

Create a SQL listener for the domain-independent SQL Server Always On Availability Group

Expand the availability group in SSMS on the primary replica and create a new availability group listener.

Here, we specify a listener DNS name, port and the static IP address.

SQL listener

Once your listener configuration is complete and you try to connect to it in SSMS, you might get the network-related error.

Once we configure a listener in a traditional AG, it creates a computer object in the active directory. In the domain-independent failover cluster, it cannot create the listener computer object in the AD.

To overcome this issue, connect to the DNS manager and create a new forward lookup zone( SQLShackdemo.com) and the new host record for the SQL listener, as shown below.

Create a new host in DNS

Once the host record is created, you should get the ping response for the listener. It should return the IP address if you ping with the listener’s name.

Ping response

You can now connect to the domain-independent Availability Group with a listener as well.

Connect primary replica using listener

Failover in a domain-independent Availability Group

It is always advisable to check the AG failover once you configure it for the first time. In the failover wizard, verify the current and new replica.

AG failover

Once the failover is completed, connect to the new primary replica SQLAG2\INST2 and verify the dashboard health and new AG owner.

AG dashboard health

Conclusion

In this article, we configure a domain-independent SQL Server Always On Availability Group. This feature is available on Windows Server 2016 and SQL Server 2016 onwards. You should consider your requirements before implementing an AG group.

Attachment

Table of contents

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups

Rajendra Gupta
660 Views