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
  • Background

    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:

    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?

          • API
          • 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

    API

    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

    Step-1:

    Choose the server name

     

    Step-2:

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

     

    Step-3:

    Click the “Connect” button

     

    Validating connection to read-only database:

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

    Execute:

    SELECT DATABASEPROPERTYEX(DB_NAME(), ‘Updateability’)

    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.

    Step-1:

    Choose the server name and database name.

    Step-2:

    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.

    Step-3:

    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:

    SELECT DATABASEPROPERTYEX(DB_NAME(), ‘Updateability’) AS Access;

    SELECT

           req.session_id,

           req.status,

           req.command,

           req.cpu_time,

           req.total_elapsed_time,

           s.host_name,

           s.program_name,

           s.login_name

    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.

Join the conversation

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Comments

Post has no comments.