If you are dealing with Always on availability groups, then you are familiar with the Listener. Its like an endpoint for your Primary Server in availability groups. It’ll swap the DNS to the elected primary during the failover. So you don’t need to change anything in your connection string. You can add or remove SQL Server nodes at any time.

What is this External Listener?

Its better you can read the explanation from Microsoft.

Your Availability Group can contain replicas that are on-premises only, Azure only, or span both on-premises and Azure for hybrid configurations. Azure replicas can reside within the same region or across multiple regions using multiple virtual networks (VNets). The steps below assume you have already configured an availability group but have not configured a listener.

Why External Listener?

This listener address basically a CNAME record.

If you want to talk to the Database, its mandatory that your system should be a part of the Active Directory Domain.

Some of your API servers are in different region/cloud/somewhere even though if you have VPN, the DNS server’s IP should be configured as your Active directory.

If you want to access the Primary Server anywhere in the world, then the CNAME will never help you outside the domain network.

Before moving into deep, I want to recommend that, Privately accessible listener are good. But never use the publically accessible listeners. In my case, just showing that its possible not only on Azure.

External Listener on GCP:

We have done a SQL Server migration recently from On-Prem to GCP. 90% of the app servers are already in GCP. But few application servers are still on the Datacenter. But those servers should access the Primary Server. And we have a site to site VPN between GCP and On-Prem. So instead of changing something on all the app servers, we decided to use an External Listener with private IP only.

How does this work?

In GCP, we have the Primary Server on Zone A, Secondary on Zone B and a Fileshare witness on Zone C.

All are in the same subnet. GCP provides a subnet can span across the entire region. So any servers inside the Subnet will be on any Zone.

So NO Multi subnet always on .

. Reserve 2 static IP addresses for Cluster and Listener.

On WSFC, create a Client access point IP address(which is your Listener IP)

On Cluster, parameter add Probe port. Probe port like a health check port which will listen only on the Primary Node.

Create an instance group with Named Port (your SQL server port).

Create a TCP internal load balancer with the health check port (your Probe port)

Use the load balancer IP on your applications.

Pre-Requirements:

From configuring AD to Always-on availability groups, I skipped here. You can find a lot of blogs for that. So Im just showing after the always-on availability group has been created.

Setup Active Directory. Configure 2 SQL servers. Configure the Windows Server Failover Cluster. Configure SQL server always-on availability group without Listener.

Reserve the IP address:

Once you configured WSFC, you should assign an IP address for that. We need to reserve this IP and the listener IP(listener you are going to create in a while). So reserve these IP address from your subnet.

gcloud compute addresses create cluster-ip --region us-central1 --subnet default --addresses 10.128.0.117 gcloud compute addresses create listen-ip --region us-central1 --subnet default --addresses 10.128.0.228

Create an availability group Listener:

We are going to create this Listner from Failover Cluster not from SSMS.

Go to Failover Cluster Manager → Expand the cluster → Roles.

Right-click on the Always-on availability group name → Add Resources → Client Access Point.

Name: listener-ip Then click Next on other windows.

Then click Next on other windows. Now your Role will show Partially Running.

Click on the Always-on availability group name, at the bottom Resources → listener-ip.

Expand listener-ip → Right-click on the IP Address: Address on the Cluster Network → properties.

→ properties. Name: listener-ip

IP Address: Static IP Address

Address: 10.128.0.228 (This is the IP you reserved from the subnet)

Add Probe Port:

Now we need to add the Probe port to the cluster resource. Open Powershell anyone on the Cluster node and run this command. Please change the parameters based on your system.

ClusterNetworkName: Failover Cluster Manager → Expand Cluster, Under Networks, you can find this. IPResourceName: Name of the IP address which you assigned the above step (Roles → Availability Group Name → Resources → Under server name Expland Name: listener-ip → Right Click on IP Address: 10.128.0.228 → there you can see the name)

I took this screenshot from a different server, but it should show IP Address: 10.128.0.228

ILBIP: Listener IP address which you reserved from the subnet and created in the Client access point. ProbePort: Any TCP port for the health check.

$ClusterNetworkName = "Cluster Network 1"

$IPResourceName = "IP Address 10.128.0.228" ##You can see this

$ILBIP = "10.128.0.228" Import-Module FailoverClusters Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="1444";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

Validate the configuration.

Get-ClusterResource $IPResourceName | Get-ClusterParameter

I took this screenshot from a different server, the Object should show IP Address: 10.128.0.228

Bring Up the Listener:

Roles → Availability Group Name → Resources

Expand the Listener IP → Right click the IP address → Bring Online.

Right click on lister IP → Bring Online.

Right Click the availability group name → Bring Online.

Create dependency on Listener Name:

On availability group, create a dependency for the listener. So this Listener IP and the probe port will be handled by the primary node.

Failover Cluster Manager → Roles → AG name → Resources → AG name → Properties → Dependencies.

Assign Port Number for Listener:

Open SSMS → Alwayson High Availability → availability groups → AG Name → Availability Group Listener → listener-ip

Go to properties → Port: 1433

Final Step from Cluster:

To prevent the connection timeout, we need to adjust the RegisterAllProversIP and HostRecordTTL .

$CAPName = 'listener-ip' Get-ClusterResource $CAPName | Set-ClusterParameter -Multiple @{"HostRecordTTL"=300;"RegisterAllProvidersIP"=1}

Validate:

Get-ClusterResource $CAPName | Get-ClusterParameter

Create Instance Group:

