Ensuring Singleton Behavior for Azure SQL Trigger in a Multi-Region Azure Functions Setup

[amaraslam07]

I am currently implementing an Azure SQL Trigger for Azure Functions in a multi-region deployment. My requirement is to ensure singleton behavior across all regions, such that only one instance of the Azure Function processes the SQL change events globally (even if the function app is deployed in multiple regions).

Here are my specific concerns:

1. Event Coordination Across Regions: How can I ensure that only one Azure Function instance, across all regions, processes the triggered event?
2. Recommended Approach: Does Azure SQL Trigger natively support any mechanism for cross-region singleton behavior, or do I need to implement a distributed locking mechanism (e.g., using Azure Blob Storage leases, Redis, or other solutions)?
3. High Availability with Singleton: What would be the best practice for maintaining high availability while enforcing singleton processing in a multi-region Azure Functions setup?
4. Scalability Considerations: Are there any specific considerations or limitations I should be aware of when combining Azure SQL Trigger and singleton processing for high-throughput applications?

Any guidance, documentation, or best practices on this would be greatly appreciated.

Thank you in advance for your help!

[amaraslam07]

I reviewed the leases table concepts and tested it locally.

It appears that scalability functions well in a single region when auto-scaling is enabled for the Function App. The reason for this is that the UserFunctionId in az_func.GlobalState table is generated based on the read-only WEBSITE_SITE_NAME app setting of the Function App. Since this app setting is read-only, we cannot update the site name in the secondary region.

I confirmed this behavior by running functions locally with the same WEBSITE_SITE_NAME property for both processes. It only triggered one process at a time.

Is there any workaround for a multi-region active-active setup? This setup would be a requirement for many organizations ?