Point Azure Hybrid Connection to Availability Group Listener

availability-groupsazuresql server

We have 2 SQL Servers on-premise, combined into Availability Group

We want to install windows updates, which will most likely require a reboot of virtual machines, and we are going to use rolling update technique (update secondary replica; fail over to secondary so it becomes primary; update former primary)

Applications that are located on the on-premise web server, are going to be fine, since web-servers' connection strings are pointed to AG Listener

But we also have apps running in Azure, 
which are connecting to on-premise DB (to primary DB replica directly) using hybrid connections. 
Azure Apps are going to be down if primary replica is down

Question: is there any way that we can point Azure hybrid connections to AG listener ? Or at least redirect hybrid connection to secondary replica if primary is not available for 1-2 mins ?

Best Answer

You should be able to connect to the AG listener from Azure to On-premises or vice versa--though it's not as straight forward as you might think. Here's one of the best article's I've ready today on how to do it.

https://www.concurrency.com/blog/w/creating-a-listener-for-a-multi-subnet-hybrid-or-a

When the AG primary node is on-premises (or in Azure), you should be able to connect to the listener. So you're relying on Azure DNS to do it's job properly and resolve your SQL Azure node correctly. Haven't tried it myself--sounds awesome!

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

Sql-server – Always On Availability Group, Always redirect user to read only instance

I don't have an answer to your entire question (though I gave an answer to a similar question today https://dba.stackexchange.com/a/137844/36812) but you mentioned that using ApplicationIntent=ReadOnly doesn't work properly.

Have you set up read only routing URLs? Because it's not done out of the box and if you don't do it then these settings and that flag won't work. I think if you got that working then you could start to re-evaluate your requirements.

Instructions on MSDN https://msdn.microsoft.com/en-us/library/hh710054.aspx and most easily done in PowerShell.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Sql-server – Always On Availability Group, Always redirect user to read only instance

Related Question