Connect to Azure SQL replica from Azure App Service-CodePudding

I have an Azure App Service that connects to an Azure SQL Database. The Database has a READABLE geo-replica in a different region (different SQL server and different SQL Database name).

I need to connect to the read-only replica from code and I thought it will be enough to:

get the connection string from the main database
add to it ApplicationIntent=ReadOnly

But then when I check the queries that run on the SQL database, I see nothing on the replica and on the primary database I see queries that should have targeted the read-only replica. This is the query I use:

SELECT
    deqs.last_execution_time AS [Time], 
    dest.TEXT AS [Query]
 FROM 
    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY 
    deqs.last_execution_time DESC

I expect to see the queries on the replica, but instead they appear on the main database. So not sure what am I doing wrong.

I have tried to create an SQL user on the replica and use that one, but I got this message:

Msg 3906, Level 16, State 2, Line 7
Failed to update database "test-replica" because the database is read-only.

CodePudding user response：

Only a premium/business critical local readable replica can be redirected with ApplicationIntent. With Active geo-replication you choose between the two server names, and Auto-failiover groups adds "a stable connection endpoint and automatic geo-failover support in addition to geo-replication".