Introduction

In my previous article I showed you how to create alerts for SQL transation logs reaching a threshold full amount using SSMS and T-SQL. These alerts are set up on a per database per instance basis. This means that you could spend an awful lot of time going through all of your servers and setting them up manually.

In this follow on article I will provide you with the tools to push these alerts to all of your remote instances using one handy PowerShell script and one T-SQL script. I am going to assume you have a basic understanding of PowerShell. If you haven’t used PowerShell yet, I would highly recommend you begin to take a look as it is a massive time saver across all of your responsibilities. (see PowerShell Basics)

Both of the scripts used in this article are included at the bottom of the page.

Pre-requisites

This article is going to make some assumptions about your environment as listed below:

1. Database Mail is configured on all of your servers.
2. You have a standard operator which has the same name across all of your instances.
3. You have a text file with all of your instances listed, or can build one.
4. The account you run these scripts under has admin rights across all of your instances.
5. You are running SQL 2005 and above

T-SQL Alert Script

This script can be used for all manner of alerts with a few tweaks, but I am going to concentrate on the standard Alert ‘SQLServer:Databases|Percent Log Used’.  I showed you previously how to set these alerts up manually using either SSMS or T-SQL. I am going to take the T-SQL method and create a file with the add alert statement as below:

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'DBA - Log Used Space - #DATABASENAME#')
EXEC msdb.dbo.sp_delete_alert @name=N'DBA - Log Used Space - #DATABASENAME#';
EXEC msdb.dbo.sp_add_alert 
 @name=N'DBA - Log Used Space - #DATABASENAME#', 
 @message_id=0, 
 @severity=0, 
 @enabled=1, 
 @delay_between_responses=3600, 
 @include_event_description_in=1, 
 @notification_message=N'The Log file for the database is >= 85% full. Please investigate any issues preventing the log from de-allocating used space.', 
 @category_name=N'[Uncategorized]', 
 @performance_condition=N'SQLServer:Databases|Percent Log Used|#DATABASENAME#|>|85', 
 @job_id=N'00000000-0000-0000-0000-000000000000';

Note the use of #DATABASENAME# as a place holder for each database you will be configuring an alert for. We will replace these with the actual database name in the PowerShell script.

We also need to add the response for the alert when our condition is met, as SQL does not include this by default, so the last part is the addition of the response:

EXEC msdb.dbo.sp_add_notification @alert_name = N'DBA - Log Used Space - #DATABASENAME#', @operator_name = N'DBA', @notification_method = 1;

This is where we have defined what operator we are sending the response to, so if you use a different name for your operators, this is the place to change it. You can also amend the alert to allow for your own response times and threshold alerts. If you have a lot of changes to it you can build it in SSMS and then script it out, making sure to set the placeholder for the database name.

Once you have played with this alert you can amend the script to use any naming convention you prefer for the alert name.

And there we have the SQL required to set the alerts up, nice and simple so far.

PowerShell Script

At a high level the PowerShell script follows this logic:

1.       Loop through list of servers/instance from text file.

2.       Connect to each remote server one at a time.

3.       Pull list of all user databases from sys.databases.

4.       Cycle through each database following these steps:

1.       Get text contents of alerts log .sql file.

2.       Replace #DATABASENAME# with database name from sys.databases.

3.       Drop and Create alerts for that database*.

5.       Move to next database.

*- I included the drop and create so I can run these scripts periodically against my servers so any newly created database are included in my monitoring.

My text file (instanceList.txt) is a simple list of all of my instances in the convention of:

Server1

Server2

Server2\Accounting

Server2\Marketing

…

The full content of the PowerShell script is as below:

#the location of our server list and SQL file
$instance_list = "C:\Powershell\instancelist.txt"
$alert_sql_file = "C:\Powershell\Log Full Alert.sql"
#loop through list of servers/instance
$servers = Get-Content $instance_list | Where-Object { $_ -ne '' }
foreach($instance in $servers)
{
    try
    {
        #make sure we can connect to the remote instance using our current credentials
        $connection_string = "Server=$instance;Database=master;Integrated Security=sspi"
        $sqlConnection = new-object System.Data.SqlClient.SqlConnection $connection_string
        $sqlConnection.Open()
       
        #make sure we connected ok
        if ($sqlCOnnection.State -eq "open")
        {    
            #get a list of the user databases
            $sqlCommand = $sqlConnection.CreateCommand()
            $sqlCommand.CommandText = "SELECT name FROM sys.databases WHERE database_id > 4" 
            $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcommand
            $dataset = New-Object System.Data.DataSet
            $adapter.Fill($dataSet) | out-null
            $uds = $dataset.Tables[0]
           
            #work through each database adding the alert and the response
            foreach ($set in $uds)
            {
                $database_name = $set.name
           
                #get the alerts sql script
                $alert_sql = Get-Content $alert_sql_file
                #make sure we are acting on the correct database
                $alert_sql = $alert_sql -replace "#DATABASENAME#", "$database_name"
                
                #if this is an instance, make sure we correct the Alert Object to reflect this
                if ($instance.contains("\"))
                {
                    #get the actual instance name
                    $short_instance = $instance.Substring($instance.IndexOf("\") + 1)
                    $short_instance = "MSSQL$ $short_instance" -replace " ", "" 
                
                    #replace this in the SQL alert script
                    $alert_sql = $alert_sql -replace "SQLServer", $short_instance
                }
               
                #now try and push the alert onto the target instance
                $AddAlertCommand = $sqlConnection.CreateCommand()
                $AddAlertCommand.CommandText = $alert_sql
                $AddAlertCommand.ExecuteNonQuery()
            }
        }   
        
        #clean up
        $sqlConnection.Close() 
    }
    #deal with any problems
    catch [Exception]
    {
        write-host "error on instance: " $instance
        write-host $_.Exception.Message
    }
}

The exception trap at the bottom is a useful way of checking what servers may be causing you issues; the first time I ran this it showed me 3 common problems

• Standard Operator was not configured.
• Server was SQL 2000.
• The server was not available remotely.

This allowed me to clean these servers up and make sure they conformed to my standard configurations.

Once you run this script you should begin to receive alerts for any databases on your server that meet the threshold conditions.

Conclusions

These simple and easily amendable scripts will allow you to manage and push alerts to all of your remote instances without having to log in and manually go through every database. If you push this script onto a central management server and schedule it to run once a week via your SQL Agent you can also make sure any new databases are included once they have been added.

References:

• http://technet.microsoft.com/en-us/library/ms190670.aspx
• http://www.sqlservercentral.com/articles/alerts/89885/