Note:

Configure Microsoft SQL Server Always On Availability Group on OCI with Listener and Load Balancer

Introduction

Deploying Microsoft SQL Server in Oracle Cloud Infrastructure (OCI) offers enterprises a powerful platform to meet their high availability and business continuity goals. One of the most effective approaches for Microsoft SQL Server high availability is the Always On availability group feature. Microsoft SQL Server Always On availability groups provide a high-availability and disaster recovery solution that enables seamless failover and data replication across multiple SQL Server instances.

There are various ways to set up a Microsoft SQL Server availability group. One approach is to place your SQL Server virtual machines in separate subnets within the same virtual network. To deploy a SQL Server availability group using a multi-subnet architecture, see Deploying a highly available Microsoft SQL Server database on OCI using Always On availability groups and Deploy Microsoft SQL Server Always On Availability Groups for HA and DR on OCI.

By following this tutorial, you will deploy and configure a SQL Server Always On availability group on OCI using virtual machines within a single subnet. This configuration includes a private OCI Load Balancer acting as the listener, enabling seamless client connectivity and high availability. This approach is ideal for environments where simplicity and reliability are key, providing automatic failover, data redundancy, and uninterrupted access to your SQL Server databases. With this setup, your infrastructure is well-positioned to support mission-critical workloads with robust business continuity. For production deployments, consider implementing enhanced monitoring, logging, security hardening, and backup strategies to complement this high availability configuration.

If you are looking to deploy an Always On availability group within a single subnet, this tutorial provides step-by-step guidance for setting it up using OCI virtual machines and an OCI Load Balancer. This tutorial walks you through setting up a two-node Always On availability group on OCI using virtual machines in a single subnet, with an OCI Load Balancer.

Architecture

The following image shows a sample high level architecture of the solution.

Image 1

Exclusions for this Tutorial

Objectives

Prerequisites

Task 1: Configure SQL Server Always On Availability Group

Task 1.1: Enable Availability Groups on both the SQL Server Nodes (DevSQL1 and DevSQL2)

  1. In the DevSQL1 node, go to Start, search and open SQL Server 2022 Configuration Manager.

  2. Select SQL Server Services, right-click the SQL Server (MSSQLSERVER) service and select Properties. If you are using a named instance it will be SQL Server (INSTANCENAME).

    Image 3

  3. Click Always On Availability Groups and select Enable Always On availability groups.

    Image 4

  4. Select Apply and click OK.

  5. Restart the SQL Server service.

  6. Repeat step 1 to 5 on the other node (DevSQL2).

Task 1.2: Create a Database on first SQL Server Node (DevSQL1) and take a Full Backup

  1. In the DevSQL1 node, go to Start, search SSMS and open SQL Server Management Studio (SSMS).

  2. Connect to the primary SQL Server. In this tutorial, it is DevSQL1.

    Image 6

  3. Under Object Explorer, right-click Databases and click New Database.

    Image 7

  4. Under General, enter Database name and under Options, select Full as Recovery Model.

    Image 8

    Image 9

  5. Click OK.

  6. To take a full backup, right-click database “AdventureWorks2022”, Tasks and click Backup.

  7. Under General, ensure Backup Type is Full and under Destination, select Disk as Backup to, click Add and enter the location and filename for the full backup.

  8. Click OK.

You can also take a backup using Transact-SQL. For example:

Transact-SQL:

BACKUP DATABASE [AdventureWorks2022] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventuresWork2022.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2022-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Task 1.3: Create an Availability Group

  1. Open SQL Server Management Studio, connect to DevSQL1. Under Object Explorer, right-click Always On High Availability and click New Availability Group Wizard.

    Image 10

    This will open a New Availability Group page. Click Next.

    Image 11

  2. Under Specify Options, enter Availability Group Name as DevAG, Cluster type as Windows Server Failover Cluster and click Next.

    Image 12

  3. In Select Databases, select the database which you want to add to this availability group. Under status, you should see Meet prerequisites to be able to add the database to the availability group. If there are any issues, it will tell you the reason and we need to take the necessary actions to meet the prerequisites. For example, a full database backup may be pending.

    Image 13

  4. In Specify Replicas, add the replicas. Click Replicas to ensure both nodes are added under Availability Replicas and change the Availability Mode to Synchronous Commit. You can have different Availability Mode as per your requirements.

    Image 14

  5. Click Endpoints to ensure both the endpoints are using the same port and these ports are open in the firewall and security list. By default it will be port 5022.

    Image 15

    Note: For this tutorial, we will keep all the other options as default. Do not create listener as of now.

  6. Next we need to select data synchronization. Select Automatic Seeding and click Next.

    Image 16

  7. In Validation, ensure validation is Success and fix any failures. You can ignore the warning Checking the listener configuration as we will be creating the listener later. Click Next and once the availability group is created, select Close to close the wizard.

  8. In Object Explorer, expand Always On High Availability, and then expand Availability Groups. You should now see the new availability group in this container. Right-click the availability group and select Show Dashboard.

    Image 17

  9. Open Failover Cluster Manager, go to Start, search and open Failover Cluster Manager. Connect to your cluster and click Roles.

    The availability group name that we used is a role on the cluster and that availability group does not have an IP address for client connections because we did not configure a listener. We will configure the listener after we create an OCI load balancer.

    Image 18

