Hi,

I will continue to explain What is the SQL Server AlwaysOn Architecture and Step by Step Installation in this article series.

 

SQL Server AlwaysOn

Read the first article of SQL Server AlwaysOn before this.

SQL Server AlwaysOn ( Availability Group ) Architecture and Step by Step Installation -1

 

 

AlwaysOn Availability Group Install

After completing the requirements mentioned in the previous article for AlwaysOn installation, we now have 2 Standalone SQL Server Instances which are on 2 different servers. These Standalone SQL Server Instances’ disks, IPs, etc. are all different, but they run as Windows Cluster with Windows Server Failover Clustering service feature as below.

 

 

Open SQL Server Configuration Manager and Double click on SQL Server (MSSQLSERVER) service to go to the Properties section. AlwaysOn Availability Groups feature is enabled on the AlwaysOn High Availability Tab by clicking.

If Windows Cluster is not installed correctly in this step, you cannot activate this feature. Because SQL Server AlwaysOn does not work without Windows Cluster, just like SQL Server Failover Cluster.

 

After this process, the SQL Server service must be restarted.

 

 

 

When we look at the SQL Server Instance features after Restart, we can see that AlwaysOn feature is active as follows.

 

 

Now we can start the AlwaysOn installation. Connect to SQL Server Instance with SQL Server Management Studio to create AlwaysOn Availability Group in SQL Server 2012, 2014, 2016, 2017, 2019. Right-click on the Availability Groups under AlwaysOn High Availability on Object Explorer to launch the New Availability Group Wizard.

 

Click Next in this step.

 

 

 

 

A name is defined the Availability Group to be created.

 

On the Select Databases page, select the databases that are required to be included in the Availability Group. To be able to join a database in the Availability Group, Full Recovery Mode must be enabled and a full backup must be taken once before the operation.

 

 

 

 

 

 

Take backup as follows.

 

 

 

 

Meets prerequisites. Choose the database and Click the Next button.

 

On the Specify Replicas page, the second server to be syncronized (and the third server, if any) will be added from the Add Replica section by connecting to the other node as follows.

 

 

Specifications are made to both nodes for automatic failover, synchronous commit, readable secondary roles, depending on the scenario. (According to our scenario, each feature is selected, each server is readable secondary.)

 

 

 

Endpoints tab provides the necessary port settings. Endpoints Enable communication between servers for synchronization of SQL servers included in Availability Groups. Therefore, it is important to note that the port used for the endpoints must be opened on the Firewall.

 

 

In this step, determine where to take Backup, etc. To reduce the load on Primary it is recommended you to take Backups from Secondary.

 

 

 

 

 

If you want to access SQL servers with unique IP and listener name, you must define the Availability Group Listener from the Listener tab. Even if the Listener identification is not performed during installation, it can be defined later by the Add Listener option via the corresponding availability group. This listener is also referred to as the AlwaysOn listener name, which is the same as the Failover Cluster name and IP in the SQL Server Failover Cluster. After the installation, we will access AlwaysOn with this IP and name instead of the local IP.

Before the Listener is created, the Windows Server Failover Cluster object that hosts the availability group must have Create Computer Objects privilege. Otherwise, an error will be received as follows. (create computer objects)

 

 

The Select Data Synchronization step requires specifying a shared location where all replica servers have access. In addition, it should not be ignored that the read / write privileges of SQL Service and Analysis Service accounts must have at this step.

 

 

Click Next and all the controls are done.

 

 

Installation is started.

 

The installation was successfully completed as follows.

 

 

 

I will continue to explain AlwaysOn Installation in the Next Article.

 

You can access the Next posts related with SQL Server Always On Installation with the following link.

https://ittutorial.org/sql-server-alwayson-architecture-and-step-by-step-installation-3-manual-fail-over-steps/

 

 

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

SQL Server ( MSSQL DBA ) Database Tutorials for Beginners Database Administrators