SQL Server Always On availability groups offer high availability and a disaster recovery solution for mission critical databases running on SQL Server. Always On availability groups support multiple secondaries to offload read-only workloads and other resource intensive maintenance activities, such as backups and rebuilding indexes.

This blog post is the second in a series that acts as your guide for deploying SQL Server and its high availability solutions on Oracle Cloud Infrastructure (OCI). For an explanation of how SQL Server runs on OCI, read Microsoft SQL Server on Oracle Cloud Infrastructure.

Why run SQL Server on Oracle Cloud Infrastructure?

OCI is an excellent choice for optimizing your application, infrastructure, and platform through cloud computing, given its comprehensive capabilities for infrastructure as a service (IaaS), platform as a service (PaaS), packaged apps, and software as a service (SaaS). Many customers are now receiving the benefits of deploying SQL Server and its high availability solutions on OCI, such as lower total cost of ownership (TCO) than on-premises and competing clouds, superior performance backed by service license agreements (SLAs), and no single point of failure.

Getting started with SQL Server Always On

As mentioned in the first post, you can deploy SQL Server on OCI using two methods:

  • Provision a Compute instance running SQL Server from the Oracle Cloud Marketplace. This option includes a SQL Server license.

  • Provision a Compute instance for each database node you need, install SQL Server on each node, and then configure Always On. You must bring your own license (BYOL) for SQL Server.

For our example, we’ve selected the second option. For our setup, we’re using the following parameters:

  • Compute nodes: Node 1 (Primary replica), Node 2 (Secondary replica), Node 3 (Witness node).

  • Domain controller node: DCNode

  • SQL Server: Microsoft SQL Server 2016 (SP3-OD) (KB5006943) – 13.0.6404.1 (X64)

  • Edition: Developer Edition (64-bit)

  • Operating system: Windows Server 2019 Standard

In some cases, you can deploy cluster nodes in an environment with no preexisting active directory. This scenario requires you to configure at least one of the cluster nodes as a domain controller, but here, we have separate nodes for domain controller because SQL Server isn’t supported on clustered nodes acting as domain controller. In our next post of the series, we discuss why we need a witness node and more on listener creation on Always On.

We’re using the following architecture for our SQL Server Always On deployment. We have three nodes in a clustered setup distributed across three availability domains: AD1, AD2, and AD3. We keep both the domain controller node and primary replica node 1 in AD1. In AD 2, Node 2 acts as a secondary replica. We’re using Compute instance node 3 as the witness node.

If failover occurs, node 1 acting as primary replica switches roles with node 2, which is our secondary replica, to avoid business downtime. It also allows you to offload read-only workloads to the secondary replica and helps you achieve better performance for OLTP transactions.

To simplify the architecture, we have configured a single virtual cloud network (VCN) and a public subnet with its own security list and route table. If we want to keep backup files on cloud storage, we can use OCI Object Storage.

A graphic depicting the architecture for deploying AlwaysOn on OCI.

SQL Server Always On is based on Windows Server Failover Cluster (WFSC). It consists of a group of computers, referred to as nodes, grouped together to make the environment highly available for business users. We have configured the failover cluster manager and added all three nodes as part of a WFSC. When all the nodes are added successfully as part of WFSC, we install SQL Server on nodes 1 and 2. Node 3 only acts as witness node, but if you want another secondary replica, you can use node 3.

The following image shows a snippet from the failover cluster manager:

A screenshot of the Failover Cluster Manager, showing the nodes in the cluster.

Why SQL Server Always On instead of SQL failover cluster?

Configured at the SQL instance level, a SQL failover cluster provides high availability and supports automatic failover, but it offers no disk redundancy because it uses shared storage. This configuration can act as a single point of failure and is exactly where SQL Server Always On can provide both high availability and a disaster recovery solution.

With the Always On feature, you can create multiple availability groups, grouping your application-specific databases together. This grouping further helps you manage the databases better and provides flexibility during failover as only SQL availability group fail over to another replica and not another SQL Server instance. Within Always On, each node uses its own disk and requires SQL Server to be installed on each node that hosts Always On availability group.

Configuring Always On

Because we’re using a SQL Server solution engineered on a Windows cluster, we must enable the Always On availability groups feature through the SQL configuration manager. All the nodes part of availability groups need this installation. We have two nodes: DevSQLMA1 and DevSQLMA2.

A screenshot of the SQL Server Configuration Manager screen, showing the properties and the Windows failover cluster name.

After we enable the Always On availability groups feature, we can create the Always On availability group on OCI with the help of SQL Server Management Studio (SSMS). Open SSMS on the primary replica (DevSQLMA1), go to the Always On option, and expand availability groups.

A screenshot of the New Availability Group window showing the availability group options.

To create Always On availability groups, we need to ensure that the database, as part of our availability group, meets all prerequisites. In our scenario, we’re using the test database, BackupTest, which meets all prerequisites: Online, read-write accessible, and using the full recovery model.

A screenshot of the New Availabilty Group window on the Select Databases tab with the BackupTest database selected and highlighted.

In the next screen, click Add Replica, which opens the Connect to Server wizard, and provide your secondary replica name (In our case, DEVSQLMA2).

A screenshot of the Specify Replicas tab with the Add Replica button highlighted and the Connect to Server window open.

Here, we can choose the initial role, automatic failover settings, and availability modes as we require.

A screenshot of the Specify Replicas tab, showing the replicas availability to select as the secondary.

In the next window, we can choose our data synchronization preference. We’re proceeding with automatic seeding, which automatically creates the database on our secondary replica.

A screenshot of the Select Data Synchronization with the option for automatic seeding selected.

After completing the validation, we can see that our Always On availability groups have been created.

For final verification, we go to the Failover Cluster Manager wizard and see that Always On availability groups have been created as a role within Failover Cluster Manager.

A screenshot of the Failover Cluster Manager showing the created availability groups.

Conclusion

In this blog post, you learned that deploying Microsoft SQL Server Always On availability groups is straightforward in OCI. With its simplicity, OCI is specifically designed to provide performance, predictability, design security, and governance required to support mission-critical, performance-intensive workloads.

OCI provides the best-in-class compute, storage, networking, database, and platform services you need to deliver robust business outcomes as you rethink your data center needs. You can begin your journey with OCI with a Free Trial.

Stay tuned for our next blog for SQL Server on Oracle Cloud Infrastructure.