In this article, we will proceed with configuring a 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.

1. A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
   • Configuration of virtual machines on Oracle VirtualBox with Windows Server 2016
2. Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
   • It configures the domain controller and active directory features.
   • Add the virtual machines to the domain created above.
3. Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
   • Windows failover cluster configurations
   • iSCSI target and initiator configurations
   • File share witness confirmation as Cluster quorum

Install SQL Server 2019 on Windows Server 2016 for SQL Server Always On availability groups

SQL Server 2019 is the latest version available for SQL Server. It has several new features along with the enhancements to existing ones.

You can check out SQL Server 2019 articles on SQL Shack to learn more about SQL Server 2019 features.

SQL Server 2019 provides developer edition to use for development and test purpose without any license cost.

Open the Microsoft URL and click on download now for developer edition.

It downloads a setup file. Launch it, and you get the below options.

• Basic: It installs SQL Server 2019 with the default configuration of the SQL Server database engine
• Custom: In this option, we can select the features, configure them in the installation wizard
• Download media: It downloads the ISO or CAB file for the SQL Server set up

It is a better idea to download the media first and then perform the installation. You can share the media across different servers as well as it avoids issues due to network fluctuations.

Next, specify the file type, directory and language of the setup.

Click on Downloads, and it gives you ISO file, but it might take time depending upon your network bandwidth.

It downloads the SQL Server 2019, as shown below.

Install SQL server 2019 on SQLNode1

Click on setup.exe, and it opens the SQL Server Installation Center.

Navigate to Installation -> New SQL Server standalone installation.

In a SQL Server Always On, we install the SQL as a standalone component on all the nodes. It does not require a SQL Server in a failover cluster mode.

Select the edition of SQL Server 2019. We choose the Developer edition in this demo.

Accept the Microsoft software license terms and privacy agreement.

The next step of Microsoft update is optional. We can ask Microsoft update to check updates for Windows, Microsoft software along with SQL Server 2019. It requires an active internet connection on the server.

It installs the required setup files.

In the next screen, we configure the following options.

• Feature selection: Here, we select the features we want to install in the virtual machine. Select the database engine services from the instance features
• Instance root directory: Specify a root directory for your SQL Server installation. As per best practice, you should not install it in the root directory where the Windows OS file exists. Similarly, configure the shared feature directory

Specify a SQL instance name as a named instance. You can give the SQL instance name to reflect your environment and application.

It creates the instance-specific directory in the specified instance root path.

On the next page, we configure the service account for running the SQL Services. Ideally, you should run the SQL Services with a separate service account in the active directory.

Let’s hold SQL Server installation at this moment (do not cancel) and connect to the active directory server.

Create a service account in the active directory

Type dsa.msc in the Windows start -> run and launch the active directory users and computers.

Right-click on Users folder and create a new user with a unique service account information.

As per best practice, you should use the following things in the SQL Server service account.

• A complex password (alphanumeric password)
• Its password should not expire
• User cannot change the password after the first logon

Once the user is created, add the user to the administrator group on the respective node.

SQL Server 2019 installation (Continued)

Switch back to SQL Server installation on Node 1 and specify the service account for SQL Services. You can also change the default startup mode (automatic or manual) for SQL Services.

Now, here comes various default database engine configurations.

• Server Configuration: It gives you the option to choose the authentication mode (Windows or mixed mode). If you select the mixed mode, it enables SQL authentication as well. In this case, specify the password for SA. We can also add AD users as SQL Server administrators. Here, I added the MyDemoSQL\adadmin Windows user as SQL admin. It provides sysadmin permissions to all SQL Server administrators added here

  

• Data Directories: Specify the data root directory, user database, user log and backup directories. You should consider your storage requirements, database growth, IOPS for considering the data directories. For this article, we can go with the default directories

  

• TempDB: TempDB is a critical database in SQL Server and responsible for all data sorting, internal objects, row versions, indexes, DBCC checks, temporary objects, order by, group by functions. We should place it on the fastest storage drives such as flash disks

  According to the Microsoft recommendation, the number of tempdb data files depends upon the number of logical processors

  Logical processors <= 8: Number of TempDB files should match the logical processors count

  Logical processors > 8: Use 8 data files and the increase in multiple of 4 in case of contention

  SQL Server 2019 set up detects your system configuration, configures the tempdb files accordingly

  

• MAXDOP: It is the maximum degree of Parallelism setting for SQL Server. It controls the number of cores for parallel query execution. This configuration during SQL Server installation is available from SQL Server 2019

  

• Memory: SQL Server is a memory-intensive application. It consumes the whole memory, whatever is assigned to it. By default, SQL Server is designed to use all OS memory. It might cause limited memory for the operating system that eventually leads to high resource utilization and performance issues

In SQL Server 2019, we get the option to set maximum default memory or the recommended maximum memory. We should assign approx. 70-80% of total server memory to SQL Server in case it does not hold any other application.

For the demo part, we can go with the default configuration.

Review your SQL Server 2019 installation configuration and click on Install.

It installs the SQL Server database engine services successfully, as shown below.

Install SQL server 2019 on SQLNode2

You can follow the steps mention in the Install SQL server 2019 on the SQLNode1 step and configure SQL Server 2019 on SQLNode as well.

Install SQL Server Management Studio on SQLNode1 and SQLNode2

SQL Server Management Studio (SSMS) is not an integral part of the SQL Server set up. It is a separate client application, and Microsoft provides periodic updates (monthly) with bug-fixes and new enhancements.

You can download SSMS latest version from Microsoft Docs and complete the installation wizard on both nodes.