If you have launched all the SQL servers in a single Zone, then in Instance group we can add all the SQL servers. Else for each SQL server, we need to create instance group. Here my both primary and secondary SQL servers are in the same zone. So one instance group is fine.

Go to Compute engine → Create Instance group → Unmanaged instance group.

Name: use any name

Location Single Region

Region: Your Vm’s region.

Zone: Your VM’s zone.

Specify Port name mapping: sql:1433

Network & Subnet: SQL server’s Network and subnet.

VM instances: add your SQL server’s which are in this zone.

Create Internal Load balancer for Accessing with Private IP:

Network Services → Load Balancing → Create Load Balancer → TCP load balancing

This is the internal one, so select Only between my Vm's and Single region only.

and Single region only. Give any friendly name.

Backend Configuration:

Region: your SQL server VM region. Network & Subnet: SQL server’s Network and subnet. Instance group: select your VM instance group. If you have multiple instance group(primary and secondary are in a different zone), then click Add item and then add the next VM instance group. Health Check → Create another health check

Name: any name

protocol: TCP

Port: 1444 (your probe port)

Check Interval: 2

Timeout:2

Healthy Threshold: 2

Unhealthy Threshold: 3 Save

FrontEnd Configuration:

Name: Any name Subnet: Select the subnet where you want to deploy this ILB Internal IP: Select the Listener Ip which you reserved at the beginning. Ports: single 1433 Save and create

The above screenshot, Under the Healthy its 1/2 means the Primary is listening 1444 port. So if you connect your applications, with this IP, it’ll route the request to the current primary.

NOTE: Make sure you need to create a firewall rule which allows GCP’s network services should talk to your SQL VM’s with 1444 port.

You can download this software to test the failover.

My output:

8/07/2019 8:57:02.73 : bhuvi-sql1

8/07/2019 8:57:02.73 : bhuvi-sql1

8/07/2019 8:57:02.74 : bhuvi-sql1

8/07/2019 8:57:02.75 : bhuvi-sql1

8/07/2019 8:57:14.06 : bhuvi-sql2

8/07/2019 8:59:48.98 : bhuvi-sql2

8/07/2019 9:00:06.00 : bhuvi-sql2

Create External Load balancer for accessing with Public IP:

Network Services → Load Balancing → Create Load Balancer → TCP load balancing

From internet to my VMs → Multiple Regions

Name: any name

Backend Configuration

Protocol: TCP

Named Port: sql (you have given this while creating instance group)

Timeout: 10

Backend types: Instance group

Instance group: Select your VM instance group

Port: 1433

Connections: 10000 per instance (or its your wish)

Capacity: 100%

Done

Protocol: TCP Named Port: sql (you have given this while creating instance group) Timeout: 10 Backend types: Instance group Instance group: Select your VM instance group Port: 1433 Connections: 10000 per instance (or its your wish) Capacity: 100% Done If you have multiple instance group(primary and secondary are in a different zone), then click Add item and then add the next VM instance group.

Health Check: Pick the health check which you created for the previous LB.

FrontEnd Configuration:

Name: Any name

Protocol: TCP

Network Service Tier: Premium (Or its your wish)

Port: This external LB does not support custom port. So pick any one of the available port. I choose in 5222.

Done & Create

If you want to access from this LB, you need to connect to SQL server with the port 5222.

Testing:

8/07/2019 11:35:22.65 : bhuvi-sql2

8/07/2019 11:35:22.86 : bhuvi-sql2

8/07/2019 11:35:22.93 : bhuvi-sql2

8/07/2019 11:35:22.93 : bhuvi-sql2

8/07/2019 11:35:23.00 : bhuvi-sql2

8/07/2019 11:35:46.89 : bhuvi-sql1

8/07/2019 11:35:46.90 : bhuvi-sql1

8/07/2019 11:35:46.90 : bhuvi-sql1

How about Read Intent only routing?

You can configure Read Only routing without any issues. But you need to make sure you have given 1433 port for read-only URL. Else it’ll not connect.

ALTER AVAILABILITY GROUP [ag-bhuvi]

MODIFY REPLICA ON

N'bhuvi-sql1' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [ag-bhuvi]

MODIFY REPLICA ON

N'bhuvi-sql1' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://bhuvi-sql1.bhuvi.local:1433')); ALTER AVAILABILITY GROUP [ag-bhuvi]

MODIFY REPLICA ON

N'bhuvi-sql2' WITH

(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)); ALTER AVAILABILITY GROUP [ag-bhuvi]

MODIFY REPLICA ON

N'bhuvi-sql2' WITH

(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://bhuvi-sql2.bhuvi.local:1433')); ALTER AVAILABILITY GROUP [ag-bhuvi]

MODIFY REPLICA ON

N'bhuvi-sql1' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('bhuvi-sql2'))); ALTER AVAILABILITY GROUP [ag-bhuvi]

MODIFY REPLICA ON

N'bhuvi-sql2' WITH

(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('bhuvi-sql1')));

Downside:

We may solve 99% problems, but we are using some creak kind of things here to access the listener from outside. So, for now, I found two issues.

Read only routing URL must use 1433 port(see the above section) Windows authentication will not work outside the VPC.

Conclusion:

Generally, its a best practice to use native things, instead of doing these kinds of funny things. But it was only available on Azure, now we implemented this on GCP. Private Load balancers are good, but don’t use Public one. And these steps will not work on AWS. We’ll try this on AWS and post it soon.

Happy Always on :)