Microsoft Power Automate -- Timeout Limitation of 120 Sec in SQL Actions -- Solution
Problem: If you have used Microsoft Power Automate for any of your process automation task and if it includes any SQL operations you might have came across a limitation within any sql action for timeout of 120 seconds. So that means any sql operation that is part of your process automation has to complete within 120 seconds. Ah isn't this strange? there might be some sql operation which would take more than 120 seconds like creating a backup of existing tables on request or any big DML task which would run for hours together. So in existing sql task available in Power Automate this can't be achieved. Below is Microsoft link for current limitation https://docs.microsoft.com/en-us/power-automate/limits-and-config
Solution: Well we have got many other actions triggers available in power automate to trigger or automate a process, I am going to use DevOps action to create a release and in Azure DevOps will keep the SQL scripts which needs longer execution time. This will simulate something like a asynchronous execution of long running sql in DevOps Azure SQL execution while the task that invokes it will complete soon the release is started. In next flow we can do a while loop logic to wait for the actual process to complete and then flag the process to be complete.
Lets deep dive into the solution
I was working to automate custom archive/backup request a user will request to have a snapshot of data that he needs for his analysis and the procedure which performs this actions where executing well over 20 mins to create the snapshot and at the same time user can request to delete the snapshot once his analysis is completed, even this process was taking 20 odd mins to complete. So both of this use case I was not able to use execute store procedure action from power automate as for a synchronous sql execution the timeout was limited to 120 seconds
Over power automate community and other tech channels some suggestions where there to optimize the proc or break the proc in small chunks so that it can complete in 120 sec, but what if the data becomes huge over the period and same issues re occurs with small chunks of data as well?
Other solution was to implement Fire and Forget solution using database triggers and a control table. Te idea was to insert a record in a control table which would have trigger set for insert DML and it would fire the proc on inserting a new record in the control table. Inserting a record in control table would not take 120 sec right :). Sounds simple right even I thought so, but when I did the implementation and executed the flow it was taking same time that the proc would run when you insert a record in a control table which had trigger to execute the proc. This doesn't create a asynchronous call rather it will create a single transaction for the insert of record followed by trigger which runs the proc. Bummer ended where I was stuck before.
The actual solution I found was based on idea which I read in 2nd solution(Fire and Forget).
I was exploring the available actions and triggers in power automate that would let me perform asynchronous execution on a Azure SQL Database(Note On premises sql server you can use sql job agent or message queue to create a asynchronous execution but in Azure SQL Database is a PAAS all such server side related operations are not available, for now Elastic Job(https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview) is in preview but I didn't explore that for this use case).
Then I found Azure Devops action which had several options to create a release, new build assign task .. etc. We already had a CICD pipelines in place and a release pipelines to perform database deployment. So I decided to leverage this and created a new release pipeline which would have the execution call for the stored procedure that i need to run and parameterize it to suit different use cases. So the final solution looked like this
We have Microsoft form which would take user inputs and then send it for a approval. Once the application owner approves his request same inputs are passed as arguments for creating a new release which would trigger the DevOps release pipelines accepts the user inputs and run the the store procs in devops Azure SQL execution task. Once a new release is triggered by Create Release action it would complete and exit but we need the procedure execution also to complete to mark the complete process as successful, So added a Do Until control action that would keep checking the procedure to complete by some sql task checks and only when the procedure execution is complete it would send the user notification that snapshot is ready for usage. This way we are creating a asynchronous execution procedure still flagging the process to complete only when the actual proc is finished.
Do Until part
Azure DevOps Snippet
Use inline scripts in deployment types
This is a workaround solution until 120 seconds timeout limitation at Azure Power Automate SQL action is upgraded or any other alternative service is provided by Azure
Solution: Well we have got many other actions triggers available in power automate to trigger or automate a process, I am going to use DevOps action to create a release and in Azure DevOps will keep the SQL scripts which needs longer execution time. This will simulate something like a asynchronous execution of long running sql in DevOps Azure SQL execution while the task that invokes it will complete soon the release is started. In next flow we can do a while loop logic to wait for the actual process to complete and then flag the process to be complete.
Lets deep dive into the solution
I was working to automate custom archive/backup request a user will request to have a snapshot of data that he needs for his analysis and the procedure which performs this actions where executing well over 20 mins to create the snapshot and at the same time user can request to delete the snapshot once his analysis is completed, even this process was taking 20 odd mins to complete. So both of this use case I was not able to use execute store procedure action from power automate as for a synchronous sql execution the timeout was limited to 120 seconds
Over power automate community and other tech channels some suggestions where there to optimize the proc or break the proc in small chunks so that it can complete in 120 sec, but what if the data becomes huge over the period and same issues re occurs with small chunks of data as well?
Other solution was to implement Fire and Forget solution using database triggers and a control table. Te idea was to insert a record in a control table which would have trigger set for insert DML and it would fire the proc on inserting a new record in the control table. Inserting a record in control table would not take 120 sec right :). Sounds simple right even I thought so, but when I did the implementation and executed the flow it was taking same time that the proc would run when you insert a record in a control table which had trigger to execute the proc. This doesn't create a asynchronous call rather it will create a single transaction for the insert of record followed by trigger which runs the proc. Bummer ended where I was stuck before.
The actual solution I found was based on idea which I read in 2nd solution(Fire and Forget).
I was exploring the available actions and triggers in power automate that would let me perform asynchronous execution on a Azure SQL Database(Note On premises sql server you can use sql job agent or message queue to create a asynchronous execution but in Azure SQL Database is a PAAS all such server side related operations are not available, for now Elastic Job(https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-jobs-overview) is in preview but I didn't explore that for this use case).
Then I found Azure Devops action which had several options to create a release, new build assign task .. etc. We already had a CICD pipelines in place and a release pipelines to perform database deployment. So I decided to leverage this and created a new release pipeline which would have the execution call for the stored procedure that i need to run and parameterize it to suit different use cases. So the final solution looked like this
We have Microsoft form which would take user inputs and then send it for a approval. Once the application owner approves his request same inputs are passed as arguments for creating a new release which would trigger the DevOps release pipelines accepts the user inputs and run the the store procs in devops Azure SQL execution task. Once a new release is triggered by Create Release action it would complete and exit but we need the procedure execution also to complete to mark the complete process as successful, So added a Do Until control action that would keep checking the procedure to complete by some sql task checks and only when the procedure execution is complete it would send the user notification that snapshot is ready for usage. This way we are creating a asynchronous execution procedure still flagging the process to complete only when the actual proc is finished.
Do Until part
Azure DevOps Snippet
Use inline scripts in deployment types
This is a workaround solution until 120 seconds timeout limitation at Azure Power Automate SQL action is upgraded or any other alternative service is provided by Azure
Comments
Post a Comment