Big Data, Data Analytics and Data Science | BI-BIGDATA.com

In this article I will try to wrap how to PowerShell to manage your SQL Server Notifications. And this is very much regular scenario for most of the SQL Server DBA’s to generate notifications when issues arise. There are many ways that we can handle these notifications, but one of the simplest and most reliable and the most common way is email notifications.

We will write simple email function which will have some hard coded values into it. But in a real time scenarios we normally reads these from configuration files such as text file, XML file, ect. I have written a simple function below called SendEmail and this function has tow parameters as subject and the body. This function has From, To email addresses which are hardcoded for now. For multiple recipients we can pass delimited string having valid email addresses.

As we are going to use SMTP relay to send email with PowerShell so we need to provide the information about SMTP Server where I hardcoded about  Hotmail Outgoing Mail Server (SMTP) – smtp.live.com and Port 587 which is the default port for sending secure mail. All these things actually can be dynamic with configuration as I mentioned earlier.

Once all these details are done then created new object cmdlet to create System.Net.Mail.MailMessage object where we can create/configure actual message with from, to email addresses along with subject and body. Then go out and create a net.mail.SMTP client object  with passing two parameters. The first is the name of the SMTP server and the second is the port number we want to use to send the email. Once that’s done we need to set a property of that object that tells us we are certainly going to use SSL or Secure Socket Layers. SSL is just a way to encrypt the email so that when it goes from your computer to the SMTP Servers, nobody can see and read inside of it. Once done need to authenticate the sender credentials to the SMTP server. Finally we are going to call Send method of client object with passing your message as a parameter. That’s it.

Once we are done with the function it’s time to call it by passing parameters to it i.e. Subject and the Body text.

Function SendMail([string]$Subject, [string]$Body)
{
 #pick up these values from configuration
 $SmtpServer = "smtp.live.com"
 $SmtpServerPort = "587"
 $SmtpUser = "FirstName.LastName@domain.com"
 $SmtpPass = "Password"
 $From = "FirstName LastName <FirstName.LastName@domain.com>"

 #assign multiple recipients with delimited string
 [string[]]$To = "firstname.lastname@domain.com,lastname.firstname@yourdomain.com"

 #Create Message Object
 $Message = New-Object System.Net.Mail.MailMessage( $From , $To )
 $Message.Subject = $Subject
 $Message.IsBodyHtml = $true
 $Message.Body = $Body

 #Create SMTP Client Object
 $SMTPClient = New-Object System.Net.Mail.SmtpClient( $SmtpServer , $SmtpServerPort )

 #Enable SSL(Secure Socket Layers) Protocol so email will be sent securly
 $SMTPClient.EnableSsl = $true

 #Create a credential object we will use to authenticate ourselves to the SMTP Server
 $SMTPClient.Credentials = New-Object System.Net.NetworkCredential( $SmtpUser , $SmtpPass );

 #Send Final Message
 $SMTPClient.Send( $Message )
}

#Call the function by passing parameters to it.
SendEmail -Subject "PowerShell Notification" -Body "This is system genrated PowerShell Notification."

So using this is a very simple way to create notifications within your PowerShell scripts.

We will be writing more about PowerShell in coming days stay tune.

Hope this Helps.

Thanks

Sandip