Written by SelvaKumar K, Sr. Database Administrator at Powerupcloud Technologies.
What is Failover Clustering?
A failover cluster is a group of independent computers that work together to increase the availability and scalability of clustered roles (formerly called clustered applications and services). The clustered servers (called nodes) are connected by physical cables and by software. If one or more of the cluster nodes fail, other nodes begin to provide service (a process known as failover). In addition, the clustered roles are proactively monitored to verify that they are working properly. If they are not working, they are restarted or moved to another node
What is AlwaysOn Availability Group?
An availability group supports a replicated environment for a discrete set of user databases, known as availability databases. You can create an availability group for high availability (HA) or for a read-scale. An HA availability group is a group of databases that failover together. A read-scale availability group is a group of databases that are copied to other instances of SQL Server for read-only workload
What we cover in this,
- Implementing Windows Failover Cluster (WFSC) in AWS Cloud and configure Always-on Availability Group between two Windows Servers
2. As like the On-Prem server, we can install and configure the WSFC Cluster and SQL Server 2017 “AlwaysOn Availability Group” in AWS Cloud to access the SQL Database Server outside the world with AG Listener on 99.99% uptime.
3. To implemented SQL Server Always-on with Minimal configuration instances and SQL Server 2017 Developer Edition. We have configured Alwayson without shared storage If you want to do the shared storage use a storage gateway in AWS service.
Implement Prerequisites from AWS :
- AWS VPC ( ag-sql-vpc )
2. AWS Subnets ( two private and two public subnets )
Launch and Configure the Server Infrastructure :
It requires three EC2 instances for Always-on Setup and it is indifferent Availability Zones, Minimum requirement for SQL Server Instances is t2.small
Our setup is configured without shared storage, add 50 GB additional disk on each EC2 instance. In addition, secondary IP’s need for windows cluster resource and AG Listener
Disk and Secondary IP for the EC2 Instances :
Security Groups :
Each EC2 instance security group allowed for all ports between Active Directory and SQL Server Instances
Implement and configure Active Directory Domain Service :
Active Directory domain ( agsql.com ) is to be configured in ag-sql-AD server, add SQL Server instances ( ag-sql-node1 and ag-sql-node2 ) in agsql.com domain
Implement and Configure WFSC :
We need to do multiple reboots once SQL Server instance configured with agsql.com active directory domain account. Let’s start to configure failover clustering roles on each server
Failover clustering role needs to be added in both servers and start creating clusters with your own steps
Adding the SQL Server nodes in Create Cluster and perform all necessary tests for windows cluster creation
Assign the secondary IP’s for the Windows cluster and bring online the cluster resources. Once cluster resource is ready, parallelly start installing SQL Server 2017 Developer editions in SQL Server Instances
Once SQL Server Installation is completed, Enable AlwaysOn Availability Group in SQL Server Service and restart the SQL Service on Both SQL Server Instances.
So, We are ready with Windows failover clustering and SQL Server Setup on both instances. Start creating AlwaysOn Availability Group and Configure AG Listener
Step 1: Specify Name for the Always-on Group
Step 2: Connect the replica Node for AlwaysOn Group
Step 3: Specify the secondary IP addresses for AG Listener
So, the aglistener will be added in Active Directory DNS Name and it will be connected from the outside world to access the SQL Servers with respected IP Addresses. We will able to ping or telnet the aglistner from the agsql.com domain account
Step 4: AlwaysOn Dashboard to check Database Sync Status
DNS and Active Directory Computers Configuration didn’t cover in this Setup, those are automatically created in the Active Directory Server
Finally, AlwaysOn Availability Group Ready in AWS Cloud !!!