SQL Server Always On availability group cluster in the AWS Cloud

Microsoft gives HA features like a charm . Lower to higher deployment costs its giving many features as per business requirements . Replication , Mirroring , Log shipping and Always On many features available to build HA Setup in On Premises . 

Like wise , we can setup all the above features in Cloud as well . In that we can see Always on availability group cluster in this blog 

What is Always On 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 read-scale. An HA availability group is a group of databases that fail over together.  

Environment Setup for Always on Availability Group ,

Launched one Active Directory and two SQL Nodes with below range . Detailed setup for environment steps are below ,

Below are the detailed steps for environment steps ,

Step 1 : Create ag-sql-vpc with 10.0.0.0/16 IPv4 CIDR range

Step 2 : Create two private subnets ( 10.0.3.0/24 , 10.0.1.0/24 ) for SQL Nodes and one public subnet ( 10.0.4.0/24 ) for Active Directory  

Step 3 : Launched the windows instances with two secondary ip's for Failover Cluster and Always on Listener

In this POC Setup , Launched windows instance and installed SQL Server Developer edition . Also we can launch Windows with SQL Server 2016 based on your requirements 

Step 4 : Change the computer properties and rename the instance names accordingly 

Step 5 : Completed the AD Server configuration and its named as ag-sql-AD , After that change DNS server address in network properties in ag-sql-node1 and ag-sql-node2 ( 10.0.4.33 is static IP of AD Server )

Step 6 : Once modified the DNS configuration reboot the server and login with AD administrator account 

Step 7 : Once logged in with AD login , Install the failover clustering and below dependent features in ag-sql-node1 and ag-sql-node2

Configuring Shared Drive for Backup and Restore 

Step 8 : Between the ag-sql-node1 and ag-sql-node2 needs to take backup and log backups for Always on background process

Step 9 : Create folder in ag-sql-node2 and share with everyone in AD account

  

Step 10 : Take one time backup of DW_Mart and DataLake in that shared folder . Created Shared drive will be used while always on group creation 

Failover Cluster Configuration 

Step 11 : Open the Failover Cluster Manager console and Create the cluster . Browse and add the both servers 

Step 12 : Once all the steps finished , create the cluster wizard 

Step 13 : Click agsqlprod failover cluster and modify the cluster core resources . In this we need to add secondary IP for both nodes ( 10.0.1.11 and 10.0.3.11 )

Once we added both secondary IP's one of the IP will be come to online 

If we have not added secondary IP , it will show as an error like below 

Configuring SQL Server Services  

Step 14 : Once all the steps are completed on Failover cluster manager , modify the SQL Service Account to AD service account 

Step 15 : Next right click the SQL Server Service in configuration manager and enable the Always on High Availability on ag-sql-node1 and ag-sql-node2 SQL instances 

Create and Configuring the Availability Group 

Step 16 : Right click the always on group wizard and create the availability group as agsqldb

Step 17 : Based on the requirements add the number of replicas ,

Step 18 : Below are the endpoints and make sure allowed below ports between the cluster nodes 

Step 19 : Then create availability group listener with remaining secondary IP ( 10.0.1.12 and 10.0.3.12 )

Step 20 : Once everything is completed click Next to create availability group 

Once its created we can able to see the Cluster Manager Role in the Failover cluster manager console ,

Ready to sync the Data from Primary to Secondary 

After all availability group is healthy and primary and secondary nodes are synchronized 

Thanks for Reading !!! Any corrections or any doubt please contact me directly !!!