Today, I worked on a service request that our customer wants to use the parameter of ApplicationIntent=ReadyOnly to connect to the secondary replica of Transparent Failover Group. Unfortunately, it doesn't work because ApplicationIntent has other purpose for Azure SQL DB and Managed Instance, I would like to explain it below.
1) Remember that every time that you create a Premium or Business Critical database in Azure SQL Database or Business Critical for Azure SQL Managed Instance will be created two additional replicas that will be synced using AlwaysOn environment.
2) If you want to connect to any of these replicas, basically, you need to add in the connection string the parameter ApplicationIntent=Readonly - https://docs.microsoft.com/en-us/azure/azure-sql/database/read-scale-out
3) If you have a Transparent Failover Group defined and you specify ApplicationIntent=Readonly in the connection string, the result will be that you are going to connect to any replica of the server that is the primary (ReadScale Out) and will not routed to the secondary server. Let me share with you an example:
- We have two servers: jmserver100 (Primary) and jmserver200 (Secondary).
- These servers are part of a Transparent failover group under this listerner - jmserver300.
- Using SQL Server Management Studio, I'm going to connect to listener jmserver300 that points to the primary server jmserver100.
- Using SQL Server Management Studio, I'm going to connect to listener jmserver300 that points to the primary server jmserver100 but I'm going to add the parameter ApplicationIntent=Readonly.
- If you need to connect to the secondary server of this Transparent Failover Group my suggestion is to use the secondary FQDN, for example, jmserver300.secondary.database.windows.net
Enjoy!!