Azure SQL Analytics - Whats supported in regards to geo replicated databases? RRS feed

  • Question

  • Would anybody be able to point me in the direction of any documentation around Azure SQL Analytics and whats supported in regards to geo replicated databases? I'm guessing things such as query performance and advice doesn't work due to the query store being read-only on the secondary.

    I'm wanting to monitor query performance and degradation on our secondary database as we are looking at using it for some reporting workloads. 

    With the query store being read-only and basically a copy of the primary in a geo replicated database what tools do people use in regards to monitoring query performance and degradation?



    Monday, October 28, 2019 4:25 PM

All replies

  • Hi Lee,

    The following documentation is a great entry point to learn about the tools and resources available for monitoring a database instance and taking action in the case where performance degradation might be experienced: Monitoring and performance tuning.

    This above document covers both how to monitor as well as diagnose and take corrective action if performance becomes an issue. The following document is specific to Geo-Replication: Creating and using active geo-replication, should you have specific questions about geo-replicating a single database.

    If you have multiple databases deployed to an Azure SQL Server instance and would like to establish fail-over of more than one database, please see: Use auto-failover groups to enable transparent and coordinated fail-over of multiple databases (link).

    Please let us know if you have additional questions.



    Tuesday, October 29, 2019 11:49 PM
  • Hi Mike,

    Thanks for the response.

    I'm comfortable configuring geo replication.  I'm also comfortable monitoring replication from a lag etc .. point of view. 

    I'm comfortable monitoring primary database query performance, its when it comes to readable secondaries that I'm asking the questions about.  As far as I can tell the links you sent don't answer these questions.

    We have a database that is geo replicated.  We are intending on running reporting workloads against the secondary readable database.  It is this database in particular I am wanting to monitor.  Monitoring the primary is no issue.

    In particular in regards to SQL Analytics I cant find anything in the docs to state what is supported on a readable secondary and how Microsoft recommends this is configured.  SQL Analytics uses the query store which is in a read only state on the secondary so does not collect information regards the readable secondary queries.  I am guessing SQL Analytics and the Query Store are not supported on readable secondary databases?

    With the above in mind do Microsoft provide any tools or have any recommendation for monitoring query performance against readable secondaries?

    Currently to monitor the secondary I am looking at Brent Ozar's sp_blitzcache which uses the plan cache so doesn't reply on the unavailable query store like the Microsoft Azure tools seem to.

    I have also just come across this link, which I'm starting to take a look at. We don't use a managed service but from a very quick look I think it may work against our SQL Database in an elastic pool.




    Wednesday, October 30, 2019 2:37 PM
  • Thank you for this additional detail. The best option I can suggest is that for a specific document where you feel there could be better (or missing) information, at the bottom of each is the ability to provide feedback. Click on Give documentation feedback.

    Monday, November 11, 2019 5:46 PM
  • Hi Lee,

    Did you use the native monitors that exist on the portal itself? Such as metrics and query insights? You can create your own dashboards and see what are the queries that are consuming more resources.

    hope this helps!


    Tuesday, November 12, 2019 12:35 PM