How to configure externally accessible SQL Always On Availability Group Listener in AWS

Scenario:
Recently I had a customer who was running 2 node SQL Always On Availability Group in AWS cloud and had a requirement to access their SQL AG Listener name over the internet.  First I explained that this is not recommended and why exactly he wants to do it. Customer mentioned that he really has a use case and wants to do it so looking for the way how to accomplish it. Customer was referring this Microsoft documentation that talks about how to create external SQL AG listener in Azure and wanted to have similar thing for his SQL environment in AWS. Customer didn’t want to expose their SQL servers to public internet and kept it in private subnet.

I have my 2 node SQL 2017 AlwaysOn Availability Group cluster already configured and running on EC2 instances in my lab, so based on the customer’s requirement I went ahead and thought of first setting this up in my lab and see how it works.

Note: If you are looking for step by step documentation how to configure SQL Always On Availability Group cluster in AWS then please refer my previous blog post about this.

Lab Environment Details:
——————————————————————————————————–
SQL-NODE1.CloudMegh.local
172.31.37.186- Primary IP (Statically assigned at OS level on the EC2 instance)
172.31.37.34 – Cluster IP (Static IP for Cluster CNO)
172.31.37.49 – SQL AG Listener IP

SQL-NODE2.CloudMegh.local
172.31.31.138 – Primary IP (Statically assigned at OS level on the EC2 instance)
172.31.31.139 – Cluster IP (Static IP for Cluster CNO)
172.31.31.140 – SQL AG Listener IP
————————————————————————————————————

Architecture Diagram:

High Level Steps:
1. Allocated two Elastic IP Addresses in my AWS account.

2. Created an internet-facing NLB in both Availability Zones as SQL cluster nodes. It takes between 5-10 minutes to provision the NLB with along with NLB targets.


3. Configured NLB Targets providing my SQL AG Listener’s IP, please refer below screenshot.




Note: Primary SQL cluster node (currently in my case SQL-NODE1) would be showing as HEALTHY in NLB Target because it’s the one that holds SQL AG Cluster role at the moment so NLB would be forwarding the requests to that node only. If you failover the node manually or if it failovers automatically then it will change the status in NLB Targets as well automatically.

4. I have not specified any custom ports for Health Checks in NLB Target Group, I left to use default traffic port which is port 1433 in my case. But if you have if you are different port for NLB Health Check then you can specify the one, in this case if you would possibly have to change ProbePort for Cluster Resource in Windows Failover Cluster as well.



5. Once I completed above configuration, then I tested connectivity from my local machine to my SQL node (SQL running on EC2 instances) successfully using UDL over internet. The SQL server FQDN specified in UDL is my NLB FQDN.



Note: When connecting SQL from outside of your Active Directory Forest/Domain boundary you would require to use SQL Server Authentication because Windows Authentication will not work in this scenario. If your SQL servers are not configured for MIXED mode authentication then you need to configure the SQL servers accordingly and create local SQL logins.

6. Then for further testing, I went ahead and installed SQL Server PowerShell module on my local machine and the connected SQL server remotely using NLB FQDN and executed a simple SQL query successfully.



Note: Please note that it’s not required to have SQL cluster nodes in public subnet to make this work because the SQL Nodes are sitting behind NLB and any external connection would hitting to NLB FQDN not the SQL server directly.

This is not a common scenario where people would prefer to access SQL server over internet specifically security reasons but if you really have such use case then you can use NLB for this kind of use case.

I hope you find this post helpful, please comment if any correction needed.

Leave a Reply

Your email address will not be published.