This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
During these last days we received multiple questions abouts how to monitor the performance and the queries that were executed in the secondaries replicas using in the connection string ApplicationIntent=READONLY with Read Scale Out feature.
In this article we explained time ago that is a Read Scale Out for Premium databases and in this article we would like to show you some alternatives to monitor the queries and performance of your database.
As you know Query Data Store, Extended Events, SQL Profiler and Audit features are not supported on the read-only replicas, so the way to monitor the replicas will be to use the available DMVs, such as sys.dm_db_resource_stats. Using the DMVs seems to be the best option for these cases, further information can be found in the link below: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-read-scale-out#monitoring-and-troubleshooting-read-only-replica
In another hand, using this DMV: select * from sys.dm_db_resource_stats you could review the resources utilization. Also, if you need more information, in this URL: https://github.com/JMNetwalker/MonGeoAzure you could have more ways to collect the data.
A lesson learned working with ApplicationIntent=ReadOnly, I found:
- When you have configured in your connectionstring ApplicationIntent=ReadOnly the load balancing will enroute the connection to any of the two replicas that you have.
- There is not possible to specify 1 or 2 at this time.
- The primary replica is also eligible even when you have ApplicationIntent=ReadOnly in your connection string.
Enjoy!!