At this stage, the availability group has been successfully configured with two SQL Server replicas, allowing failover between the instances. However, client connectivity using listener is not yet possible as a listener has not been set up.

Task 2: Create an OCI Load Balancer and Configure an Availability Group Listener

There are several approaches to configure a SQL Server Always On availability group listener on OCI, including using a multi-subnet setup or assigning a secondary IP address to each virtual machine. While these methods are valid, they often require additional configuration or custom scripts to manage IP movement during failover events.

If you wish to deploy an Always On availability group in a single subnet environment and prefer to avoid custom scripting, using a private OCI Load Balancer offers a streamlined and reliable solution. This section will guide you through configuring the OCI Load Balancer to act as the listener for your Always On availability group, enabling seamless connectivity and failover handling within a single subnet.

Task 2.1: Reserve a Static Private IP Address for SQL Server Always On Availability Group Listener

Reserve a static private IP in OCI which will later be assigned to SQL Server Always On availability group listener IP in Windows Failover Cluster. We are reserving this IP address to ensure that no other services or VMs are assigned this IP address on OCI. This IP address will not be used by clients to connect but it will help the failover of the availability group and ensure the OCI Load Balancer is routing the connection to the correct primary server.

  1. Go to the OCI Console, navigate to Networking and click Virtual Cloud Networks.

  2. Go to the VCN that you are using for the SQL Server nodes.

  3. Click Subnets, go to the subnet that is being used for SQL Server nodes.

  4. Under Resources, click IPv4 addresses.

  5. Click Add reserved IPv4 address.

    Image 33

  6. Enter the IP address that you want to assign to SQL Server Always On availability group listener. This can be any unused IP address, make a note that clients will not be able to connect using this IP address remotely. This is a dummy IP that we will be assigning to the SQL Server Always On availability group listener in Windows Failover Cluster. For this tutorial, we have used 10.0.0.148.

    Image 34

Task 2.2: Create a Private OCI Load Balancer

Note: The fields which are not mentioned here, are kept as default.

  1. Go to the OCI Console, navigate to Networking and Load Balancers.

    Image 19

  2. Click Load Balancer and Create load balancer.

    Image 20

  3. Enter Load balancer name and select Private as Visibility type.

    Image 21

  4. In Choose Networking, select the compartment name for the VCN and subnet that will be used for this load balancer. Use the same VCN and subnet that you have used for SQL Server nodes.

    Image 22

  5. In Management, select the compartment where this load balancer will be created. Ensure to enable Prevent deletion of the load balancer, listeners and backends when they are still active to avoid unintended disruption of services and click Next.

    Image 23

  6. In Backends, specify a Load balancing policy as Weighted round robin.

  7. In the Select backend servers, add both the SQL Server nodes; DevSQL1 and DevSQL2. Change the port number to 1433; in case you are using a different port for SQL Server, enter the respective port number.

    Image 24

  8. In Specify health check policy, use TCP Protocol with port number 59999. You can use any unused port.

    Image 25

  9. Keep all the other settings as Default and we are using security list for the selected VCN. Click Next.

  10. In Configure Listener, enter Listener name, TCP traffic and Port number as 1433. Keeping all other settings as default.

    Image 26

  11. Click Next, review the configuration and click Submit.

  12. Once OCI Load Balancer is provisioned you can make a note of the private IP address of the OCI Load Balancer by going to Networking, Load Balancers and click Load Balancer.

    Image 35

In this tutorial, the IP address for load balancer is 10.0.0.149. This is the IP address that the clients will be using to connect to SQL Server Always On availability group listener.

