When I got the job to restore a bacpac file, I didn’t think much of it. Because it’s quite easy. Get the bacpac and restore it with Azure Data Studio or Sql Server Management Studio. Get coffee, done. Then I got these requirements:
- The bacpac needs to be downloaded daily from an sFTP server, without certificate
- The bacpac file is in a zip file
- The zip file is password protected
- Everything must be done serverless (Azure Automation, Logic App, Function App, Data Factory and/or Synapse Analytics)
- It’s a daily process that needs to be done without human supervision
Welcome to cloud development: Part A is easy, Part B is easy, Part C is mildly challenging, and combining A with B and C is a total nightmare because it turns out that A and B aren’t compatible, so by the end, you’re dealing with A” and b and D (because C, C’, C”, etc. wouldn’t work and c and c’ would work but had severe limitations preventing you from using it in this scenario).