Monitoring consumption on Read replica of Azure SQL databases

Question

We have moved most of the Application hits to geo replica,
need to find out a way to monitor the geo replica of Azure SQL database, like query running and high cpu etc

As we will have to monitor the consumption because most of the reporting is through the geo replica.

Please help if any heads up.

Answer 1

Hi @Aman Garg ,

Welcome to Microsoft Q&A platform and thanks for using Azure services.

As I understand from the question, you want to know if we can monitor read replica of Azure SQL Database.

We wanted to inform you that we could not get the query details from our backend as Query Store is not enabled for read replica.

We would like to suggest you to reduce the read queries on primary database as primary database sync with secondary database and if primary database has more load, then it will reflect to secondary also.

We request you to run the below queries to get the real time CPU consumption:

SELECT * FROM sys.dm_db_resource_stats ORDER BY end_time DESC;  
  
The average and maximum values for CPU percent, data and log I/O, and memory consumption over the last hour.  
  
SELECT     
  
    AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent',    
  
    MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent',     
  
    AVG(avg_data_io_percent) AS 'Average Data IO In Percent',    
  
    MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent',    
  
    AVG(avg_log_write_percent) AS 'Average Log Write I/O Throughput Utilization In Percent',    
  
    MAX(avg_log_write_percent) AS 'Maximum Log Write I/O Throughput Utilization In Percent',    
  
    AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent',    
  
    MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent'    
  
FROM sys.dm_db_resource_stats;  

We request you to go through the below document

Monitoring and troubleshooting read-only replicas

Azure SQL Database auditing tracks database events and writes them to an audit log in your Azure storage account, or sends them to Event Hub or Log Analytics for downstream processing and analysis.

If you enable Azure SQL Database Auditing on the portal then you will track all the events that are happening on the database, that is on primary replica and on secondary read only replica but not specifically read only replica . It is not possible to get the audit logs of read only replica.

We hope this information helps you.

Please let us know if you have any further queries and concerns. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

Answer 2

Please consider using this PowerShell script to monitor the performance of the replica. The script returns a JSON file that you can query as shown below:

SELECT book.* FROM OPENROWSET (BULK '\servername.database.windows.net_database_YYYYMMDDHHNNSS_ResourceDB.json',  SINGLE_cLOB) as j CROSS APPLY OPENJSON(BulkColumn) WITH( [end_time] datetime , [avg_cpu_percent] [decimal](5, 2), [avg_data_io_percent] [decimal](5, 2) , [avg_log_write_percent] [decimal](5, 2) , [avg_memory_usage_percent] [decimal](5, 2), [xtp_storage_percent] [decimal](5, 2), [max_worker_percent] [decimal](5, 2) , [max_session_percent] [decimal](5, 2) , [dtu_limit] [int] ) AS book  

Every time that you execute this PowerShell command you will have a JSON file in the following format for every server, database, query for user database (or query master database) and YYYYMMDDHHNNSS. You can further improve the script and customize it for your specific needs.