I am working on an Azure website and database and trying to figure out how to periodically query a table that has a date column. For each date in the column that is past a certain date (not necessarily the current date) I need to send an email.
Reading various articles and questions the simplest solution is to use Azure Automation and create the runbook to complete the task.
I have come across this article as an example of connecting and querying a database however I am uncertain as to the best way to achieve the objective.
Do I:
a) call a stored proc to do all the work (query the table and send the email) if this is actually possible. or;
b) query the table from the runbook and send the email from there.
Keen to know what is the best approach then I can look into how to actually code it.
While it is possible to send an email from a stored procedure, Azure Automation is meant as an integration service, so it sounds like it would be a better solution for this. Just to give an example of why -- the credentials for the email account to send from would be in plain text in the stored procedure. In Azure Automation we have credential assets, which allow you to store credentials securely but still reference them for use for operations in runbooks using Get-AutomationPSCredential.
To send an email from Azure Automation, use Send-MailMessage or look at the runbook examples of sending email here or in the runbook gallery.
The only reason I could see not to use Azure Automation for this would be if perf/scale is a requirement. Azure Automation runbook sandboxes are not disk, RAM or CPU intensive, so downloading thousands of DB records at once in a runbook could cause the runbook sandbox to run out of memory.