Database Mail sends SQL Server email for administrative and database processes. It is a practical fallback when an organization lacks enterprise observability, but it is not an enterprise-grade alerting platform. section 7.2 section 7.7
Mistake 1: treating Database Mail as a complete monitoring system.
- Use it with SQL Server Agent alerts to notify support teams about errors when a stronger monitoring tool is unavailable. section 7.2
- Expect an SMTP relay dependency: Database Mail does not provide SMTP services itself. section 7.2
- Plan cautiously for failover because Database Mail resides in msdb and may stop working correctly after a failover event. section 7.2
- Treat contained availability groups carefully because a noted bug prevented Database Mail from working in that configuration. section 7.2
Alert on a duplicate sales-order error
Create a SQL Server Agent operator, define an alert for message 50001 in the MagicChoc database, and send the notification to that operator. This connects Database Mail to an actionable error-monitoring workflow. section 7.2
Listing 7.12 Creating an alert using T-SQL
EXEC msdb.dbo.sp_add_alert
@name= 'DuplicateSalesOrder'
, @message_id=50001
, @enabled=1
, @database_name= 'MagicChoc' ;
EXEC msdb.dbo.sp_add_notification
@alert_name= 'DuplicateSalesOrder'
, @operator_name= 'Pete'
, @notification_method = 1 ;
Browse the complete T-SQL pattern for creating the alert and connecting it to an operator. section 7.2
