Sign in Subscribe
Azure

Monitoring Azure SQL Read Replicas: A Practical Guide

Azure SQL Hyperscale read replicas are useful for offloading read-heavy workloads from the primary database. In this guide, we’ll cover how to monitor your read replica using Azure Data Studio (ADS). The process shown here works with any SQL client, such as SQL Server Management Studio (SSMS). Some clients have an option to set Application Intent directly, while others may require adding ApplicationIntent=ReadOnly as an additional connection string parameter.

Step 1: Connecting to the Read Replica

To start, open ADS and connect to your Azure SQL Hyperscale database.

During the connection setup, click on Advanced to configure the Application Intent to Read Only. This ensures the connection specifically targets the read replica.

Tip: Even with Application Intent set to Read Only, you may still end up connecting to the primary database. To verify your connection, you will need to run a query to check the replica role.

Step 2: Verifying the Connection

Once connected, run the following query to see the most important information about your replica:

SELECT * FROM sys.dm_db_resource_stats;

This query provides key metrics related to your replica's performance:

  • end_time: Timestamp of when the statistics were recorded.
  • replica_role: Indicates the role of the replica. A value of 1 means you are connected to the read replica, while 0 indicates a connection to the primary database.
  • avg_cpu_percent: Average CPU utilization of the replica over the recorded interval.
  • avg_data_io_percent: Average data I/O utilization.
  • avg_log_write_percent: Average log write utilization.
  • max_worker_percent: Maximum worker utilization as a percentage.
  • max_session_percent: Maximum session utilization as a percentage.
  • dtu_limit: The maximum DTU (Database Transaction Unit) limit.
  • avg_instance_cpu_percent: Average CPU usage of the entire instance.

Step 3: Checking the Replica Role

Look at the replica_role column in the result set. A value of 1 indicates you are connected to the read replica. If the value is 0, you are connected to the primary database. Sometimes, despite specifying Read Only, the connection might still point to the primary.

Troubleshooting Tips

  • Make sure that the read replica is properly configured and accessible.
  • Ensure that your connection settings include Application Intent: Read Only.
  • For clients that do not have a direct Application Intent option, add ApplicationIntent=ReadOnly in the connection string.

Conclusion

Monitoring Azure SQL Hyperscale read replicas can help ensure that your read-heavy queries are efficiently offloaded. Azure Data Studio makes it straightforward to connect and verify your replica status. Stay tuned for more tips on optimizing your Azure SQL workloads.