Monitoring Azure SQL Managed Instances

Hi,

I'm attempting to use a SQL Server User Experience monitor against an Azure SQL Managed Instance. I've added the managed instance as an external node, with a dynamic IP but it seems when i create the template, it always tries to run the query against the Ip not the dns name, which i believe is why it then fails?

e.g i get the following error

Sql Server returned an error. Cannot open server "192.168.x.x" requested by the login. The login failed.

Has anyone had any success with this. Is there a better way to run SQL queries using SAM, against a managed instance

Find more posts tagged with

Azure

sql monitoring

sam monitoring

Accepted answers

All comments

mark.gover

Assuming you have gone through this KB? https://documentation.solarwinds.com/en/success_center/sam/content/sam-microsoft-azure-sql-server-database.htm - Personally I haven't used this so unsure how it's setup.

Solarwinds also has a performance monitoring tool designed for Azure SQL https://www.solarwinds.com/sql-sentry/use-cases/azure-sql-monitoring

narzsa

Cheers Mark. The link is for a SAM template for stats, but it won't resolve the SQL query issue, since that appears to be a problem using the node in SAM

We actually have dpa, which brings those stats into SAM, but the SQL query template still has to be latch to a SAM node, not a dpa one

mark.gover

So I am having a similar issue with a cluster in AWS, the cluster is in different Availability Zones so it does not use a VIP but a DNS name. Solarwinds is expecting a response from a Static VIP which is not the case, the two servers have different IP ranges since they are in different zones. So one node always appears to be down and alerts as the cluster being down unless you change the IP. If only Solarwinds had the ability to ping a DNS instead of IP then if successful the cluster doesn't show as down. I have tried to check the "dynamic IP" option but to no avail.

narzsa

Yeah that appears to be the issue as i found this - learn.microsoft.com/.../connectivity-architecture-overview

'When connecting to a private endpoint, always use the domain name since connecting to Azure SQL Managed Instance via its IP address isn't supported yet.'

This implies, and i can confirm from testing, that a SQL Managed Instance will not accept a connection via ip. It must be done via a dns call, which conflicts with how solarwinds seems to work as you say

adam.beedell

I've got a powershell monitor for this, it doesnt work with the OOTB sql monitor as far as I'm aware

adam.beedell

### Module-less SQL (Azure SQL MI) template

[Net.ServicePointManager]::SecurityProtocol = [Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12

Function SecureStringToString($Value)
{
    [System.IntPtr] $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($Value);
    try
    {
        [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($BSTR);
    }
    finally
    {
        [System.Runtime.InteropServices.Marshal]::FreeBSTR($BSTR);
    }
}

$cred = Get-Credential -credential ${CREDENTIAL}

[string]$username = $cred.Username
[string]$password = SecureStringToString $cred.Password

$mi = "${Node.Caption}"


$ConnectionString = "Server=tcp:$mi,1433;User ID=$Username;Password=$Password;Connection Timeout=30;"


#### add a query here
$query = "


"

$connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)


    $connection.Open()



    $Command = $connection.CreateCommand()
    $Command.CommandText = $Query


    $Result = $Command.ExecuteReader()

    if ($Result.Read())
    {
        $Statistic = $Result.GetValue(0).ToString()
        $message = $Result.GetValue(1).ToString()
        $Result.Close()
        $connection.Close()
    }### This is reading data positionally as defined in the query
    ELSE
    {
        $Result.Close()
        $connection.Close()
        Write-Host "Statistic: 0"
        Write-Host "Message: Error during query"
        Exit 1
    }


  Write-host "Statistic: $Statistic"
  write-host "Message: $message"
  Exit 0

@narzsa @mark.gover

adam.beedell

My understanding of the issue connecting to azure SQL MI is independent of any IP-based issues, so maybe those exist too, but if there's no IP issues you'd still have problems

You could always do something like set a monitor against both IPs, group em, alert on if both down, for example.

Could build an LB into the PS
I'd hope the dynamic IP thing took care of it, but i've not had that problem

adam.beedell

If line 35 fails there's a problem I can only describe as "ODBC-like" in which a earlier version of the SQL processor is default and not supported, I wrote this with the intention of making that sort of problem as unlikely as possible

narzsa

Cheers Adam, i'll give it a test as i think powershell may work. The issue isn't the ip per say, its the fact MS don't support calls to SQL through the IP and thus block it. So so long as the Powershell calls it via the dns name it should work

DPA doesn't have the problem and calls it correctly so we can get monitoring from that side at least

mapujece

Download videosIt seems like the SQL Server User Experience Monitor is defaulting to the IP instead of the DNS name for your Azure SQL Managed Instance. To fix this, make sure to use the fully qualified domain name (FQDN) of your instance when adding it as an external node in SAM. If it continues to use the IP address, try creating a new template that explicitly specifies the DNS name. Additionally, ensure that your login credentials have the necessary permissions for the managed instance. If you still encounter issues, check SolarWinds documentation or community forums for additional guidance on configuring SAM with Azure SQL Managed Instances. Also, if you're looking to download videos or additional resources for troubleshooting, consider exploring the Snaptube.

narzsa

Hi Mapujece, it is set to DNS, and you can see in SW it is resolving to an IP. However, MS do not support connections over IP, the connection needs to be via FQDN, hence the issue.

There doesn't appear to be an option for a SQL Server User Experience Monitor to override this, since there is no ip field in it, only port, and SQL server instance

The only thing that may work would be to use powershell