I'm exploring the idea of using PowerShell Automation Workbooks to manage tasks that typically require SQL Jobs. It seems straightforward to run T-SQL scripts or transfer data between servers with the dbatools module. However, I'm concerned about the challenge of scheduling, specifically the lack of multi-step support in the Automation framework. For instance, if I had a job that requires 20 sequential steps, it seems like I'd have to consolidate everything into a single PowerShell script with 20 blocks of code instead of handling each step separately. Is there any feature or tool I'm missing to make this more efficient? Or is there a better approach?
2 Answers
You can definitely start another Runbook from your current one using the Start-AzAutomationRunbook command, just make sure the necessary permissions are set on your automation account. This could streamline running multiple scripts sequentially!
Have you thought about combining logic apps with Azure Functions? They can handle multiple steps effectively, possibly giving you the flexibility you're looking for. Some users have found that Logic Apps can perform tasks similar to what Powershell does, although there are mixed opinions on performance.
I switched from Azure Functions to Automation and noticed it works better for my needs. It's simpler, but the insights from Azure Functions were helpful despite their drawbacks. Just keep in mind that Logic Apps might require some finesse when it comes to task completion and integration.
You could attach that command to the end of each runbook. Alternatively, consider creating a master runbook to manage the workflow. It can track completions and initiate the next runbook once it's done, giving you better control over logging and job resumes.