Read-scale availability groups on Oracle Cloud Infrastructure

Recently, we were deploying Always On availability groups in a customer tenancy within Oracle Cloud Infrastructure (OCI). Always On availability groups are helpful for environments where high availability and disaster recovery solutions are primary objectives. Running SQL Server on OCI can be the best choice because you get a robust solution that’s easy to maintain, secure, and offers superior price-performance. OCI services that we offer are backed by the most comprehensive service level agreement (SLA), where we financially guarantee the availability and performance of our network and storage services as your ability to manage services through APIs at all times.

With Microsoft SQL Server 2017, you can use the availability group without a cluster, known as the read-scale availability group, in a clusterless environment.

Scenario background

The customer's tenancy was comprised of multiple environments, so they needed multiple applications to be migrated to OCI. One of the environments was running a highly scalable application, so we needed to ensure that the application’s scalability offered a better user experience, handled growth, and was available across different business units. At the same time, it needed to be an effective solution for cost and operations. For this specific application, our customer didn’t need high availability, but a few secondary replicas for read-only purposes.

In a previous blog, we saw that Windows failover cluster is a prerequisite for setting up an Always On environment. While using Always On with a failover cluster, we get important benefits, such as automatic failover and health checks at the database level, which provides business continuity for an application.

Why do we need read-scale availability groups?

Customers may not need high-availability solutions in every instance, though performance, accessibility, and cost are likely always a consideration. This is where read-scale availability groups - a feature introduced with SQL Server 2017 - provide an optimal solution. As the name indicates, read-scale availability groups help scale the application by offloading read-based workloads to multiple secondary nodes. Does the functionality sound familiar? Perhaps you've heard of read replicas for Microsoft SQL server in Amazon RDS or the read scale-out feature in Azure SQL that leverages read-only replicas. With OCI, you can achieve the same functionality and realize an optimization of cost plus performance by simply deploying SQL Server on Windows-based compute instances.

Not all business applications are as crucial as other mission-critical workloads requiring high availability. Scaling up to multiple replicas can be more than sufficient in some scenarios. So, designing a read-scale availability group can help you cut costs and preserve your resources. Although high availability isn’t possible with this solution, we still can use it for disaster recovery solutions. You can configure Always On availability groups with synchronous commit mode, so you can get the recovery point objective (RPO) you want.

Architecture

Our architecture has two SQL servers hosted within each fault domain. Our bastion host and Windows domain controller reside in fault domain 1 with the primary SQL Server compute instance.

We used the following components in this environment:

• SQL Server 2019 Enterprise Edition

• Bastion server hosted in a public subnet

• Domain controller server hosted in a private subnet

We need the bastion server hosted in a public subnet to provide access to a private network from an external network. When we have logged into the bastion host, we can access the Windows compute instance where SQL Server is installed, or we can use it to connect to the Windows domain controller.

The architecture requires us to set up a bastion host in a public subnet and Windows availability domain services on a Compute instance in a private subnet. After completing this setup, we must ensure that SQL Server is installed on both Compute instances. For installing the SQL Server on a Compute instance, you can go through Deploying Microsoft SQL Server on Oracle Cloud Infrastructure.

After installing the SQL Server either on Compute instance manually or utilizing a pre-configured image from the Oracle Cloud Marketplace, we add the primary replica and secondary replica to the active directory domain, which was a part of the workgroup until now. We’re doing these steps because we don’t have an underlying cluster. It requires both Compute instances — the primary and secondary replicas — to be added as a part of the domain we have created to communicate with another Always On replica. Both Compute instances must have SQL Server installed to enable the Always On Availability group. To enable that option, refer to a previous blog in this series, SQL Server Always On availability groups on Oracle Cloud Infrastructure..

Authentication

Because we have no underlying cluster, communication between replicas, or synchronization to function appropriately, the availability group needs to authenticate over the endpoint, which we can achieve through one of the following ways:

1. With a service account

2. With SQL authentication

If you want to use the service account option, all your replicas must be joined to the same domain, so that SQL Server can authenticate using the service account. Using the service account requires you to create a login explicitly on each SQL Server replica using the following command:

CREATE LOGIN [<domain>\Service Account] FROM WINDOWS;
</domain>

For more details, you can refer to the LiveLab we created for Read Scale Availability Groups on OCI. This LiveLab is a step-by-step process of implementing read-scale availability groups using the service account option.

Sometimes, the secondary replicas aren’t joined to an active directory domain. In such cases, we recommend using SQL authentication. With SQL authentication, you must create a login, a user associated with that login, and a certificate. Run the following command:

CREATE LOGIN <loginname> WITH PASSWORD = ’**<password>**’;
CREATE USER username FOR LOGIN <loginname>;</loginname></password></loginname>

Using SQL further requires certificate authentication for authentication between mirroring endpoints. The process for using certificate authentication uses the following steps:

1. Create a master key and certificate on the primary replica.

2. Back up the certificate and secure it with a private key.

3. Copy your certificate and private key to the same location on all replicas.

4. Ensure that your service account for the SQL Server instance has permission to access the certificate.

5. Create the certificate on the secondary replica.

6. Create database mirroring endpoints on all replicas.

For detailed steps, see Read Scale with SQL Certificate.

After completing these steps, we need to create an availability group in SQL Server as explained in SQL Server Always On availability groups on Oracle Cloud Infrastructure. The only difference here is that we need to choose the cluster type NONE because we have no underlying cluster.

After you create the availability groups using cluster type NONE, choose one of the following options for failover:

• Manual failover without data loss

• Forced manual failover with data loss

Because we’re using this setup as only a disaster recovery solution, the configuration has no option to automatically fail over to another replica.

Conclusion

In this blog post, we showed you how read-scale availability groups are a great option to deploy on OCI in scenarios without high availability. Read-scale availability groups are perfect for offloading workloads to multiple secondaries. Deploying SQL Server on OCI offers a better price-to-performance ratio, and read-scale availability groups on OCI give better cost-effectiveness. It eliminates the need to manage the traditional cluster setup, which is another cost and operational overhead.

In most cases where high availability is an essential part of the business configuration, traditional Always On availability groups remain the preferred choice for customers running on OCI.

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