Launch SSMS and verify the SQL Server 2019 on both the instances.

Enable feature SQL Server Always On availability groups on SQLNode1 and SQLNode2

To use the SQL Server Always On, we need to enable the feature on both nodes. Connect to SQLNode1 and open SQL Server 2019 configuration Manager.

In the configuration manager, navigate to the Always On Availability Groups tab. Here, you can see the Windows failover cluster name. Put a check on the Enable Always On Availability Groups.

You need to restart the SQL Services. Click OK and restart SQL Services on SQLNode1.

Similarly, enable the SQL Server Always On availability groups on SQLNode2 and restarts its services.

Create a SQL database to add in the availability group

Connect to SQL Server 2019 on SQLNode1 using SSMS. Expand Always On Availability Groups, and it does not show any groups as of now.

It does not hold any user databases as of now. We need a user database to add in the SQL Server Always On. In the new database configuration window, specify a database name and create it with default configurations.

As a prerequisite, a full backup should exist for a database to add in the Always On. In the query window of SSMS and run the following command to take full backup in the default directory.

 

1	Backup database sqlshackdemo to disk=’’sqlshackdemo.bak’

Configure SQL Server Always On availability group

To create a new availability group, right-click on Always On Availability groups and new availability group wizard.

It launches the new availability group with a brief description of the steps.

Specify a name for the availability group. Here, I specify the availability group name as SQLAG2019. In the cluster type, it automatically selects the Windows Server Failover Cluster.

We can skip the options – database level health detection and Per database DTC support for this article.

In the next step, it shows you the user database and its eligibility to add to the availability group. We have already performed a full database backup, so it shows the status as meets prerequisites. If the database is encrypted, such as SSISDB, we need to specify the password to decrypt the database and configure the AG.

On the next page, we can do the following configurations.

• Replicas: By default, it shows you the instance you are connected to

  

  Click on Add replica, specify the SQL instance name for SQLNode2. As we can see here, SQLNode1 takes the primary role, while SQLNode2 takes a secondary role initially.

  We can have Synchronous commit and Asynchronous commit for the availability mode. In the synchronization mode, it waits for the acknowledgment from the secondary node before commit records on the primary instance.

  In an asynchronous mode, it does not wait for any acknowledgment and commit records directly on the primary instance.

  You can refer to this article, Data synchronization in SQL Server Always On Availability Groups for more details on availability modes.

  

  We can have automatic failover for the synchronous commit mode; therefore, I choose automatic failover in synchronous mode.

  

• Endpoints: It automatically creates the database mirroring endpoints on both the SQL instances on port 5022

  

  Backup preferences

  As highlighted earlier, we can use the secondary AG replica for SQL Server backups as well. We will go with the default options in this article. You can refer to the following articles for the backups knowledge in AGs.

   

  • Understanding backups on Always On Availability Groups – Part 1
  • Understanding backups on Always On Availability Groups – Part 2
  • SQL Server Always ON Availability Group Log Backup on Secondary Replicas

   

  

  Listener

  In SQL Server Always On, we use a listener to connect to the primary node. We might have a primary replica on any of the participating nodes. SQL listener helps to avoid any modification in the connections string, in case of a database failover. They can connect to SQL using listeners, and it always redirects the connection to the primary replica.

  We can choose to create an availability group listener name or not. If we wish to create a listener, specify a listener’s name and the port.

  

  On the next page, select the method for the initial synchronization. You can choose the automatic seeding, full database and log backup, Join only and skip initial data synchronization.

  

  Next, it performs the validations, and you get success status once it does not report any issues.

  

  

  Review your configuration and click Finish to configure a new availability group and synchronize it. Launch availability group dashboard and you can see the database is in synchronized mode on both SQLNode1 and SQLNode2.

  

  Previously, we do not have any role in the Windows failover clustering. Now, after the AG configuration, we see the role of the SQL listener. Currently, this role is owned by SQLNode1.

  

  A usual error while creating the SQL Listener for SQL Server Always On availability groups

  You might face the following error while creating the SQL listener.

  

  This error comes if the computer account does not have sufficient permissions in the active directory to create the computer objects.

  To resolve this error, open the active directory and open the Computers Properties and go to Security.

  

  Click on Add and search for the cluster name account in the Computers object type. For example, in my case Windows cluster name is SQLAGCLU.

  

  Click OK and open the advanced properties. In the advanced properties, click on Add permissions.

  

  Here, provide the Create Computer objects and Delete Computer objects permissions for the cluster computer account.

  

  After assigning the permissions, it creates the SQL listener successfully.

  

  We can connect to the primary SQL instance using SQL listener, as shown below.

  

  Failover testing for SQL Server Always On availability groups

  Once you have configured the AG replica, it is equally important to test whether the AG failover is working fine or not. You might not come with an accidental surprise later that failover is not working.

  Right-click on the AG group and click on failover.

  It shows you the current primary replica, replica status and its failover readiness. Its status should be No data loss to avoid any data loss scenario.

  

  On the next page, connect to the current secondary replica.

  

  View the summary for the current and new primary replica along with the affected database during failover.

  

  Click Finish, and it performs a failover from SQLNode1 to SQLNode2 for my demonstration.

  

  Refresh the AG console, and you can validate the new primary replica and synchronization status between both nodes.

  

  In the Failover Cluster Manager also, you have the role owner as SQLNode2.

  

  Conclusion

  In this series of articles for SQL Server Always On availability groups, we explored end to end process for all configurations including VM, Active Directory, DNS, failover cluster, AG replica. You can follow these articles and prepare your SQL Server Always On availability group for the testing purpose.