A screen with stock chart

How To Connect Power BI to Read-only Replica of Azure SQL Database & More

by Nate Wang, Consultant – Altis Melbourne

Using both Azure SQL Database and Power BI as Business Intelligence tools in your company?

Then this easy, but often neglected, functionality might help you supercharge your reports’ performance with no additional cost. Sounds good? Read on!

This blog post will cover:

  • The background
  • What is the Azure SQL Database read-only replica?
  • Benefit & Limitation
  • Methods & Step-by-step guide
  • Testing & Verification


One of our clients’ company is using the Microsoft BI services extensively, which involves Azure SQL Database and Power BI.

As we’re using the Premium tier of Azure SQL Database, it comes with an accessible read-only replica. So, naturally, a service like Power BI which only requires read-access is the perfect candidate to be switched to read-only replica. However, I didn’t manage to find any Microsoft official documentation on the method to do this.

After some research, I’ve found a very straight forward solution and it’s almost as easy as a click-of-a-button. And to save you the time and efforts to track down all the information and piece them together, I will include all my findings for you in this blog.

(If you know the relevant concepts already, you can jump straight to the step-by-step guide section.)

What is the Azure SQL Database read-only replica?

As part of the architecture for the ‘Always-on Availability Group’, the read-only replicas (or secondary replicas) of Azure SQL Database are provisioned (free of charge) to enable the feature of Read Scale-out. And one of them has endpoint set up, therefore, available for read-only access. (However, at any point of time, only one of the AlwaysON replicas is accessible by the ReadOnly sessions.)

Currently, of the three Microsoft Azure SQL Database offerings, two of them have read-only replica available for access:

General Purpose/Standard (no read-only replica available)

Hyperscale (read-only replica available, default to Disabled)

Business Critical/Premium (read-only replica available, default to Enabled)

As shown in the graph below, Business Critical instance that has one primary instance and three replicas where you can access read-only data in one instance using the read-only endpoint.I won’t go into details in this blog about the ‘Always-on Availability Group’, but you can find more information here.Also, Read Scale-out is a ‘General Availability’ functionality for Azure SQL Database since March 2019, so it has been proven to be reliable.Find out more about it here:

Use read-only replicas to load-balance read-only query workloads

General availability: Read scale-out support for Azure SQL Database (29 March 2019)

Benefits & Limitations

Benefits of Connecting to Read-Only Replicas

‘Increase’ compute power

‘double’ the compute power with no added cost (pretty awesome right?) – the main and read-only replicas are essentially separate database instances, so the accessible read-only replica can act as the 2nd database to process the read-only workloads.

Load-balancing for primary database instance

By off-loading some read-only workloads to the read-only replica, it frees up the resources on the main database instance for mission-critical workloads (e.g. Scheduled Data Ingestion/Processing) so that the resource bottleneck is effectively mitigated during ‘peak hour’ of the day.

Limitations of Read-Only Replicas

Data latency

sync with primary instance within seconds, but still shouldn’t be used by reports with real-time data or time-critical data.

Data consistency

data consistency is maintained with transactional logs and at a session level, so while a process is locking the resources in read-only replica, the changes in the main instance might not be reflected in the read-only replica immediately.

Rule of thumb

In short, if your report doesn’t contain any real-time data, it’s a good idea to use the read-only replica as a source.

However, you should keep the ‘balance of things’ too. Having a read-only replica with no additional charge is awesome, but it doesn’t mean you should point all your read-access processes to it. The balance of the workloads between the main and the read-only replica should be controlled and managed.

What are the ways you can connect to read-only replicas?


Power BI

SQL Server Management Studio

In this blog, I’ll go through all three methods of connecting to read-only replicas.

Step-by-step Guide: Connecting to Read-Only Replica


When you’re connecting to a database with read-only replicas available, the ‘ApplicationIntent’ option in the connection string provided by the client dictates whether the connection is routed to the primary instance or to a read-only replica. Specifically, If the ApplicationIntent value is ReadOnly, the connection is routed to a read-only replica. (the default value is ReadWrite). For example, the following connection string connects the client to a read-only replica (replacing the items in the angle brackets with the correct values for your environment and dropping the angle brackets):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

SQL Server Management Studio


Choose the server name

Click “Options” button and then “Additional Connection Parameters” and type ApplicationIntent=ReadOnly


