How to Send Email and Notifications from Azure SQL Databases
Often a database administrator will find the need to send email notifications when specific things change within a database. This may be to be notified when inventory is low or when a new order comes in. Using Cotega, you can easily set up your database to allow for this capability. Please note that this capability is only availabe in the Professional and Enterprise plans.
How it Works
To support this capability you will create a CotegaMessageQueue table in your database which Cotega will constantly monitor. When there is a new row added to this table, Cotega will pick it up, extract the email addresses, email subject and body and create an email message to be sent. Once the messages have been successfully sent they are removed from the CotegaMessageQueue table. Using this table, you can create triggers which insert rows whenever you like.
Getting Started
In order to send emails from your Azure SQL database, you will need to have create a CotegaMessageQueue table and grant the user that will monitor this table SELECT, INSERT, UPDATE and DELETE priviledges.
CREATE TABLE [dbo].[CotegaMessageQueue](
[QueueId] [uniqueidentifier] NOT NULL default newid(),
[EmailAddresses] [nvarchar](250) NULL,
[MessageSubject] [nvarchar](256) NULL,
[MessageBody] [text] NULL,
[QueueTime] [datetime] NOT NULL default getdate(),
PRIMARY KEY (QueueId)
)
GRANT INSERT, UPDATE, DELETE, SELECT on [CotegaMessageQueue] to [COTEGA USER]
Next, you will need to have created a Cotega monitoring agent for the databas where you created the CotegaMessageQueue table. If you have not already done this, can do this by logging in to the Cotega dashboard and choose "Add Monitoring Agent" for the database you wish to execute this stored procedure from. Once this is done, you should see a new monitoring agent. Within this agent choose "Stored Procedure Scheduling" as shown below.
Once the Cotega monitoring agent has been created, choose "Edit Agent" as shown below.
At this point, you can now enable the monitoring of new messages. To do this, choose "Enable" next to the "Email Messageing" text within your agent as shown below.
At this point, choose "Save Agent" and everything is set up and Cotega will check for new messages from this table every 3 minutes. To test it, add a new row to the CotegaMessageQueue table. To send emails, to multiple recipients, simply add a comma between the email addresses. Here is an example of an insert that you could use (please remember to change the email addresses to valid emails).
insert into [CotegaMessageQueue] (EmailAddresses, MessageSubject, MessageBody) values ('[email protected]','Test Subject', 'Test Message Body')
Common Issues (FAQ)
Question: Why am I not receiving emails?
Answer: The most common reason for not receiving emails is due to the fact that you are sending an email to a serivce that has implemented DMARC. When service such as Yahoo receive emails, if they receive an email message that claimed to be from a company or a brand and it clearly wasn’t, it will be bounced back. This is done to stop "phishing". In this case you most likley registered with Cotega using a Yahoo.com or other similar email service. To avoid this issue, please register with Cotega using a real company name email address. For more details on this issue please contact us.