Tuesday, 17 September 2013

PowerShell to Enable and Configure Database Mail across various SQL servers


Recently I was asked to configure Database Mail on all SQL Servers in one of my client environment, Instead of using SSMS or TSQL I tried to use PowerShell with SMO objects to accomplish the task faster, Find the script below I created for the same, I took reference from http://www.sqlservercentral.com/articles/Database+Mail/74429/ and created my version of script, The Script can be run for one server or set of SQL Servers listed in a file. By default the Account and Profile name will be ServerName or SQLName_InstanceName in case of named instance which you can change by just assigning right  value to the variables in the bleow script.
# Script to create database mail
# Created by - Vinoth N Manoharan
# Version 1.0
# Date - 13/09/2013
# Script Help :-
#---------------
# Parameter 1 :- "SQL" to run powershell for Single server
#      "File" to give filename with list of servers
# Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)
#
# Example1:- DBMail.ps1 -Mode SQL -Server servername
# Example2:- DBMail.ps1 -Mode File -Server filename with fully define path like c:\test.txt

Param(
 [Parameter(Mandatory=$True,Position=0)]
 [String]$Mode,
 
 [Parameter(Mandatory=$True,Position=1)]
 [String]$Server
     )
Clear-Host
$ChkAccount = $null
if($Mode.ToUpper() -eq "SQL")
{
 $srt = "Server Name :- " + $Server
 $srt
 Echo "---------------------------"
 $SQLSplit = $Server.Split("\")
 $SQLcompname = $SQLSplit[0]
 if($SQLSplit[1] -ne $null)
 {
  $instancename = $SQLSplit[1]
  $ProfileName = $SQLcompname+"_"+$instancename 
 }else{$ProfileName = $SQLcompname}
 $AccountName = $ProfileName
 $smtpServer = "smtp.yourdomain"
 #Connect to the local, default instance of SQL Server.
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
 #Get a server object which corresponds to the default instance
 $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server -ArgumentList $Server -ErrorAction Stop
 #Define the Database Mail; reference it using the Server Mail property.
 if($srv.Configuration.DatabaseMailEnabled.ConfigValue -ne 1)
 {
 $srv.Configuration.DatabaseMailEnabled.ConfigValue = 1
 $srv.Configuration.Alter()
 Write-Host -ForegroundColor DarkGreen "Database Mail enabled"
 }
 $DBMail = $srv.Mail
 $ChkProfile = $DBMail.Profiles |where{$_.name -eq $ProfileName}
 $ChkAccount = $DBMail.Accounts |where{$_.name -eq $AccountName}
 #Delete existing profile
 if ($ChkProfile -ne $null)
 {
  Write-Host -ForegroundColor Red "DB Profile $ProfileName Already Exists and will be deleted"
  Try
  {
   $ChkProfile.Drop()
  } 
  Catch
  {
   Write-host -ForegroundColor Red "ERROR [DROP EXISTING PROFILE]:"$_.Exception.Message
   Exit;
  }
  Write-Host -ForegroundColor Red "DB Profile $ProfileName Dropped"
 }
 #Delete existing account
 if ($ChkAccount -ne $null)
 {
  Write-Host -ForegroundColor Red "DB Account $AccountName Already Exists and will be deleted"
  Try
  {
  $ChkAccount.Drop()
  }
  Catch
  {
   Write-host -ForegroundColor Red "ERROR [DROP EXISTING ACCOUNT]:"$_.Exception.Message
   Exit;
  }
  Write-Host -ForegroundColor Red "DB Account $AccountName Dropped"
 }
 #Create Mail Account
 $DBAccount = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount -argumentlist $DBMail,$AccountName -ErrorAction Stop
 Try
 {
 $DBAccount.Description = "SQL Alert Email Account"
 $DBAccount.DisplayName = $AccountName
 $DBAccount.EmailAddress = $AccountName+"@YourCompany.com"
 $DBAccount.Create()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [CREATE ACCOUNT]:"$_.Exception.Message
  Exit;
 }
 Try
 {
 $DBAccount.MailServers.Item($Server).Rename($smtpServer)
   $DBAccount.Alter()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [ADD SMTP TO ACCOUNT]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Account $AccountName Created"
 #Create Mail Profile
 $DBProfile = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailProfile -argumentlist $DBMail,$ProfileName -ErrorAction Stop
 Try
 {
 $DBProfile.Description = "SQL Alert Email Profile"
 $DBProfile.Create()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [CREATE PROFILE]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Profile $ProfileName Created"
 Try
 {
 $DBProfile.AddAccount($AccountName,0)
 $DBProfile.AddPrincipal("Public",$false)
 $DBProfile.Alter()
 }
 Catch
 {
  Write-host -ForegroundColor Red "ERROR [ADD ACCOUNT TO PROFILE]:"$_.Exception.Message
  EXIT;
 }
 Write-Host -ForegroundColor DarkGreen "DB Account [$AccountName] added to Profile [$ProfileName]"
}
elseif($Mode.ToUpper() -eq "FILE")
{
 $filename = $Server
 $computers = get-content $filename
 ForEach($computer in $computers)
 {
  $SQLServer = $computer -replace '^\s+', ''
  $srt = "Server Name :- " + $SQLServer
  $srt
  Echo "---------------------------"
  $Error_srt = 0
  if(($SQLServer -ne $null) -or ($SQLServer -like '^\s+'))
  {  
   $SQLSplit = $SQLServer.Split("\")
   $SQLcompname = $SQLSplit[0]
   if($SQLSplit[1] -ne $null)
   {
    $instancename = $SQLSplit[1]
    $ProfileName = $SQLcompname+"_"+$instancename 
   }else{$ProfileName = $SQLcompname}
   $AccountName = $ProfileName
   $smtpServer = "smtp.yourdomain"
   #Connect to the local, default instance of SQL Server.
   [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
   #Get a server object which corresponds to the default instance
   Try{
     $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server -ArgumentList $SQLServer -ErrorAction Continue
     #Define the Database Mail; reference it using the Server Mail property.
     if($srv.Configuration.DatabaseMailEnabled.ConfigValue -ne 1)
     {
      $srv.Configuration.DatabaseMailEnabled.ConfigValue = 1
      $srv.Configuration.Alter()
      Write-Host -ForegroundColor DarkGreen "Database Mail enabled"
     }
    }
   Catch {Write-host -ForegroundColor Red "ERROR [CONNECT SQL $Server]:"$_.Exception.Message;continue}
   #Define the Database Mail; reference it using the Server Mail property.
      $DBMail = $srv.Mail
   $ChkProfile = $DBMail.Profiles |where{$_.name -eq $ProfileName}
   $ChkAccount = $DBMail.Accounts |where{$_.name -eq $AccountName}
   #Delete existing profile
   if ($ChkProfile -ne $null)
   {
    Write-Host -ForegroundColor Red "DB Profile $ProfileName Already Exists and will be deleted"
    Try
    {
     $ChkProfile.Drop()
    } 
    Catch
    {
     Write-host -ForegroundColor Red "ERROR [DROP EXISTING PROFILE]:"$_.Exception.Message
     Continue
    }
    Write-Host -ForegroundColor Red "DB Profile $ProfileName Dropped"
   }
   #Delete existing account
   if ($ChkAccount -ne $null)
   {
    Write-Host -ForegroundColor Red "DB Account $AccountName Already Exists and will be deleted"
    Try
    {
     $ChkAccount.Drop()
    }
    Catch
    {
     Write-host -ForegroundColor Red "ERROR [DROP EXISTING ACCOUNT]:"$_.Exception.Message
     Continue
    }
   Write-Host -ForegroundColor Red "DB Account $AccountName Dropped"
   }
   #Create Mail Account
   $DBAccount = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailAccount -argumentlist $DBMail,$AccountName -ErrorAction Continue
   Try
   {
    $DBAccount.Description = "SQL Alert Email Account"
    $DBAccount.DisplayName = $AccountName
    $DBAccount.EmailAddress = $AccountName+"@yourcompany.com"
    $DBAccount.Create()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [CREATE ACCOUNT]:"$_.Exception.Message
    continue
   }
   Try
   {
    $DBAccount.MailServers.Item($SQLServer).Rename($smtpServer)
      $DBAccount.Alter()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [ADD SMTP TO ACCOUNT]:"$_.Exception.Message
    Continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Account $AccountName Created"
   #Create Mail Profile
   $DBProfile = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Mail.MailProfile -argumentlist $DBMail,$ProfileName -ErrorAction Stop
   Try
   {
    $DBProfile.Description = "SQL Alert Email Profile"
    $DBProfile.Create()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [CREATE PROFILE]:"$_.Exception.Message
    continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Profile $ProfileName Created"
   Try
   {
    $DBProfile.AddAccount($AccountName,0)
    $DBProfile.AddPrincipal("Public",$false)
    $DBProfile.Alter()
   }
   Catch
   {
    Write-host -ForegroundColor Red "ERROR [ADD ACCOUNT TO PROFILE]:"$_.Exception.Message
    Continue
   }
   Write-Host -ForegroundColor DarkGreen "DB Account [$AccountName] added to Profile [$ProfileName]"
  
  }
 }
}
else
{
Write-Host "Incorrect Paramenter, use -SQL to specify the SQL Server Name or use -FILE to specify the path of the file containing server list" -ForegroundColor Red
$uParameterHelp = "
# Script Help :-
#---------------
# Parameter 1 :- -SQL to run powershell for Single server
#      -File to give filename with list of servers
# Parameter 2 :- Server name(if -s is the parameter 1) or Filename with fully defined path(if -f is the parameter 1)
#
# Example1:- DBMail.ps1 -Mode SQL -Server servername
# Example2:- DBMail.ps1 -Mode File -Server filename with fully define path like c:\test.txt"
Write-Host $uParameterHelp -ForegroundColor Blue
}
Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

1 comment:

  1. This comment has been removed by the author.

    ReplyDelete