Click the “Connect” button

Validating connection to read-only database:

Once you are in the Microsoft SQL Server Management Studio Query window.



You should be getting the result as ‘READ_ONLY’. This confirms you are in read-only database.

Power BI

How can Power BI access the read-only replica?

Since Power BI service only requires read-access to the Azure SQL Database, it seems like it’s the ideal type of service to point to the read-only replica.

However, since Azure Active Directory is not enabled in this case for Power BI, the conventional method of ODBC/JDBC connection is inapplicable. (as seen in the below snapshot)

OLE DB doesn’t support Microsoft Active Directory.

After some research, I’ve found a solution and the cherry on the top is that, with any exist Power BI dashboard, it’s as easy as selecting a button and re-publishing the dashboard.


Choose the server name and database name.


Click “Advanced options” button and then “Additional Connection Parameters” and select “Enable SQL Server Failover support”. Then click OK.

This enables MultiSubnetFailover (fail over function in MS availability group) and set ApplicationIntent to ‘read-only’ (to use the read-only replica of SQL DB).

Bonus – by enabling MultiSubnetFailover option in the process, this gives you all the added benefits of Always-on Availablity groups too, which ensure the availability of your reports and disastrous backups.


Click “Apply changes” button.

Testing & Verification

Verification of this Approach

Simply selecting a button to make this happened seems almost too easy.

So, we want to be sure that, with ‘Enable SQL Server Failover Support’ enabled, Power BI refresh process only connects to the read-only replica. We can prove this by monitoring the system tables on both the main and the read-only replica instances of Azure SQL Database.

After repointing an existing Power BI dashboard from the main database instance to the read-only instance, I refreshed the Power BI dataset.

Then, immediately, I’m tracking the Power BI refresh process in the database by monitoring the ‘Mashup Engine’. And as can be seen in the snapshots. This process only runs in the read-only database instance after the change.

Please find the SQL query here:











FROM sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions s ON  req.session_id = s.session_id

WHERE s.program_name = ‘Mashup Engine’;

An alternative way of verification can be found  here – (SQL server profiler monitoring at 8:45)

This is also supported by comments from Microsoft sources – (Miguel is a program manager at Microsoft – January 9, 2017)

Testing of Power BI Refresh Performance & Compute Usage

Overall, refreshing Power BI dataset takes roughly the same time to finish in the Primary instance and the Read-only Replica. Since it won’t clash with other workloads, there will be some potential performance boost as well.

For your reference, below are some details on the compute usage and refresh performance.

This is a relatively quick change but has a huge impact on the compute utilisation of the database.

Compute Usage – daily Power BI refresh linked to Primary Instance

100% DB compute usage for the duration of refresh (~15 min)

This may cause slower performance for other processes during this period (e.g. data ingestion, data processing and UI refreshes)

Compute Usage – Power BI dataset refresh linked to Read-only Replica

0% DB compute usage on the primary instance

Power BI Refresh Performance

roughly the same time to finish refresh (faster at times, possibly due to less clashing workloads)
Final Remarks

In this blog, we covered all things related to the Read-only Replicas of Azure SQL Database. If you have any question or issue that requires Power BI expertise, please don’t hesitate to reach out to us. Good luck and have fun with your Power BI data journey.


Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

3 Responses

  1. Hello!

    One easy question:

    In Power BI, Does it works in Direct Query mode?

    Thank you so much in advance

    1. Hi Félix,

      Short answer is – yes, it’ll work in Direct Query mode! You can set it up as shown in the snapshot.

      Also, it’s always a good idea to verify the connection of the read-only replica, by monitoring the compute usage of your database as mentioned in the blog.

      Best of luck!

  2. We’ve discovered an issue with our Business Critical setup where the Power BI Service, with the Failover Support option enabled, still connects to the Read-Write instance. Power BI Desktop, Excel and SSMS all connect appropriately to the ReadOnly. But any time the call originates from Power BI Service it’s connecting to ReadWrite. The Service connects via data gateway to our Business Critical Instance. Ever heard of this?

Leave a Reply

Your email address will not be published.


Connect with us

If you’d like to be kept in the loop on courses, events and other related topics, simply complete your details and we’ll add you to our list.

We use cookies to improve your experience and support our mission.
Read more about it here. By using our sites, you agree to our use of cookies.