On April 26th, Paessler Supported editted the KB article with the announcement that the ADO SQL v2 is in canary release at the moment and will be stable soon.
We use the ADO SQL sensor mostly to get data out of the TopDesk database so we can show customers (realtime) if the SLA is met or not. The great thing about the ADO SQL sensor is that I can clone the sensor, change the name of the customer in the query and start the sensor. In this way, I can create a new dashboard for new customers in a few minutes by just cloning the sensors.
In our environment, we use over 400 ADO SQL sensors that we need to migrate to new sensors before upgrading PRTG to the latest version. I waited to come up with a plan to see what the new ADO SQL v2 sensor looks like. Today I upgraded my PRTG test environment to the latest canary release (16.2.24.3242) that has the new ADO SQL v2 sensor.
As soon as I added the sensor to PRTG, I discovered that it looks very similar to the Microsoft SQL v2. You need to create a SQL Query file and the ADO SQL v2 sensor will get the query from the file.
My solution?
As you know, I'm a big fan of the PRTG API and the prtgshell PowerShell module. I decided to use this to query PRTG to determine the impact of the deprecation of this sensor. (I'm not going into detail how to use this, see here for a description)
First, I needed to know how much databases are queried from PRTG in order to create a plan of approach. I decided to use the ADO SQL connection string as unique value to see how much databases are queried through this sensor and came up with a quick script for this.
# Get all the ADO SQL sensors
$adosqlsensors = Get-PrtgDeviceSensorsByTag -FilterTags "adosqlsensor"
# Create an outer array
$outarr = @()
# Get the information from the ADO SQL sensor
foreach ($sensor in $adosqlsensors) {
# Get all the connectionstrings
$connstring = Get-PrtgObjectProperty -ObjectId $sensor.objid -Property connectionstring
$sqlquery = Get-PrtgObjectProperty -ObjectId $sensor.objid -Property sql
$arrconnstring = New-Object System.Object
$arrconnstring | Add-Member -type NoteProperty -Name Probe -Value $sensor.probe
$arrconnstring | Add-Member -type NoteProperty -Name device -Value $sensor.device
$arrconnstring | Add-Member -type NoteProperty -Name ConnString -Value $connstring
$arrconnstring | Add-Member -type NoteProperty -Name sqlquery -Value $sqlquery
# Write all info in the outer arr
$outarr += $arrconnstring
}
# Count all unique connection strings
$count = $outarr | Sort-Object ConnString -Unique Now I can find out how much unique connection strings I have:
And an example of the output.
The most important part is that I now have all the connection strings with the corresponding devices and probes in PRTG so that I have an estimation how much databases we are talking about now. (12 unique ones in my environment). Depending on the connection string, I can determine which database is queried and how many queries / sensors are used and from which probes / devices in PRTG.
Of course you can gather so much more information from this, but I'm not going into detail with this for now.
And now what?
From here I know what databases are queried from PRTG from the connection string. I decided not to use the new ADO SQL v2 sensor, but I'm going to build a PowerShell script with queries the database and I'm going to use the "advanced exe/script" sensor for this. The reason is that I can use parameters in PRTG for my different customers to keep everything simple. Of course I will make a blogpost when this is ready.
However, if you have a lot of ADO SQL sensors that use complete different database and queries, I still recommend to use the new ADO SQL v2 sensor in PRTG together with the SQL files. If you have any questions about this, please let me know in the comments below.