Deploy a Highly Available Microsoft SQL Server Database
Deploy Microsoft SQL Server Always On availability groups on Oracle Cloud Infrastructure to take advantage of the built-in redundancy and resiliency features of Oracle Cloud.
Architecture
This reference architecture includes a Microsoft SQL Server Always On availability group, bastion servers, Active Directory domain controllers, and a quorum witness host on Oracle Cloud Infrastructure.
The following diagram illustrates this reference architecture.
The architecture has the following components:
- Region
An Oracle Cloud Infrastructure region is a localized geographic area that contains one or more data centers, called availability domains. Regions are independent of other regions, and vast distances can separate them (across countries or even continents).
- Availability domain
Availability domains are standalone, independent data centers within a region. The physical resources in each availability domain are isolated from the resources in the other availability domains, which provides fault tolerance. Availability domains don’t share infrastructure such as power or cooling, or the internal availability domain network. So, a failure at one availability domain is unlikely to affect the other availability domains in the region.
- Fault domain
A fault domain is a grouping of hardware and infrastructure within an availability domain. Each availability domain has three fault domains with independent power and hardware. When you distribute resources across multiple fault domains, your applications can tolerate physical server failure, system maintenance, and power failures inside a fault domain.
- Virtual cloud network (VCN) and subnets
A VCN is a customizable, private network that you set up in an Oracle Cloud Infrastructure region. Like traditional data center networks, VCNs give you complete control over your network environment. You can segment VCNs into subnets, which can be scoped to a region or to an availability domain. Both regional subnets and availability domain-specific subnets can coexist in the same VCN. A subnet can be public or private.
This architecture uses different subnets to host the bastion host, quorum witness server, database server, and domain controllers.
- Route table
Virtual route tables contain rules to route traffic from subnets to destinations outside a VCN, typically through gateways.
- Security lists
For each subnet, you can create security rules that specify the source, destination, and type of traffic that must be allowed in and out of the subnet.
This architecture needs ingress and egress rules in the security lists attached to the bastion host, quorum witness server, database server, and domain controller subnets. These rules must be added to enable features such as remote desktop connectivity, access to the SQL Server database, and access for the Always On availability groups endpoints.
- Internet gateway
The internet gateway allows traffic between the public subnets in a VCN and the public internet.
- Block volume
With block storage volumes, you can create, attach, connect, and move storage volumes, and change volume performance to meet your storage, performance, and application requirements. After you attach and connect a volume to an instance, you can use the volume like a regular hard drive. You can also disconnect a volume and attach it to another instance without losing data.
- Object storage
Object storage provides quick access to large amounts of structured and unstructured data of any content type, including database backups, analytic data, and rich content such as images and videos. Use standard storage for "hot" storage that you need to access quickly, immediately, and frequently. Use archive storage for "cold" storage that you retain for long periods of time and seldom or rarely access.
Recommendations
Your requirements might differ from the architecture described here. Use the following recommendations as a starting point.
- Compute shapes
This architecture uses the following shapes for different servers:
- Bastion server: Windows Server 2016 Standard on a VM.Standard2.1 shape
- Quorum witness server: Windows Server 2016 Standard on a VM.Standard2.1 shape
- Active Directory domain controller server: Windows Server 2016 Standard on a VM.Standard2.2 shape
- Database server: Windows Server 2016 Standard on a VM.Standard2.2 shape
If your application’s resource needs are different, you can choose different shapes for your database servers. The bastion servers are optional if you don't connect to the Windows servers over the public internet and instead connect your on-premises network to Oracle Cloud Infrastructure over a FastConnect or IPSec VPN connection.
- Virtual cloud network (VCN)
When you create the VCN, determine how many IP addresses your cloud resources in each subnet require. Using the Classless Inter-Domain Routing (CIDR) notation, specify a subnet mask and a network address range that's large enough for the required IP addresses. Use an address range that's within the standard private IP address space.
Select an address range that doesn’t overlap with any other network (in Oracle Cloud Infrastructure, your on-premises data center, or in another cloud provider) that you intend to set up private connections to.
After you create a VCN, you can't change its address range.
When you design the subnets, consider your traffic flow and security requirements. Attach all the resources within a specific tier or role to the same subnet, which can serve as a security boundary.
- Microsoft SQL Server version
Oracle Cloud Marketplace has images for Microsoft SQL Server 2016 Enterprise and Microsoft SQL Server 2016 Standard. You can use either of these images for ease of deployment or install your licensed copy of SQL Server on the compute nodes.
Considerations
Consider the following points when deploying this reference architecture.
- Scalability
This architecture uses one Microsoft SQL Server database, one domain controller, and one bastion server in each fault domain.
You can vertically scale the database, domain server, quorum, and bastion server by changing the virtual machine (VM) shape of each compute node. Using a shape with a higher core count increases the memory allocated to the compute instance and network bandwidth.
- Application availability
Fault domains provide the best resilience within a single availability domain. In addition, deploying compute instances that perform the same tasks across multiple fault domains provides redundancy and prevents a single point of failure.
- Cost
Select the VM shape based on the cores, memory, and network bandwidth that you need for your database. You can start with a two-core shape for the database server. If you need more performance, memory, or network bandwidth for the database node, you can change the VM shape later.
Deploy
You can deploy this architecture by using a image provided in Oracle Cloud Marketplace to provision a compute instance running Microsoft SQL Server that includes a license, or by provisioning a compute instance for each database node that you need, and then installing Microsoft SQL Server on each instance using your own license.
- Provision the required networking resources as shown in this reference architecture by using your preferred Oracle Cloud Infrastructure interface: the web console, CLI, API, or Terraform.
- Deploy the Microsoft SQL Server nodes by using one of the following options:
- Create Microsoft Windows Server compute instances and then install and configure the Active Directory domain controllers on the instances.