If you are using Azure SQL DB Premium tier or Azure SQL Managed Instance (Business Critical Tier), you will benefit from using the read-only replicate provided to lighten the workload on your read-write database and avoid unwanted locks.
This article will provide guidance to explain how to do this on Power BI and Azure Analysis Services (for Power Query type connections and not legacy mode).
Power BI
This is the easy part, when importing data from SQL Server, all you have to do is check “Enable SQL Server failover support”. And if a read replica is available, it will be used.
In order to check if it worked, we can use the following query:
CREATE VIEW dbo.Properties AS
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Readability By adding it in a view, I can add it like my other tables to my model and check once the table is loaded that it is in READ_ONLY.
Azure Analysis Services
As far as Analysis Services is concerned, it is not much more complicated, in fact we find this same option of activating the support for failover in the connection options.
It become more complicated when you want to define it via code (for your ci / cd for example) you will have to pay attention to the case and ensure that your connection JSON looks like:
"dataSources": [
{
"type": "structured",
"name": "AdventureWorks",
"connectionDetails": {
"protocol": "tds",
"address": {
"server": "lab-file.database.windows.net",
"database": "blog"
},
"authentication": null,
"query": null
},
"options": {
"commandTimeout": "P5D",
"MultiSubnetFailover": "true"
},
"credential": {
"AuthenticationKind": "OAuth2",
"PrivacySetting": "Private"
}
}
]You can find the option with SSMS on the connection details:
The only property needed is the option:
"MultiSubnetFailover": "true" No need to specify the read itent property contrary to what some old articles on the Internet may suggest.
You can then test that this is in effect with the same method exposed for Power BI.
Conclusion
If you are using Azure SQL Premium or Azure SQL Managed Instance (Business Critical Tiers) do this setup! This helps lighten your workload and you pay for it so use it!
