Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Ability run AD HOC query from HVR UI

Answered
Jeff Shaw User

When adding a new channel action with connectionstring variable ApplicationIntent=ReadOnly it would be great to be able to run a quick query to test HVR user intent.

Action: Environment

Name: HVR_ODBC_CONNECT_STRING_ADD
Value: ApplicationIntent=ReadOnly


run query from location to validate.
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Updateability;

otherwise it is almost impossible to validate that the HVR connection is hitting an Azure SQL Managed Instance read-replica from my Linux based HUB servers.

this would make it click/click/done.

Please sign in to leave a comment.

Comments

1 comment

Date Votes
  • Edwin Fivetranner

    Hi Jeff,

    Thanks for the request. You can currently perform a similar validation from the Linux host where the HVR database connection originates—either the Hub or the Agent.

    First, validate the configured HVR location:

     
    hvrtestlocation -l <location> <hub> <channel>
     

    Then use Microsoft sqlcmd with the same server, database, and HVR user:

     
    sqlcmd -S "tcp:<managed-instance-listener>,1433" \
  -d "<database>" \
  -U "<hvr_user>" \
  -K ReadOnly \
  -Q "SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS Updateability;"
     

    The -K ReadOnly option is equivalent to ApplicationIntent=ReadOnly. The query should return READ_ONLY when Azure SQL Managed Instance has routed the connection to a readable secondary.

    However, this is only a workaround. hvrtestlocation validates connectivity but cannot currently execute an arbitrary diagnostic query. Also, running sqlcmd separately does not conclusively prove that the HVR process applied the channel’s HVR_ODBC_CONNECT_STRING_ADD setting. Therefore, the requested UI capability would still be useful: executing a read-only query through the actual HVR location would provide a simple and reliable end-to-end validation. We have put this as a requested item in our backlog but we don't have a clear planning yet.

    We'll let you know if its status changes.

    Best regards,

    Edwin