Did you know it is possible for SQL Server Agent to alert you of problems if something goes haywire with your Agent? Have you ever had an issue with Alerts not being sent after critical events? Then you might need to configure the SQL Server Agent Fail-Safe Operator to save the day. A Fail-Safe WHAT you might say?? This is a special SQL Agent Operator configured in the SQL Agent Alert System in the chance any of the following situations occur.
- If the assigned Operator cannot be reached
- SQL Server Agent cannot access certain system tables in MSDB
- No Operator is scheduled for the time the Alert was triggered
- Example: If no Operator is scheduled for Saturday, then the Fail-Safe Operator will be utilized in the event of an Alert
Walking through the steps below will configure the Fail-Safe Operator on your SQL Server.
PowerShell is a great tool to accomplish this so I have put together the steps needed to complete the configuration for your SQL Server using PowerShell commands.
Configuration Steps:
- Enable Database Mail on SQL Server
- Setup a Database Mail Account
- Setup a Database Mail Profile
- Create a SQL Server Agent Operator
- Enable Database Fail-Safe Operator
1. Enable Database Mail on SQL Server
By default SQL Server is not installed with the Database Mail feature enabled. The first check performed is to see the current setting for the Server.
As you can see by the RunningValue, my Database Mail is disabled. Here is the PowerShell command that will change this setting.
Set-DbaSpConfigure -SqlInstance "localhostsql2017" -Name "Database Mail XPs" -Value $true
The dbatools PowerShell module is going to do a lot of the heavy lifting with this configuration. As you can see, we have a Get and Set command to work with the SQL Server configuration values.
Here you can see, our NewValue setting for the Database Mail is enabled.
2. Setup Database Mail Account
We are now ready to configure the Database Mail Account which is the next step in getting the mail configured and ready to send.
$mailAccount = New-DbaDbMailAccount -SqlInstance "localhostsql2017" -Name HomeDbMailAccount `
-DisplayName "Home SQL DB Mail" -EmailAddress "gbargsley@gmail.com" -ReplyToAddress "gbargsley@gmail.com" `
-MailServer "smtp.gmail.com" -ForceWe are using the
New-DbaDbMailAccount command to perform this step. You will need the following items in order to complete this step:
- Name of Account being created = HomeDbMailAccount
- What is displayed in messages sent = Home SQL DB Mail
- Email Address to use for sending = gbargsley@gmail.com
- Email Address used for replies = gbargsley@gmail.com
- Name of Mail Server = smtp.gmail.com
- Force = This will create the account even if mail is not configured or account already exists
Once this is executed, a new Database Mail Account is created. Depending on your mail configuration and how mail will be sent, it might be necessary to perform some additional configurations. My configuration uses Gmail since I am performing these steps on my personal computer.
There are a few more steps that must be performed to complete the Database Mail Account setup for Gmail. This was a bit tricky to determine since Gmail has very tight security protection when sending unknown mail clients. Gmail lists SQL Server as an unsecure app. This link was super helpful in getting passed the hurdle of using Gmail.
For information on using Hotmail, Google or Outlook for sending your Database Mail
$mailServer = $mailAccount.MailServers.Item(0) $mailServerUsername = Read-Host "Enter the username for the SMTP account" $mailServerPassword = Read-Host "Enter the password for the SMTP account" -AsSecureString $mailServer.SetAccount($mailServerUsername, $mailServerPassword) $mailServer.EnableSsl = 'True' $mailServer.Port = 587 $mailServer.Alter()
Using Gmail you must setup a username and password that is stored in the Database Mail Account for Basic Authentication settings. SSL is required so that setting must be enabled. Finally, the port that Gmail uses for secure SMTP email is configured.
3. Setup Database Mail Profile
Ok… The Database Mail Account is created and ready to use. Now we need to create a Database Mail Profile to be used for sending mail.
New-DbaDbMailProfile -SqlInstance "localhostsql2017" -Name "HomeDbMailProfile" -MailAccountName "HomeDbMailAccount"
- Name of Profile being created = HomeDbMailProfile
- Name of the Account created = HomeDbMailAccount
This is a simple profile for getting our pre-requisite satisfied. There are more advanced settings that can be configured with Database Mail Profiles, see this link.
4. Create SQL Server Agent Operator
Now everything we need to start sending Database Mail is in place. Let’s get to work on creating a SQL Agent Operator that will be used to send the mail for our Fail-Safe setting.
This was a little bit more involved than I had planned and took some effort to get working. There are probably more efficient methods, but this worked for these purposes. Let’s take a look.
$JobServer = $server.JobServer $operators = $JobServer.Operators $operators = new-object Microsoft.SqlServer.Management.Smo.Agent.Operator( $JobServer, "DBA") $operators.EmailAddress = "gbargsley@gmail.com" $operators.Create()
Using the SQL Server SMO, I am able to create an Operator and assign my Email Address.
5. Enable Fail-Safe Operator
Finally, we are on the final and most important step for setting up our Fail-Safe Operator.
$AgentServer = Set-DbaAgentServer -SqlInstance "localhostsql2017" -AgentMailType "DatabaseMail" -DatabaseMailProfile "HomeDbMailProfile" $AgentServer.AlertSystem.FailSafeOperator = "DBA" $AgentServer.AlertSystem.NotificationMethod = "NotifyEmail" $AgentServer.AlertSystem.Alter()
I am using the
Set-DbaAgentServer command to set the Mail System and Mail Profile for the SQL Agent Server Alert System. Then I have to use SQL Server SMO commands again to actually enable the Fail-Safe Operator.
Once this is complete, we have our SQL Server Agent configured for alerting when something goes wrong with normal Operators and Alerts.
In Conclusion:
The entire PowerShell script used for this process is below.
## Configure the SQL Server Instance to allow Database Mail to be used
Get-DbaSpConfigure -SqlInstance "localhostsql2017" -Name "Database Mail XPs"
Set-DbaSpConfigure -SqlInstance "localhostsql2017" -Name "Database Mail XPs" -Value $true
## Configure the Database Mail Account
$mailAccount = New-DbaDbMailAccount -SqlInstance "localhostsql2017" -Name HomeDbMailAccount `
-DisplayName "Home SQL DB Mail" -EmailAddress "gbargsley@gmail.com" -ReplyToAddress "gbargsley@gmail.com" `
-MailServer "smtp.gmail.com" -Force
# Set the server for the account
$mailServer = $mailAccount.MailServers.Item(0)
$mailServerUsername = Read-Host "Enter the username for the SMTP account"
$mailServerPassword = Read-Host "Enter the password for the SMTP account" -AsSecureString
$mailServer.SetAccount($mailServerUsername, $mailServerPassword)
$mailServer.EnableSsl = 'True'
$mailServer.Port = 587
$mailServer.Alter()
## Configure the Database Mail Profile
New-DbaDbMailProfile -SqlInstance "localhostsql2017" -Name "HomeDbMailProfile" -MailAccountName "HomeDbMailAccount"
## Gather instance settings for enhanced configurations later
$server = Connect-DbaInstance -SqlInstance "localhostsql2017"
## Add SQL Agent Operator for FailSafe configuration
$JobServer = $server.JobServer
$operators = $JobServer.Operators
$operators = new-object Microsoft.SqlServer.Management.Smo.Agent.Operator( $JobServer, "DBA")
$operators.EmailAddress = "gbargsley@gmail.com"
$operators.Create()
## Configure the SQL Server Agent Notification properties
$AgentServer = Set-DbaAgentServer -SqlInstance "localhostsql2017" -AgentMailType "DatabaseMail" -DatabaseMailProfile "HomeDbMailProfile"
$AgentServer.AlertSystem.FailSafeOperator = "DBA"
$AgentServer.AlertSystem.NotificationMethod = "NotifyEmail"
$AgentServer.AlertSystem.Alter()