Configure a SQL Servers cluster with AlwaysOn Availability Group - 2

 If in previous posts we explained a little about what the AlwaysOn Availability Groups were and how we began to build the environment, in this post we will give you the steps to configure the cluster.

SQLServer Configuration
Before creating the availability group, we must have some previous steps. First, we will enable AlwaysOn Availability Groups using the SQL Server 2019 “Configuration Manager”:

We right click on the “ SQL SERVER ” service and select “ properties”:

Since we are in the properties of the SQL server, we can do two things. The first is to check the “ Enable Always On Availability Groups ” checkbox in the “Always On Availability Groups” tab.

The other is to add the user that we mentioned at the beginning of the tutorial, to start the SQL Server and make the connections between servers:

Create Availability Group AlwaysOn
To create a new availability group we access the “ SQL Server Management Studio ” and right click on the Availability Groups folder to start the creation wizard:

With the wizard open, we assign a name to the group and check that the type of cluster is “ Windows server Failover Cluster”:

We select the database that we want to be in the group:

On the next screen, only the primary node will appear. Thus, we will add a new node and check “ Automatic Failover ”:

In our case, the two servers that make up the cluster are exactly the same, so we can leave the default option:

We switch to the “ Listener ” tab and create a listener for the availability group, giving it a name that we will add to DNS , assign a listening port and an IP:

We validate that everything is correct:
We already have the Availability Group created and running:

We check in the summary that all the data we have entered is correct:

We already have the Availability Group created and running:

ConnectionString
In order to connect to our new structure and take advantage of the high availability offered by AGs, it is no longer necessary to make the request directly to the main SQL server. We will use the “ listener ” that we configured in the previous step. To put us in a situation, a connection string without AG would have a structure like the following:

Server=ServerName;Database=dbName;User Id=user;Password=***;

Our structure the connection string would be the following:

Server=itinfs-lab02.testing2.icm;Database=Test;User Id=user;Password=***;

As we can see, the request goes directly to the server and that is no longer the correct way to operate with AGs. It should have the following structure:

Server=tcp:AGlistener_name,AGlistener_port;Database=dbName;User Id=user;Password=***;

For it to work with what was seen in this tutorial, the connection string would be as follows:

Server=tcp:AG_listener,5504;Database=Test;User Id=user;Password=***;

In the event that we had X nodes that only attended read requests, we could indicate it in the connection string:

Server=tcp:AG_listener,5504;Database=Test;User Id=user;Password=***; ApplicationIntent=ReadOnly

Conclution
The tool that Microsoft proposed with Availability Groups AlwaysON is very useful. It allows the service to be idle for as short a time as possible before it balances all the load to the secondary node, which will become the primary node to accept new read and write requests.

In the environment we have created, Quorum is not fully exploited . The Quorum allows there to be no “ Split Brain ” error, since the nodes that are part of the Availability Group agree on what data is correct in the event of a failure, so the more nodes the more difficult it is to this bug. In addition, it has good scalability in case we want to add more nodes to the structure.