Seeking Advice for Automating VMSS Scaling Based on On-Premises SQL Data

Hi everyone,

I have a few VMSS (Virtual Machine Scale Sets) already configured to scale up and down based on predefined rules. Now, what I’m looking to achieve is to periodically run a query on my on-premises SQL database, and depending on the result (let’s say if the number is X), I want to automatically scale the VMSS accordingly (increase or decrease the number of instances).

I’ve considered several approaches like using Azure Automation Account, Power Automate, or Azure Function for this. But I’m not sure what would be the most efficient way to implement it.

Any ideas or suggestions on the best method to achieve this would be greatly appreciated!

Thanks in advance for the help!