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.):

Alert Servers

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:

Alerts Text

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:

AlertsPS1

Once your failovers and maintenance are complete, run the same script again but enter “1” instead to re-enable the Alerts:

AlertsPS2

That’s all it takes to save your Inbox during a regular maintenance window!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s