Task 2.3: Configure the SQL Server Always On Availability Group Listener

  1. Remote Desktop Protocol (RDP) into the VM which is hosting the primary replica. In this tutorial, it is DevSQL1. To confirm this, you can open Windows Failover Cluster Manager, Roles and for the SQL availability group role look at the Owner Node column to confirm which node is the primary replica.

  2. Go to Networks and make a note of the network Name as a $ClusterNetworkName variable.

    Image 27

  3. Add the client access point. The client access point is the network name that applications use to connect to the databases in an availability group.

    1. In Failover Cluster Manager, expand the cluster name, and select Roles.

    2. Click Roles, right-click the availability group name, select Add Resource and click Client Access Point.

      Image 28

    3. In Name, create a name for this new listener. The name for the new listener is the network name that applications use to connect to databases in the SQL Server availability group.

    4. Click Next twice, and select Finish. Do not bring the listener or resource online at this point.

  4. Take the cluster role for the availability group offline. In Failover Cluster Manager, click Roles, right-click the role, and select Stop Role.

  5. Configure the IP resource for the availability group.

    1. Click Resources, and expand the client access point that you created. The client access point is offline.

    2. Right-click the IP resource, and select Properties. Note the name of the IP address as a $IPResourceName variable. In this tutorial, the name is IP Address 10.0.0.0.

    3. Click IP Address and select Static IP Address. Set the IP address as the IP address that we reserved in Task 2.1. In this tutorial, it is 10.0.0.148.

    Image 29

  6. Make the SQL Server availability group dependent on the client access point.

    1. In Failover Cluster Manager, click Roles, and select your availability group.

    2. Click Resources, under Other Resources, right-click the availability group resource, and click Properties.

    3. Click Dependencies and add the name of the client access point (the listener).

      Image 30

    4. Click OK.

  7. Make the client access point dependent on the IP address.

    1. In Failover Cluster Manager, click Roles, and select your availability group.

    2. Click Resources, right-click the client access point under Server Name, and click Properties.

      Image 31

    3. Click Dependencies and verify that the IP address is a dependency. If it is not, set a dependency on the IP address. If multiple resources are listed, verify that the IP addresses have OR, not AND, dependencies and click OK.

  8. Set the cluster parameters in Windows PowerShell.

    1. Copy the following PowerShell script to one of your SQL Server instances. Update the variables for your environment.

      • Find $ClusterNetworkName name in the Failover Cluster Manager by selecting Networks, right-click the network and select Properties. The $ClusterNetworkName is under Name in the General tab.

      • $IPResourceName is the name given to the IP address resource in the Failover Cluster Manager. This is found in the Failover Cluster Manager by selecting Roles, select the SQL Server availability group or FCI name, select Resources under Server Name, right-click the IP address resource and click Properties. The correct value is under Name in the General tab.

      • $ListenerILBIP is the IP address that you created in the Azure load balancer for the availability group listener. Find the $ListenerILBIP in the Failover Cluster Manager on the same properties page as the SQL Server AG/FCI listener resource name.

      • $ListenerProbePort is the port that you configured on the Azure load balancer for the availability group listener, such as 59999. Any unused TCP port is valid.

      $ClusterNetworkName = "<MyClusterNetworkName>" # The cluster network name. Use Get-ClusterNetwork on Windows Server 2012 or later to find the name.
$IPResourceName = "<IPResourceName>" # The IP address resource name.
$ListenerILBIP = "<n.n.n.n>" # The IP address that we reserved in Task 2.1. This is the static IP address for the SQL Server AG Listener that you reserved in OCI Console.
[int]$ListenerProbePort = <nnnnn>

Import-Module FailoverClusters

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

      For this tutorial, we used the following:

      $ClusterNetworkName = "Cluster Network 1" # The cluster network name. Use Get-ClusterNetwork on Windows Server 2012 or later to find the name.
$IPResourceName = "IP Address 10.0.0.0" # The IP address resource name.
$ListenerILBIP = "10.0.0.148" # The IP address of the internal load balancer. This is the static IP address for the load balancer that you configured in the Azure portal.
[int]$ListenerProbePort = 59999

Import-Module FailoverClusters

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

    2. Set the cluster parameters by running the PowerShell script on one of the cluster nodes.

  9. Adding an exclusion will prevent other system processes from being dynamically assigned to the same port. For this scenario, configure the following exclusions on all cluster nodes.

    netsh int ipv4 add excludedportrange tcp startport=59999 numberofports=1 store=persistent

  10. Bring the cluster role for the availability group online. In Failover Cluster Manager, click Roles, right-click the role, and select Start Role.

  11. In SQL Server Management Studio, set the listener port.

    1. Open SQL Server Management Studio and connect to the primary replica.

    2. Go to Always On High Availability, Availability groups and click Availability group listeners.

    3. Right-click the listener name that you created in Task 2.2, and click Properties.

    4. In Port, specify the port number for the availability group listener and click OK. The default value is 1433.

    Image 32

  12. In your DNS server, ensure there is a Host A record for the private OCI Load Balancer IP address pointing to the SQL availability group listener name. Ensure the SQL listener computer object cannot update the DNS records. This is to ensure that after failover the DNS entry is not updated to the IP address that is being used in Windows Failover Cluster.

    In this tutorial, we updated Host A record of the SQL availability group listener with IP address 10.0.0.149 instead of 10.0.0.148 and updated the Security to ensure SQLAGL$ computer object cannot update this record and can only read it.

    Image 36

    Image 37

There may be more methods to achieve this, you can implement your own solution. The goal is to have a DNS record for SQL Server availability group listener with IP address of the OCI Load Balancer.

Now you can test the connection to SQL Server availability group listener from SSMS or any other client tool.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.