In this blog post, we discuss how to configure high availability with automatic failover and an availability group listener configured on Oracle Cloud Infrastructure (OCI). One of the important features of Always On availability groups is providing high availability with automatic failover and ensuring that the database is quickly made available again after the loss of the primary replica.
This blog continues our series on configuring SQL Server on OCI. Previously in the blog, SQL Server Always On availability groups on Oracle Cloud Infrastructure, we learned how we can deploy Microsoft SQL Server Always On availability groups on OCI. Also in the blog, Read-scale availability groups on Oracle Cloud Infrastructure, we understood how read-scale availability groups are a great option to deploy on OCI for offloading workloads to multiple secondaries.
Important features in this configuration
High availability for SQL Server is the capability of SQL Server that allows it to continue functioning when the primary replica becomes unavailable. This feature is important for mission-critical databases that can’t tolerate interruption in service, and any downtime can cause damage or result in financial loss.
An automatic failover causes a qualified secondary replica to transition automatically to the primary role when the primary replica becomes unavailable.
An availability group listener is a virtual network name (VNN) that clients can connect to in order to access a database in a primary or secondary replica of an Always On availability group. A listener allows a client to connect to a replica without having to know the physical instance name of the SQL Server. Because the listener routes traffic, the client connection string doesn’t need to be modified after a failover occurs.
Architecture
This reference architecture includes a Microsoft SQL Server Always On availability group hosted on two OCI Compute virtual machine (VM) instances on different subnets, an Active Directory domain controller, and a file share witness hosted on a domain controller.
The following diagram illustrates this reference architecture:
We start by configuring the infrastructure, which we kept the infrastructure simple. Create two subnets in the VCN and provision an OCI Compute VM Instance on one of the subnets that we created (subnet A). Configure Active Directory Domain Services (ADDS) on this OCI VM Instance and make it our domain controller.
Provisioning SQL Server
As we have discussed in our previous blog, you can deploy Microsoft SQL Server on OCI using the Oracle Cloud Marketplace image (search for “SQL Server”) or Bring Your Own License (BYOL)
If you choose the BYOL option, you must provision two VM instances on each of the subnets that we created earlier—one VM on subnet A and another VM on subnet B—and then install and configure SQL Servers on these VMs. Similarly, for the Oracle Cloud Marketplace image, we provision the two SQL servers on different subnets.
Configuring secondary private IP to the VNICs
The VM instances have a primary IP address assigned to their VNICs. Next, we assign a new, secondary private IP to the VNICs of these SQL Server VM Instances. Follow the steps mentioned in the documentation to assign a new secondary private IP to a VNIC.
Configuring SQL Server AlwaysOn availability groups
We already discussed configuring SQL Server AlwaysOn Availability groups in our previous blog. In this case, however, we’re configuring the availability group in multiple subnets. The step is similar with the exception that the steps configure an availability group listener.
The complete steps for configuring availability group in multiple subnets are documented in Tutorial: Configure an availability group in multiple subnets (SQL Server on Azure VMs). Follow these steps after you configured the secondary Private IP for the VNICs. Now, enable automatic failover for the SQL AlwaysOn availability group.
Conclusion
In this blog post, you learned how to configure high availability for Microsoft SQL Server using SQL Server Always On availability groups in a multiple subnet environment on OCI.
You can begin your journey of Oracle Cloud Infrastructure with a Free Trial.