SQL Azure DB Read-Only Replicas for Power BI and Azure Analysis Services

Back in March 2019 Microsoft enabled some new functionality for the Premium and Business Critical tiers for SQL Azure DB which enables users to connect to a read scale-out copy of the database. I won’t be detailing on how to enable this, as it is quite straight forward, and the documentation located here covers it very well.

So what does this have to do with Power BI and Azure Analysis Services? The question we then want to ask is:How do you connect to the read-only replica from Power BI, if you are using the standard Azure SQL Database connection?

When you are connecting to a read-only replica with a more common driver like ODBC or OLEDB it is quite easy to point your connection to the read-only replica by adding the following option into your connection string:

ApplicationIntent=ReadOnly

After a few rabbit holes, I found the following comment for Azure Analysis Services on GitHub:

When you set MultiSubnetFailover=true, we will also set ApplicationIntent=ReadOnly.

MultiSubnetFailover is set by enabling the following flag when you set up your connection:

To verify that your connection is in fact hitting the read-only replica, you can do the following:

• Connect to the read-only replica using your preferred editor, in my case it is Azure Data Studio
• On the connection properties ensure that you have set the “ApplicationIntent” as “ReadOnly”
• Execute the query below:

SELECT [session_id], 
       [host_name], 
       [program_name], 
       [host_process_id]
FROM sys.dm_exec_sessions
WHERE   [database_id] = <your_database_id>
		AND [program_name] = N'Mashup Engine';

For my testing, I get the following result back where we can draw some conclusions:

• The connection from RD00155D36A4AE is a test from the Power BI portal
• host_process_id 12708 and 4188 is originating from Power BI Desktop and this is validated by looking at the process_id in Task Manager:

The steps required for Azure Analysis Services is exactly the same as described above since it shares the same M query connection builder with Power BI.

Conclusion

The ability to use the read-only replica is very valuable, considering there is no additional cost for this (at the time of writing this post). It is still recommended to thoroughly go through the documentation linked in the beginning of this post, and to assess it for your environment.