Rajendra Gupta
Network configurations

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016

July 13, 2020 by

In this article, we will configure a SQL Server Always On Availability Group on the Windows Server 2016. We use SQL Server 2019 for configuration.

Introduction

SQL Server Always On Availability Groups offers high availability and disaster recovery solution for mission-critical databases. It also supports to offload some read workloads to the secondary replica. We can also configure database backups from the secondary instance.

Usually, Database Administrators are responsible for configuring availability groups in an organization. Still, they are unaware of the underlying infrastructure creation such as virtual machine, configure an active directory, join virtual servers in the domain, configure a failover cluster, quorum configuration, and storage allocation.

Suppose you do not have any infrastructure set up as of now. You can follow this article series for an end to end guidance on configuration of SQL Server Always On Availability Groups in Windows Server 2016 with SQL Server 2019.

This series combines the following articles:

  1. A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
  2. Configure Domain Controller and Active Directory for SQL Server Always On Availability groups
  3. Configure Failover Cluster, Storage controller, and Quorum configurations for SQL Server always-on availability groups
  4. Install SQL Server 2019 on Windows Server 2016 with SQL Server always-on availability groups

In this article, we will do the following tasks:

  • Oracle VirtualBox installation
  • Create Virtual machines with Windows Server 2019
  • VM network configurations

Let’s start the journey toward your first SQL Server Always On Availability group.

Configure a Virtual Machine in Oracle VirtualBox

To begin with, we require the following servers.

  • Server 1: We require a server for configuring domain controller and active directory
  • Server 2: Primary node in the SQL Server Always On Availability group
  • Server 3: It acts as a Secondary node for SQL Server Always On Availability group

We will use the Oracle VM VirtualBox in this article to create the virtual machines on my laptop. It is an open-source virtualization software, and you can install Windows, Linux, Unix operating systems on it. In this article, I use Oracle VM VirtualBox 6.1 version.

Oracle VM to prepare SQL Server Always On Availability group

You can browse this URL, and download the platform-specific software, follow the steps and install it. It is pretty straightforward.

This article uses the term VirtualBox for subsequent sections.

On my laptop, I already have the following two virtual machines configured.

virtual machines

Let’s click on New for a new virtual machine. It asks for the following details.

  • Machine name: It is the virtual machine name. It is similar to your laptop hostname
  • Machine folder: It is the directory in your local system where VirtualBox stores all relevant files
  • Type: Select the required operating system from the drop-down values. I require Microsoft Windows operating system for all VM’s
  • Version: Select the version of the operating system. In this article, we will install Windows Server 2016 standard edition

Create virtual machine

Click Next and configure the RAM for the virtual machine. I would recommend you assign at least 4 GB RAM for testing purposes.

configure the RAM

In the next step, add a virtual disk in the new machine. You should consider your database requirements and configure a suitable size of the disk.

Hard disk configuration

Choose the hard disk file type from the following values.

  • VDI ( VirtualBox Disk Image)
  • VHD(VirtualBox Hard Disk)
  • VMDK(Virtual Machine Disk)

We can go ahead with the default hard disk type as VDI. You can refer to VirtualBox documentation for details on hard disk types.

Hard disk file type

The next step is to configure the static or dynamic disk size. In case of static, if your disk becomes full, you need to expand it to avoid performance issues in the VM.

In the dynamic mode, it automatically grows the hard disk space up to maximum configured values. You should also note that it does not release the space once occupied.

Storage on physical disk

Select the VDI storage file location and size. You should have sufficient space in the drive to avoid any storage issues.

VDI file location and size

It creates a new virtual machine, as shown below.

Configured VM

Install Windows Server 2016 on Virtual machine

Download Windows Server 2016 ISO for SQL Server Always On Availability Group

Microsoft gives 180 days evaluation copy to use and learn about the Windows Server 2016. It is beneficial to explore new things, especially if you do not have any corporate servers to perform the activity.

Open Microsoft website and start the evaluation version valid up to 180 days. It requires a license to use Windows beyond 180 days. It is enough time for all your testing work or learns new features.

Download Windows Server 2016

It downloads the Windows Server 2016, depending upon your network bandwidth. Its size is approximately 6.49 GB.

Click on Start in the virtual machine, browse to the directory where we downloaded the Windows Server 2016 ISO, and click Choose.

  • Note: In the below screenshots, it shows the server name as Windows2k16-1. However, all steps remain the same.

Add ISO file

It shows up the start disk to configure the virtual machine.

Select start up disk

Click Start, and it starts to create the process of the virtual machine.

Start process for VM

Choose the language, Time & currency format, and keyboard type. By default, it takes the input as per your system configuration. You can change these values if required.

Install Windows Server 2016 on Virtual machine

Click Next and Install now to begin Windows configuration for SQL Server Always On Availability group.

Click on Install now

Windows Server 2016 ISO involves the following operating systems.

  • Windows Server 2016 standard evaluation edition
  • Windows Server 2016 standard evaluation edition (desktop experience)
  • Windows Server 2016 datacenter evaluation edition
  • Windows Server 2016 datacenter evaluation edition(desktop experience)

If we choose edition without desktop edition, it comes without a GUI. You can connect with command-line tools such as CMD, PowerShell to perform your stuff on it. You should be good with the PowerShell or scripting in this case.

For this article, we install Windows Server 2016 standard edition with the desktop experience.

Windows Server 2016 standard evaluation edition (desktop experience)

Accept notice and license terms.

license terms

Select the installation type.

  • Upgrade: Install Windows. Keep files, applications, and settings
  • Custom: Install Windows only

We require a fresh Windows installation; therefore, select the second option and proceed.

Select the installation type

Select the root operating system directory. In our case, we have a single drive in the virtual machine. By default, it is available for all installation.

root operating system directory

Click Next, and it starts the Windows operating system, with default programs and features.

default programs and features

After the Windows installation, it automatically reboots the virtual machine.

Windows reboot

After reboot, once it comes up, it asks you to configure a password for the default administrator user. We do not have any domain for this virtual machine. Therefore, we will use this user to login to the system.

Specify password for administrator

Finish the installation process and login with the administrator user credentials set above.

Login to the VM

It configures your Windows systems for the first time startup. You can see that VM is part of a workgroup. We will add the domain for this server later.

Server Manager

Open the command prompt and check the IP configuration of this server using the ipconfig command.

IP configuration

Follow the above steps and prepare the below specified virtual machines.

  • SQLNode1: It is the first node for SQL Server Always On Availability group

    SQLNode1  host name

  • SQLNode2: It is the second node for SQL Server Always On Availability group

    Host name

  • VDITest3(DC): This server serves as a domain controller and active directory

    Domain Controller

Network configurations for the Virtual machine in VirtualBox

By default, a virtual machine is configured for the Network Address Translation (NAT). In a NAT configuration, usually, a firewall assigns a public address to the VM inside the private network. To view the network configuration, go to the VirtualBox, right-click on it and view Settings -> Network.

Network configurations

Modify the network adapter from NAT to Bridged adapter. In the bridged adapter, select the ethernet adapter.

Network adapter

You can also configure multiple network adapters in a VM. For example, in my case, I have an internet connection using Wi-fi. To use the internet on VM as well, I configured the second adapter for all VM’s as shown below.

Bridged Adapter

Conclusion

In this article, we configured Oracle Virtual machine and prepared three VM’s. We will use these VM’s in the subsequent articles for configuration of SQL Server Always On Availability Groups.

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
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
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
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Group
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views