How to run sql profiler on read only node of SQL Manage instance in Azure?

Question

We are using readonly node of SQL MI azure using applicationIntent=Readonly property in connection string. When we enable sql profiler on readonly node then it connect only read-write node only . Can anyone suggest how can we enable profiler or extended event on readonly node?

score 0 · Accepted Answer · answered Nov 04 '20 at 14:02

0

Unfortunately you will not be able to turn on profiler/extended events because you do not have direct access to the node itself. Business Critical Tier run Always on on the backend which is not visible to the user. Because of that you will not be able to do anything with secondary nodes.

What is the reasoning behind running XE or profiler on secondary nodes.

answered Nov 04 '20 at 14:02

Rizwan

318
1
5

Thank you Rizwan for your answer. Our application architecture is point to secondary node for read only data. And we need to measure long running queries or procedures on secondary node. We have any alternative solution to measure long running queries on read only node. – NP007 Nov 05 '20 at 05:42
At this moment, I am not aware of any solutions within Microsoft Product Group to enable monitoring. I will update if I see any blog updates on this. – Rizwan Nov 05 '20 at 16:19
1

Here is a good place to start https://learn.microsoft.com/en-us/azure/azure-sql/database/read-scale-out#monitoring-and-troubleshooting-read-only-replicas These are some DMVs that can help you – Rizwan Nov 05 '20 at 17:17

score 0 · Answer 2 · edited May 15 '21 at 10:01

You would want to run profiler on a read only sql server secondary node because you can do read only queries on the database on the secondary and you want to monitor performance.

You can connect SSMS to a read only node by using the button / additional connection parameters when you connect and putting ApplicationIntent=ReadOnly You can try to create a test table. It will fail saying the database is read only. One way to know SSMS really is connected to a read only replica.

Then you can use regular DMV's to see performance stats. The DMV data is stored in RAM, not the database.

So far the connection option does not seem to work to connect Profiler to the read only server node. I may be making a mistake, missing something or it doesn't work with profiler.

How to run sql profiler on read only node of SQL Manage instance in Azure?

2 Answers2