How to Disable/Enable SQL Alerts on Multiple SQL Servers with PowerShell

When managing SQL Server AlwaysOn Availability Groups and/or standard Database Mirroring, setting up SQL Alerts should be standard protocol. (See this SQL Server Pro article for more background on this.) These types of Alerts can be configured to send e-mails any time the defined event occurs which is great for being notified of unexpected failovers and errors. However, the mass of e-mails generated during a regular planned maintenance that involves one or more failovers can be a little annoying. (Or maybe that’s just me… But if you’re reading this, maybe that’s just you as well. 🙂 I work with dozens of servers that have multiple Availability Groups and Mirrored Databases across them. Whenever we perform planned maintenance, the first thing I do is to disable all Mirroring and Availability Group related Alerts to spare my Inbox from the onslaught of unnecessary SQL Alert e-mails notifying me of the failovers. To do this quickly and efficiently, I’ve written a PowerShell script to disable/enable multiple Alerts on any number of servers automatically.

To execute the script, two input files are necessary. First, create a “Alert_Servers.txt” file containing all the servers with Alerts to be disabled. The Alert_Servers.txt file should be saved in the same directory as the PowerShell script and should look like this (Just replace “Server#” with your real server names.):

Secondly create a “Alerts.txt” file containing the specific Alerts you want to disable/enable. This file should also be saved in the same directory as the PowerShell script and should look similar to this:

In the example above, I’ve added all the specific Mirroring and Availability Group Alerts we set up as standard on all our servers. You can get a list of all configured Alerts on your servers by simply running this query:

SELECT name FROM msdb.dbo.sysalerts;

Just add new lines to this file for each Alert you’d like to disable/enable.

Finally, save the PowerShell Script below as a .ps1 file. (In the example shown below, I’ve named the file ToggleAlerts.ps1)

if (!(Get-PSSnapin -Name SQLServerCmdletSnapin100 -ErrorAction SilentlyContinue)) {
Add-PSSnapin SQLServerCmdletSnapin100}
if (!(Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue)) {
Add-PSSnapin SqlServerProviderSnapin100}

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo.Extended") | Out-Null

$Enabled = read-host "Enable or Disable? Please enter '1' or '0'"

if ($Enabled.ToString() -ne 1 -and $Enabled.ToString() -ne 0)
{
Write-Output "Only 1 and 0 are valid entries."
Exit
}

$ServerList = Get-Content Alert_Servers.txt
$AlertList  = Get-Content Alerts.txt

Write-Output "Please wait..."

foreach ($Server in $ServerList)
{
foreach ($Alert in $AlertList)
{
$Query = "IF EXISTS (SELECT 1 FROM msdb..sysalerts WHERE name = '$Alert')
EXEC msdb.dbo.sp_update_alert
@name = '$Alert'
,@enabled = $Enabled"

Invoke-Sqlcmd -Server $Server -Database "msdb" -Query $Query
}
}

if ($Enabled -eq 1)
{Write-Output "Alerts Enabled!"}

if ($Enabled -eq 0)
{Write-Output "Alerts Disabled!"}

Once you’ve created the ToggleAlerts.ps1 and the two text files, open a PowerShell window and execute the script. In the example below, I’ve saved all three files to the folder “C:\PowerShell\ToggleAlerts”. Enter “0” to disable alerts prior to the start of maintenance: