SQL Insight on SQL Server Azure VM (SQL IaaS)

Question

Hello Everyone,

I hope you're doing fine,

I just discovered the service SQL insight to monitor SQL IaaS VMs, SQL databases, and azure managed Instances, and i want to give it a try to monitor the sql layer of my azure iaas SQL VM.

So, i started by creating a log analytics, sql login on the sql vm (to be used by sql insight),a monitoring machine, and a profile.

Once i created the profile, i added the monitoring machine to that profile, and i used the following connection string (Just for a test on my dev vm) :

{
  "sqlVmConnections": [
    "Server=IPofTheIAASSQLVM;Port=1433;User Id=user3;Password=P@ssword;"
  ]
}

I don't use for now a key vault to store the sql creds this is why i configured the user/password directly on the connection string even if it's not recommanded, just for testing. and i configured only one connection string (for my test vm) to monitor it.

I'm receiving the following errors

Monitoring machine state : Not collecting
Logs :

Failed to generate per VM TOML config with ID: SqlInsights. Profile: TestProfile. Error: error during executing TOML. Error: could not execute TOML template. Error: template: template:2:38: executing "template" at <.Parameters.sqlAzureConnections>: map has no entry for key "sqlAzureConnections"

I want to know what i'm missing. i'm pretty sure something is wrong with the connection string that i made, maybe i'm missing some stuff...unfortunatly couldn't find some interesting articles on the internet.

Secondly, it says that it didn't find a key for sqlAzureConnections, this entry is for Azure SQL databases, in my case i don't i want to monitor them for now, this is why i didn't integrate them on the query.

Any help please?

Answer 1

@Mohamed jihad bayali thank you for showing interest in SQL Insights.

I understand that you are trying to enable SQL Insights for an Azure VM hosting SQL Server. Based on the error received, it seems that the Connection strings field as below only contains the sqlVMConnections in your case (as you are only configuring it for monitoring SQL Server installed on Azure VM):

Please add the parameters with all the 3 type of connection strings. You can add value only in sqlVMConnections field and leave the other 2 empty. For example, in the image above, sqlVMConnections and sqlManagedInstanceConnections are empty but are still included.

Also, ensure that all the supported pre-requisites are met for enabling SQL VM Insights.

Please let me know if you have any questions. Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.