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

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

This article configures SQL Server Always On Availability Groups on the domain independent Windows Failover Clusters. 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.

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

alwayson availability groups installation setup and configuration sql

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts

This article gives an overview of SQL Server Always On Availability Group features. We configured the SQL Server Always On Availability Group from the beginning of building virtual machines.

Configure Managed Service Accounts for SQL Server Always On Availability Groups

This article gives an overview of Configuring Managed Service Accounts for SQL Server Always On Availability Groups.

Install SQL Server 2019 on Windows Server 2016 with SQL Server

In this article, we will discuss configuring SQL Server Always On Availability Groups and perform failover validations. You should go through the following articles and build the infrastructure before proceeding with this article.

An overview of distributed SQL Server Always On Availability Groups

SQL Server 2016 provides a new feature Distributed Availability Group for disaster recovery purposes. It is a particular type of availability group that helps access the multiple failover clusters. In this article’s series, we configured a traditional Always On group. It has the following requirements.