1

I want to connect a Read-Only replica for reporting purpose in Azure SQL managed instance. For this, I tried to add an ApplicationIntent=ReadOnly parameter.

But it is still it not connecting the Read-Only replica. So is there any configuration required to connect Read-Only replica in Azure MI?

Software Engineer
  • 15,457
  • 7
  • 74
  • 102
NP007
  • 659
  • 8
  • 21
  • Did you get any error message? Run this query `SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability')` to verify whether you are connected to a read-only replica . – Leon Yue Mar 19 '20 at 08:27
  • Yes Leon I tried with same query. But it shows READ_WRITE. Just want to confirm like this Azure MI feature support in all tiers of MI or just in premium tier we get this feature. – NP007 Mar 19 '20 at 08:34
  • Hi @NP007, please see my answer. – Leon Yue Mar 19 '20 at 08:59

2 Answers2

3

Azure SQLMI Business critical service tier has Built-in additional read-only database replica that can be used for reporting and other read-only workloads similar to azure sql database premium tier. It is enabled by default no user action required. You need to use 'ApplicationIntent=ReadOnly' flag to access internal replica.

Shashikant Shakya
  • 114
  • 5
  • Thank you Shashikant for your reply. Just to confirm like only Business Critical service tier support read only replica. Is General purpose service tier not support this read only replica feature? – NP007 Mar 19 '20 at 09:51
0

Azure support the replica feature , please see this tutorial Configure replication in an Azure SQL Database managed instance database.

But for ApplicationIntent=ReadOnly, it's not supported or applies to in Azure SQL managed instance.

You can reference this document: SQL Server Native Client Support for High Availability, Disaster Recovery.

enter image description here

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23
  • Looks like they updated the document. Adding this to help others. https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/sql-server-native-client-support-for-high-availability-disaster-recovery?view=sql-server-ver15#specifying-application-intent – Pradeep Mahdevu Feb 24 '21 at 00:41