When setting up monitoring for your on-premises Microsoft SQL Server databases, there are a few key tools and configurations to put in place. Two important pieces are Database Mail for email notifications, and SQL Server Agent for alert automation.  Properly configuring these tools is essential for proactively managing the health and performance of your database environment. 

How To: Enable Database Mail 

Here's a step-by-step guide for setting up Database Mail in SQL Server: 

Step 1: Enable Database Mail 

  • Connect to your database server using SQL Server Management Studio (SSMS). 
  • In Object Explorer, expand the Management node. 
  • Right-click on Database Mail and select Configure Database Mail. 
  • Click Next on the welcome screen of the Database Mail configuration wizard. 

Important note: Database Mail is not available in Microsoft SQL Server Express edition, but available in Microsoft SQL Server Developer edition. 

Step 2: Choose the Setup Option 

  • Select "Set up Database Mail by performing the following tasks". 
  • Click Next. 

Step 3: Create a Database Mail Profile 

  • On the New Profile screen, the wizard will create a new profile automatically. 
  • Click Next to proceed. 

Step 4: Configure Database Mail Accounts 

  • On the Configure Database Mail Accounts screen, click Add to create a new mail account. 
  • Provide a descriptive Account name. 
  • Enter the email address this account will send mail from. 
  • Specify the details of the outgoing SMTP mail server: 
    • Enter the SMTP server name or IP address. 
    • Specify the port number (usually 25 for non-encrypted connections and 587 for encrypted connections). 
    • Fill in the authentication details if required. 
  • Select security settings that match your organization's email policies (e.g., using encrypted connections and requiring authentication). 
  • Click the Test button to send a test email and verify the configuration. 
  • Click OK to save the mail account settings. 
  • Click Next to proceed. 

Step 5: Create a Default Public Profile 

  • The wizard will create a Default public profile that includes the newly created mail account. 
  • Click Next to proceed. 

Step 6: Configure System Parameters 

  • Review the system parameters for Database Mail, such as maximum file size for attachments, character set, and logging behavior. 
  • Adjust the settings if necessary to suit your environment. 
  • Click Next. 

Step 7: Complete the Configuration 

  • Review the summary of actions that will be performed. 
  • Click Finish to complete the Database Mail configuration wizard. 

After completing these steps, Database Mail will be enabled and ready to use for sending notifications from your SQL Server database. 

Troubleshooting Tips: 

  • Ensure you use a valid SMTP server name and port. 
  • Provide the correct authentication credentials if required by the mail server. 
  • Configure the SMTP server to allow relay from the database server IP address. 
  • Work with your email administrators to properly set up permissions and settings on both the database server and SMTP server. 

Configuring SQL Agent Alerts  

With Database Mail enabled, follow these steps to set up SQL Server Agent to automate alerts: 

  •  Ensure SQL Server Agent is running: 
    • If the SQL Server Agent service is disabled, right-click on SQL Server Agent in Object Explorer and select Start. 
  • Enable the mail profile for sending alerts: 
  • Right-click on SQL Server Agent and select Properties.
  • In the SQL Server Agent Properties dialog, select the Alert System page. 
  • Check the "Enable mail profile" option and configure the mail profile settings as necessary. 
  • Click OK to save the changes. 

Create a new alert: 

  • Expand the SQL Server Agent node in Object Explorer. 
  • Right-click Alerts and select New Alert. 
  • In the New Alert dialog, give the alert a descriptive Name. 

 

Choose the alert Type: 

  • Select one of the following alert types: 
  • SQL Server event alert: Triggers when a specific SQL Server error or event occurs. 
  • SQL Server performance condition alert: Triggers when a performance metric exceeds a threshold. 
  • WMI event alert: Responds to server and operating system level events using WMI. 

 Configure the alert definition (varies by alert type): 

  • For SQL Server event alerts: 
    • Choose the Database name, Error number, and Severity level to monitor. 
    • Use wildcard characters (%) in the database name to match multiple databases. 
  • For SQL Server performance condition alerts: 
    • Choose the performance Object, Counter, and Instance if applicable. 
    • Specify the Alert if counter threshold condition. 
  • For WMI event alerts: 
    • Define the WMI namespace, query, and condition to monitor. 

Define the alert response: 

  • On the Response page, select one or more actions to occur when the alert is triggered: 
    • Notify operators: Sends email or pager notification to predefined operators. 
    • Execute a SQL Server Agent job: Runs a predefined set of tasks. 
    • Write to the Windows Application event log: Logs an entry with alert details. 

 Save the alert: 

  • Review the alert definition and click OK to save it. 
  • The new alert is now active, and SQL Server Agent will monitor the specified conditions. 

 

 Example: Creating a "Transaction Log Over 5 GB" Alert  

  • Right-click Alerts under the SQL Server Agent node and select New Alert. 
  • Name the alert "Transaction Log Over 5 GB". 
  • For the alert Type, choose SQL Server performance condition alert. 
  • Set the Object to Databases, the Counter to Log File(s) Size (KB), and the Instance to <All instances>. 
  • In the Alert if counter section, set the condition to `rises above` 5210000 KB (5 GB). 
  • On the Response page, click New and choose Notify operators. Select the operators to receive email notifications. 
  • Click OK to save the response and the alert definition.

By following this same process, you can define alerts to monitor for a wide variety of critical conditions and errors. Some common alerts include: 

  • Severity 16 or higher SQL Server errors 
  • Deadlocks exceeding a certain frequency 
  • Long-running queries over a certain duration  
  • Disks with less than 10% free space 
  • Databases that have not had a successful backup in the past 24 hours 
  • Servers with consistently high CPU usage 

By following these steps, you can create alerts to monitor critical conditions and errors in your SQL Server environment. Focus on actionable alerts and adjust thresholds as needed to avoid alert fatigue. 

You may also like: How to Improve SSIS Data Flow Task Performance: A Step-by-Step Demo 

If you are using Red Gate's SQL Monitor, many of these alerts can be set up more easily through the intuitive web interface. SQL Monitor provides pre-configured alerts for common issues as well as the ability to create custom metrics and alerts. It also allows you to manage alerts across your entire SQL Server estate from a single dashboard. 

Getting Further Assistance 

In this guide, we've walked through the key steps for setting up Database Mail and SQL Server Agent alerts to provide robust monitoring for your SQL Server or Azure SQL Managed Instance. However, every environment is unique, and you may have questions about how to best configure monitoring for your specific needs. 

Contact us today to discover how our services can help your business succeed. Our expert team provides tailored solutions to optimize your technology infrastructure, enhance productivity, and drive growth.