CODEWITHSUNDEEP
c#azure-sql-database
Henrik
Henrik

Reputation: 342

How to read data from Geo-Replication Secondary database?

I have an Azure SQL (S3) geo-replicated read-only database. My problem is that when I query the Read-only from VisualStudio I can see the Query is hitting the Master database. I expect the Query to hit the Read-only database.

But if I run the same Query from SMSM connected to Read-only then I can see the Read-only database is hit. This works as expected.

To see the last Query in each database I use following SQL.

SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql ORDER BY execquery.last_execution_time DESC

Question

Why is my c# (entityframework 6.0) code not showing in the Read-only database?

Background

The goal is to have a read-only SQL to handle the external API load so the Master SQL is not to load.

In Azure Portal, I created a Geo-Replication SQL in the same region as Master.

The connection string is set to Read-only database. I tried[ApplicationIntent=ReadOnly] flag in connectionstring with no success.

Upvotes: 3

Views: 445

Answers (1)

Henrik
Henrik

Reputation: 342

The problem was old format on the connectionstring "User ID".
I used "username@server". The @server was pointing to the server of Master Sql.
I changed to only "username" and now it works.

Upvotes: 1

Related Questions