This is a tale about a SFTP Azure API Connection that was difficult to support and maintain. Additionally, I will tell you some tricks to tame a monster like this.
The solution
This solution uses one SFTP API connection. However, I am not going to walk you through this because that is not the story I want to tell. Instead, please focus on steps 1 and step 12. Step 1 is SFTP polling trigger that picks up the order. Step 12 sends an invoice via SFTP using the same API connection as the first step. This is unremarkable and we have many SFTP and FTP order and invoice flows like this. But this one API connection was extremely problematic.
The problems
Firstly, standard settings for SFTP polling trigger work well if there is one file to pick up. On the other hand, if there are many files then a trigger failure occurs after a while and some files are left on the SFTP server. The trigger failure is an authentication error. Subsequently another poll after ten minutes picks up more files, but the same trigger failure occurs with some files left on the server. On the other hand, another poll less than ten minutes after the first failure always fails with the same trigger failure. I worked this all out by trial and error. But this was not satisfactory because although we could pick up the files, we still saw a lot of trigger failures. Furthermore, we want to set up an alert on trigger failures to detect when the SFTP server is not online. This is extremely important to make sure our customer could always send us orders. We could not do this with this above behaviour in place.
At this point I spoke to the owner of the SFTP server from Hell who volunteered that they restrict the number of concurrent connections to 5. Furthermore, if we generate to many connection errors then their server blacklists our connection. Now that is not really friendly. Thus, the next step was to throttle the number of connections as shown below.
Finally with concurrency control in place the trigger no longer fails when picking up many files. Furthermore, setting up an alert on trigger failure no longer gives false positives. For our support team there is nothing better than ringing the customer to tell them that their SFTP server is down. But this is not the end of the story.
What about sending on the same SFTP API Connection?
As was previously stated, we also send an invoice via SFTP using the same API connection. How do we stop this separate asynchronous process exceeding the maximum concurrent connections? I could not find a way of throttling both order trigger and the outbound invoice connections together.
First approaching the owner of the SFTP server for another SFTP user we were rebuffed. Thus, setting up a new API connection just for outbound invoices was not a solution.
In end our solution is based on sending invoices when orders are not be received. Observing that orders come in between 7a.m. and 8 p.m. we schedule creation of the invoices between 1 a.m. and 3.a.m. An Azure function schedules and creates invoices between these times only. There is not enough time to detail that process here, but I might do that in a subsequent blog.
Thus, we configure the invoice send action like this;
In this case the pickup from a service bus is throttled. This ensures we do not exceed 5 concurrent sends to the SFTP API connector. Notwithstanding this we configure 6 exponential retires on the send SFTP action.
However, whenever the invoice runs the order pick up starts to throw trigger failures because inevitably, we exceed the 5 concurrent connections limit to the SFTP server. Thus, we schedule a DevOps pipeline to disable the alerts at 8 p.m. and enable the alerts at 7 a.m. Alternatively, we could have also chosen to disable and enable the orders logic app too.
The pipeline task runs an Azure CLI task to disable or enable the alert.
az monitor metrics alert update --enabled false --name metricAlerts_nz-edi-customerpreprocessing-spotless-prod-la-TriggerFailure --resource-group nz-edi-prod-rgI have not shown the enabling of the alerts at 7 a.m. but I think you can work out how I did that.
Conclusion
This is the end of a story about subtle tuning to conquer the SFTP API connection from Hell. I also show how to use DevOps to schedule shutdown and startup of Logic App processes. As a side it would be nice if Microsoft would add the functionality to the SFTP Logic App trigger such that you can schedule when it runs. I hope you enjoyed this story and please let me know if you any better ways of doing this.
