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

Question

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!

Answer 1

Here are a couple of suggestions:

Azure Function with Hybrid Connection or VPN Gateway

• Serverless: Only runs when triggered, reducing costs.
• Scalable: Can handle spikes in activity easily.
• Direct Integration with Azure REST APIs: You can directly call the VMSS API to scale the instances.

Architecture Overview

1. Trigger: Run the Azure Function periodically (using a Timer Trigger, e.g., every 5 minutes).
2. Query Execution: The Function queries the on-prem SQL database using one of these:
   • Hybrid Connection: If a simple TCP connection is sufficient.
   • VPN Gateway/ExpressRoute: If you need secure connectivity and have already established a site-to-site VPN.
3. Decision Logic: Based on the query result (if the count matches X), the Function will call the Azure REST API to update the instance count of the VMSS.
4. Scale Up/Down: The VMSS adjusts the number of instances accordingly.

Key Benefits

• Lightweight and Cost-Effective: Azure Functions are billed per execution.
• Secure: Use Managed Identity to call Azure APIs securely, avoiding hardcoding credentials.
• Fast and Responsive: Timer trigger ensures periodic execution, but you can adjust it based on your needs.

Drawbacks

• Requires Hybrid Connection or VPN Gateway to connect to on-prem SQL.

Alternative Approach: Azure Automation Account with Runbook

• Easier to set up if you are comfortable with PowerShell.
• Supports Hybrid Worker: You can run the SQL query directly from on-prem using a Hybrid Worker.

Steps

1. Hybrid Worker Setup: Install a Hybrid Worker on an on-prem server.
2. PowerShell Runbook: Create a PowerShell runbook that runs on the Hybrid Worker, queries the SQL database, and adjusts the VMSS.

Key Benefits

• Hybrid Worker makes it easy to access on-prem resources without complex networking.
• Native PowerShell integration simplifies automation if your team is already using PowerShell.

Drawbacks

• Automation Accounts are less scalable than Azure Functions.
• You may need to maintain the Hybrid Worker infrastructure.

Other Options to Consider

1. Power Automate: Can be used if you want a low-code/no-code approach, but it may not be suitable for advanced logic.
2. Logic Apps: Similar to Power Automate but more suitable for enterprise-level integration and monitoring.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin