Configure Database Mail

When it comes to monitoring servers and software, I like to have them notify me of issues rather than my having to periodically ask them how they are doing. The foundation of that is being able to have them send me email.

SQL Server has a feature called Database Mail that works very well for this purpose, but it can be a bit difficult to configure correctly, especially if you try to do it manually. I will show several ways to make use of Database Mail in future posts, but in this post I will show how to get it configured correctly. The necessary steps are:

1. Enable Database Mail and SQL Agent: This sets the features so they can be used, as they are generally disabled by default.
2. Setup Database Mail
1. Create a Database Mail profile: A profile is a collection of mail accounts, and it must exist before we can add an account to it.
2. Create a Database Mail account: This is the account we are going to use for sending email; it contains all the information about the SMTP server and the address to be used as the sender.
3. Add the account to the profile: The account cannot be used unless it is a member of a profile.
4. Grant the DBMailUsers role access to the profile: This provides the security right that is needed and also makes the profile the default.
3. Send a test message: At this point, Database Mail should be ready to send a message and this will confirm that it works properly. Depending on your mail client, you may need to refresh your InBox view or give it a command to fetch new mail to avoid waiting for the standard interval for checking if new mail has arrived.
4. Configure SQL Agent to use the profile and operator: Without this step, SQL Agent cannot use Database Mail. Unfortunately, it requires that the SQL Agent service be restarted. I didn't add the restart to the script as it is likely that security would prevent it from working in many environments. You can use SSMS (right-click on SQL Agent in Object Explorer and select Restart), SQL Server Configuration Manager, or the Windows Services Control Panel applet to restart the service manually.

The script below is what I use to accomplish all of this except the service restart. Just substitute your own values in the block of SET statements near the top and execute it. Each of these values is described in the script.

/* Configure Database Mail.sql

Purpose:        Configures Database Mail and sends a test message
Author:         Mark Freeman
Last Edited:    2011-11-20
Instructions:   Update the values in the block of SET statements as appropriate,
                 then execute.
References:     Adapted from:
                 http://www.kannade.com/tech/database-mail-and-sql-agent-mail-setup-using-script-t30.html and
                 http://www.mssqltips.com/sqlservertip/1736/sql-server-database-mail-and-sql-agent-mail-setup-by-using-a-script/
Compatibility:  SQL Server 2005 and newer
License:        This work is licensed under a Creative Commons
                 Attribution-NonCommercial-ShareAlike 3.0 Unported License.
                 http://creativecommons.org/licenses/by-nc-sa/3.0/
*/

SET NOCOUNT ON
USE [master]

DECLARE @ProfileName                    SYSNAME,
        @SmtpServer                     SYSNAME,
        @OperatorName                   SYSNAME,
        @ToEmail                        SYSNAME,
        @RecipientDomainName            SYSNAME,
        @SenderDomainName               SYSNAME,
        @ReplyToEmail                   SYSNAME,
        @SmtpUserName                   NVARCHAR(128),
        @SmtpPassword                   NVARCHAR(128),
        @SmtpPort                       INT,
        @SmtpUseDefaultCredentials      BIT,
        @SmtpEnableSsl                  BIT;

-- Please update the values in the section below as needed:
SET @ProfileName                = 'MyProfile';          /* This could be your company name,
                                                            domain name, or something
                                                            generic such as
                                                            'Database Mail Profile' */
SET @SmtpServer                 = 'smtp.mydomain.com';  /* Usually something like
                                                            'smtp.mydomain.com' */
SET @SmtpPort                   = 25;                   /* This is the standard port,
                                                            but yours may differ */
SET @RecipientDomainName        = 'mydomain.com';     /* This may differ from the domain
                                                            of the sender if, for example,
                                                            you arehaving servers at other
                                                            companies send you emails */
SET @SenderDomainName           = @RecipientDomainName; /* This is usually the domain of the
                                                            SMTP server */
SET @SmtpUserName               = NULL;                 /* Only needed if your SMTP server 
                                                            requires authentication */
SET @SmtpPassword               = NULL;                 /* Only needed if your SMTP server 
                                                            requires authentication */
SET @SmtpUseDefaultCredentials  = 0                     /* Change to 1 to use the
                                                            credentials of the SQL Server
                                                            Database Engine and ignore the 
                                                            user name and password
                                                            settings */
SET @SmtpEnableSsl              = 0;                    /* Change to 1 if your SMTP server
                                                            uses SSL encryption */
SET @ToEmail                    = 'dba' + @RecipientDomainName;
SET @ReplyToEmail               = 'sqlserver' + @SenderDomainName;
-- Please update the values in the section above as needed


-- Enable Database Mail and SQL Agent
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure 'Database Mail XPs', 1;
EXEC sp_configure 'Agent XPs',1;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;

-- Setup Database Mail
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile)
    PRINT 'Database Mail already configured';
ELSE BEGIN
    DECLARE @FromEmail  SYSNAME,
            @FromName   SYSNAME;
    SELECT  @FromEmail  = REPLACE(@@SERVERNAME,'\','_') + @SenderDomainName,
            @FromName   = 'SQL Server ' + @@SERVICENAME;

    -- Create a Database Mail profile
    EXEC msdb.dbo.sysmail_add_profile_sp
        @profile_name = @ProfileName
        ,@description = 'Mail profile setup for email from this SQL Server';

    -- Create a Database Mail account, assuming Windows Authentication
    EXEC msdb.dbo.sysmail_add_account_sp
        @account_name              = @ToEmail
        ,@description              = 'Mail account for use by database processes'
        ,@email_address            = @FromEmail
        ,@replyto_address          = @ReplyToEmail
        ,@display_name             = @FromName
        ,@mailserver_name          = @SmtpServer
        ,@mailserver_type          = 'SMTP'
        ,@port                     = @SmtpPort
        ,@username                 = @SmtpUserName
        ,@password                 = @SmtpPassword
        ,@use_default_credentials  = @SmtpUseDefaultCredentials
        ,@enable_ssl               = @SmtpEnableSsl;

    -- Add the account to the profile
    EXEC msdb.dbo.sysmail_add_profileaccount_sp
        @profile_name              = @ProfileName
        ,@Account_name             = @ToEmail
        ,@sequence_number          = 1;

    -- Grant access to the profile to the DBMailUsers role
    EXEC msdb.dbo.sysmail_add_principalprofile_sp
        @profile_name              = @ProfileName
        ,@is_default               = 1;
END

-- Send a test message
DECLARE @MessageText VARCHAR(100);
SET @MessageText = 'Test from SQL Server ' + @@SERVICENAME;
EXEC msdb..sp_send_dbmail
    @profile_name                  = @ProfileName
    ,@recipients                   = @ToEmail
    ,@subject                      = @MessageText
    ,@body                         = @MessageText;

-- Create operator
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysoperators WHERE name = @ToEmail)
    EXEC msdb.dbo.sp_add_operator
        @name                      = @ToEmail 
        ,@enabled                  = 1 
        ,@email_address            = @ToEmail;

EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator = @ToEmail;

-- Configure SQL Agent to use the profile and operator
USE [msdb];
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1;
EXEC master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    ,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
    ,N'UseDatabaseMail'
    ,N'REG_DWORD'
    ,1;
EXEC master.dbo.xp_instance_regwrite
    N'HKEY_LOCAL_MACHINE'
    ,N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
    ,N'DatabaseMailProfile'
    ,N'REG_SZ'
    ,@ProfileName;
PRINT '**** You must manually restart the Service: SQL Server Agent ('
    + @@SERVICENAME + ')';

Finding Foreign Keys without Indexes

I have seen a number of blog posts on this topic that go into detail as to why it is important to have such indexes and sometimes provide helpful queries. Examples would include:
    http://www.brentozar.com/archive/2011/09/indexing-for-deletes/
    http://michaeljswart.com/2009/12/finding-your-missing-keys/

However, I wanted to take it a step further. The following query not only finds the Foreign Keys without an associated Index, it also provides the command needed to create each index in both XML and text formats.

If you click on the XML result, SSMS will open a new window containing the command already nicely formatted for you to copy into your source control system of preference, or into a new SSMS window so you can execute it. Of course, the query as shown uses my preferred naming and formatting standards for the indexes, but it should be easy enough to modify it to use your own.

/* What indexes are needed for my foreign keys.sql

Purpose:    Find foreign keys without an index and the command needed to create the index.
            Note that if these aren't showing up in the missing_index* views you might not
            think that you need them, but not having them will cause some deletes from
            referenced tables to perform full table scans. For example, without the
            appropriate foreign key index, deleting a customer would result in a full scan
            of the invoice table to ensure that the customer PK does not exist in any
            invoice record.

Author:     Mark Freeman (@m60freeman)

Changes:    2011-09-01  Created
            2011-11-20  Eliminated matches to index where the referenced column is not the
                         first column in the index, added comment and brackets in Potential
                         DDL, and added handling of objects with the same name in multiple
                         schemas
*/
SELECT      rc.Constraint_Schema                            AS Base_Schema
            ,ccu.Table_Name                                 AS Base_Table
            ,ccu.Column_Name                                AS Base_Column
            ,ccu.Constraint_Name                            AS Foreign_Key
            ,ccu2.Constraint_Schema                         AS Referenced_Schema
            ,ccu2.Table_Name                                AS Referenced_Table
            ,ccu2.Column_Name                               AS Referenced_Column
            ,'IX_' + ccu.Table_Name + '_' + ccu.Column_Name AS Potential_Index_Name
            ,CAST('<?query --' + CHAR(13) +
                'CREATE INDEX [IX_' +
                ccu.Table_Name + '_' + ccu.Column_Name +
                ']' + CHAR(13) +
                CHAR(9) + '-- Needed to support ' +
                ccu.Constraint_Name + CHAR(13) +
                CHAR(9) + 'ON [' + rc.Constraint_Schema +
                '].[' + ccu.Table_Name + ']' + CHAR(13) +
                CHAR(9) + '(' + CHAR(13) +
                CHAR(9) + CHAR(9) + '[' + ccu.Column_Name +
                ']' + CHAR(13) + CHAR(9) + ');'
                + CHAR(13) + ' --?>' AS XML)             AS Potential_DDL_XML
            ,'CREATE INDEX [IX_' + ccu.Table_Name + '_' +
                ccu.Column_Name + ']' + ' ON [' +
                rc.Constraint_Schema + '].[' +
                ccu.Table_Name + ']' +  '([' +
                ccu.Column_Name +
                ']); -- Needed to support '
                + ccu.Constraint_Name                       AS Potential_DDL
FROM        information_schema.referential_constraints rc
            JOIN information_schema.constraint_column_usage ccu
                ON rc.CONSTRAINT_NAME           = ccu.CONSTRAINT_NAME
                    AND rc.CONSTRAINT_SCHEMA    = ccu.CONSTRAINT_SCHEMA
            JOIN information_schema.constraint_column_usage ccu2
                ON rc.UNIQUE_CONSTRAINT_NAME    = ccu2.CONSTRAINT_NAME
                    AND rc.CONSTRAINT_SCHEMA    = ccu2.CONSTRAINT_SCHEMA
WHERE       NOT EXISTS (
                SELECT  1
                FROM    sys.indexes i
                        JOIN sys.index_columns ic
                            ON  ic.[object_id]  = i.[object_id]
                                AND ic.index_Id = i.index_Id
                        JOIN sys.columns c
                            ON  c.[object_id]   = ic.[object_id]
                                AND c.column_id = ic.column_id
                        JOIN sys.objects o
                            ON  o.[object_id]   = c.[object_id]
                        JOIN sys.schemas s
                            ON  s.[schema_id]   = o.[schema_id]
                WHERE   c.name                      = ccu.COLUMN_NAME
                  AND   OBJECT_NAME(c.[object_id])  = ccu.TABLE_NAME
                  AND   s.name                      = ccu.TABLE_SCHEMA
                   AND   ic.key_ordinal              = 1
                )
AND         ccu.Table_Name NOT LIKE 'aspnet_%'
ORDER BY    ccu2.Constraint_Schema,
            ccu2.Table_Name,
            rc.Constraint_Schema,
            ccu.Table_Name,
            ccu.Column_Name;