Azure-docs: Monitor utilization of the secondary

[towerbe]

This capability is something that's been needed for a long time, and is an implementation of the CQRS concept. One thing I don't see addressed here is how, if it is even possible, we can monitor the utilization of the secondary. Since the DTU graphs and monitors only provide insight to the primary, if we direct all of our read-only work loads to a secondary potentially we could run into response and performance issues with no visibility to them. Does this capability exist?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 7a2961d1-4448-11b7-2dc2-3b738bd70939
• Version Independent ID: 206eb022-25c4-66d7-7285-0d53a84956e3
• Content: Azure SQL Database - read queries on replicas
• Content Source: articles/sql-database/sql-database-read-scale-out.md
• Service: sql-database
• GitHub Login: @anosov1960
• Microsoft Alias: sashan

[Mike-Ubezzi-MSFT]

@towerbe Thanks for the feedback. We are actively investigating and will get back to you soon.

[JeremyWeir]

Are there any additional billing costs if this is enabled?

Is this "read only replica" any different than having a geo-replicated readable secondary in the same region?

[SqlDavid]

I am no authority on the matter, but I dont believe there is any extra cost associated with this. So that is the first difference. The other difference is that this replica is located in the same data center as your primary and should be transactionally consistent with the primary. So you skip the lag of hitting a different data center as well as the potential data consistency from the asynchronous process of geo replication. It also will be guaranteed to be the same performance level as the primary, where as a geo replicated database might be configured at a lower performance level to save cost.

[Mike-Ubezzi-MSFT]

@JeremyWeir Does this answer your question? Seems there is potentially some documentation enhancement that could be made. Thanks, Mike

[Mike-Ubezzi-MSFT]

@anosov1960 There is some additional information that could be added to this document.

[JeremyWeir]

@Mike-Ubezzi-MSFT it answers it if if the guy who said they're "no authority on the matter" is correct :)

[Mike-Ubezzi-MSFT]

We will now proceed to close this thread. If there are further questions regarding this matter, please reopen it and we will gladly continue the discussion.

[SqlDavid]

But the original question about being able to monitor the DTU on these read only replicas was not answered. I am still curious to know the answer to that question.

[Mike-Ubezzi-MSFT]

@SqlDavid Thanks for clarifying this. I have assigned to the content owner (@anosov1960) as a doc enhancement (please correct me if I am wrong). From a product enhancement perspective, the Azure SQL Database User Voice may be the best path for making the product enhancement, and this GitHub issue serving as making any documentation enhancement as to how to monitor the secondary or pointing out that there is currently a gap, etc.
Thanks,
Mike

[SqlDavid]

@Mike-Ubezzi-MSFT Got it. I did not realize that. Thanks for the clarification and I will definitely go over to the User Voice site!

[clement911]

Also wondering the same question about monitoring replicas

[JeremyWeir]

Haven't been able to find anything in Azure portal to help with this, but stats/metrics can come directly from queries as is pointed out on SO

https://stackoverflow.com/questions/51062304/sql-azure-read-scale-out-feature-monitor-queries-on-read-only-replica/51068643#51068643

You just need to connect using ApplicationIntent=ReadOnly in the connection string (or additional connection parameters in SSMS).

---

For others reading this thread curious about the billing, it is currently free to enable. Hopefully it stays that way once it is no longer a "preview" feature.

https://azure.microsoft.com/en-us/updates/azure-sql-database-announces-preview-of-read-scale-out-support-in-premium-service/

effectively doubles the compute capacity of the database or elastic pool at no additional charge

[JeremyWeir]

@SqlDavid Thanks for clarifying this. I have assigned to the content owner (@anosov1960) as a doc enhancement (please correct me if I am wrong). From a product enhancement perspective, the Azure SQL Database User Voice may be the best path for making the product enhancement, and this GitHub issue serving as making any documentation enhancement as to how to monitor the secondary or pointing out that there is currently a gap, etc.
Thanks,
Mike

This is the best UserVoice Idea I could find that matches what this issue is requesting.

https://feedback.azure.com/forums/217321-sql-database/suggestions/34337935-monitor-queries-on-secondary-database-when-read-sc

Everyone go vote 3x!