SELECT [BlogName], [Description]
FROM [DBA].[Blogs]
WHERE [BlogURL] = 'sqlideas.com';

BlogName             Description						
-------------------- --------------------------------
The SQL Ideas        Towards the innovative SQL ideas
(1 row(s) affected)

Email Notification for DBA Monitoring and Alerts - Powershell

Last time I wrote about Powershell script to collect the SQL data collector and WMI data collector. Today I would like share one more Powershell script to send an email alerts for collected data statistics and some other counter queries.

1. Create a DBARepository database and following tables there

CREATE DATABASE [DBARepository]
GO
 
USE [DBARepository]
GO

CREATE TABLE [dbo].[SQLServerInstances](
 [InstanceID] [smallint] IDENTITY(1,1) NOT NULL,
 [DBName] [varchar](100) NULL,
 [IsActive] [bit] NULL,
 [UserName] [nvarchar](200) NULL,
 [Password] [nvarchar](200) NULL,
 [ServerName]  [varchar](100) NULL,
 [IsDataCollector] [bit] NOT NULL DEFAULT ((0)),
 [IsEmailAlert] [bit] NOT NULL DEFAULT ((0)),
 [IsIndexBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsSQLObjectBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsDatabaseObjectBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsSchemaBackup] [bit] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[EmailAlerts](
 [AlertID] [smallint] IDENTITY(1,1) NOT NULL,
 [AlertName] [varchar](100) NULL,
 [AlertScript] [varchar](8000) NULL,
 [InstanceIDs] [varchar](20) NULL,
 [EmailTo] [varchar](200) NULL,
 [EmailCC] [varchar](200) NULL,
 [EmailBCC] [varchar](200) NULL,
 [IsActive] [bit] NULL,
 [Schedule] [varchar](30) NULL,
 [Occurrence] [smallint] NULL,
 [StartDate] [smalldatetime] NULL,
 [LastRun] [smalldatetime] NULL,
 [NextRun] [smalldatetime] NULL,
 [FromEmail] [varchar](50) NULL,
 [smtp] [varchar](30) NULL,
 [EndDate] [datetime] NULL,
 [IsWeekendAlert] [bit] NOT NULL DEFAULT ((0)),
 [Note] [varchar](4000) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DBAErrorLog](
 [CounterName] [varchar](200) NULL,
 [ServerName] [varchar](100) NULL,
 [ErrorMessage] [varchar](4000) NULL,
 [ErrorDate] [datetime] NULL,
 [IsSolved] [bit] NULL
) ON [PRIMARY]
 
GO
CREATE FUNCTION [dbo].[GetNextScheduledRunDate] 
(
    @StartDateTime     DATETIME
    ,@PreNextRunDateTime DATETIME
    ,@FrequencyType     VARCHAR(10) -- YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND
    ,@FrequencyInterval  INT
 ,@IsWeekendAlert     BIT
)
RETURNS DATETIME
AS
BEGIN

    DECLARE @StartDateTime_   DATETIME    = @StartDateTime
    DECLARE @NextRunDateTime_   DATETIME    = @PreNextRunDateTime
    DECLARE @FrequencyType_   VARCHAR(10) = @FrequencyType
    DECLARE @FrequencyInterval_ INT     = @FrequencyInterval
    DECLARE @CurrentDate_   DATETIME    = GETDATE()
    DECLARE @TempRunDateTime_   DATETIME

    SET @NextRunDateTime_ = ISNULL(@NextRunDateTime_,@StartDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_

    IF @FrequencyType_ = 'YEAR'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(YEAR, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END
    
    IF @FrequencyType_ = 'MONTH'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(MONTH, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'WEEK'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(WEEK, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'DAY'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(DAY, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'HOUR'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(HOUR, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'MINUTE'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(MINUTE, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'SECOND'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(SECOND, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END
 
 
 IF (@IsWeekendAlert = 0 AND @FrequencyType_ IN ('HOUR','MINUTE','SECOND'))
 BEGIN
  IF (DATENAME(WEEKDAY,@NextRunDateTime_) = 'Saturday')
  BEGIN
   SET @NextRunDateTime_ = DATEADD(DAY,2,@NextRunDateTime_)
  END
  ELSE IF (DATENAME(WEEKDAY,@NextRunDateTime_) = 'Sunday') 
  BEGIN
   SET @NextRunDateTime_ = DATEADD(DAY,1,@NextRunDateTime_)
  END
 END
 
    RETURN @NextRunDateTime_
END


GO
[dbo].[EmailAlerts] - Includes the queries with particular schedule which run against specified servers and send data in table format to specified emails, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug

2. Create following PS1 file and schedule in SQL Server Agent or Windows Task
<#
File : EmailAlerts.ps1
Created By : Paresh Prajapati
Created date : 01/09/2015
Purpose : Send an email notification for queries data
#>

Try
{
$Counter = 'EmailAlerts - '
$DCInstance = '.'
$DCDatabase = 'dbarepository'
$DCQuery = "SELECT AlertName, AlertScript,InstanceIDs,EmailTo,EmailCC,Schedule,Occurrence,smtp,FromEmail,Note FROM [dbo].[EmailAlerts] WHERE IsActive = 1 AND InstanceIDs IS NOT NULL AND ISNULL(NextRun,0) <= GETDATE() AND (GETDATE() BETWEEN StartDate AND EndDate)"
$dccn=new-object System.Data.SqlClient.SQLConnection
#$dcconstring = "Server=$DCInstance;Database=$DCDatabase;user id=$DCUserName;password=$DCPassword" 
$dcconstring = "Server=$DCInstance;Database=$DCDatabase;Integrated Security=sspi" 

#$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Username $DCUserName  -Password $DCPassword -Query $DCQuery
$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $DCQuery

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$dccn.ConnectionString=$dcconstring 
$dccn.Open()

if ($AlertScripts -ne $null)
{
 foreach ($AlertScript in $AlertScripts) 
 {
  Try
  {
    $DataSet = New-Object System.Data.DataSet
    $Result = New-Object System.Data.DataTable

    $InstanceIDs = $AlertScript.InstanceIDs.ToString()
    $AlertName = $AlertScript.AlertName.ToString()
    $Script = $AlertScript.AlertScript.ToString()
    $EmailTo = $AlertScript.EmailTo.ToString()
    $emailCC = $AlertScript.EmailCC.ToString()
    $Schedule = $AlertScript.Schedule.ToString()
    $Occurrence = $AlertScript.Occurrence.ToString()
    $smtp = $AlertScript.smtp.ToString()
    $FromEmail = $AlertScript.FromEmail.ToString()
    $Note = $AlertScript.Note.ToString()
    
        
    IF ($InstanceIDs -ne $null -and $InstanceIDs -ne "")
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] > 0 AND [InstanceID] IN (" +  $InstanceIDs + ") AND IsEmailAlert = 1"
    }

    $Instances = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $Query
    
            
    foreach ($Instance in $Instances) 
    {
     Try
     {   
        $Server = $Instance.ServerName.ToString()
        $DBName = $Instance.DBName.ToString()
        $UserName = $Instance.UserName.ToString()
        $Password = $Instance.Password.ToString()
        
        
        $SqlConnection.ConnectionString = “Server = $Server; Database = $DBName;Integrated Security=sspi" 
        $SqlConnection.Open()
        $SqlCmd.CommandText = $Script
        $SqlCmd.Connection = $SqlConnection

        
            $SqlAdapter.SelectCommand = $SqlCmd
            $SqlAdapter.Fill($DataSet)
            
         
            if($Result -eq $null)
            {
                $Result =  $DataSet.Tables[0].Clone()
                $Result.Clear()
            }

            $Result.Merge($DataSet.Tables[0])
            $DataSet.Tables[0].Clear()


        $SqlConnection.Close()
        #$SqlConnection.Dispose()
      }
     Catch
     {
          #  Write-Host "Error Occured - Instance"

           if ($SqlConnection.State -eq "Open")
           {
           $SqlConnection.Close()
           }

           $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
           $ErrorMessage = $ErrorMessage.replace("'","''")
           $CounterName = $Counter + $AlertName
           $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
           $ErrorLogSQL = $dccn.CreateCommand()
           $ErrorLogSQL.CommandText = $ErrorLogScript
           $ErrorLogSQL.ExecuteNonQuery()

       }
                       
     }
    
    $Style = "
     
    "
    
   
    
    If ($Result.Columns.ColumnName -contains "Alert")
    {
         $IsAlertSet  = 1
    }
    else
    {
        $IsAlertSet  = 0
    }
    
    $PreContent = ""+$AlertName+""+"    "+"Run on: $(Get-Date)
" if($Note -ne $null -and $Note -ne "") { $PostContect = "Note:"+" "+""+$Note+"
" } else { $PostContect = " " } #$results = $DataSet.Tables[0] | format-table -autosize | out-string $results = $Result | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray| ConvertTo-Html -head $Style -PreContent $PreContent -PostContent $PostContect | out-string If ($IsAlertSet -eq 1) { $results = $results.Replace("High
","") $results = $results.Replace("Medium
","") $results = $results.Replace("Low
","") $results = $results.Replace("Alert
","") } if($Result -ne $null -and $Result.Rows.Count -gt 0) { $message = new-object System.Net.Mail.MailMessage $message.From = $FromEmail $message.To.Add($emailTo) $message.ReplyTo = "prajapatipareshm@gmail.com" if ($results -like '*Class="High"*' -and $emailCC -ne $null -and $emailCC -ne "") { $message.CC.Add($emailCC) } #$message.Bcc.Add($bccaddress) $message.IsBodyHtml = $True $message.Subject = $AlertName $message.body = $results $smtpServer = $smtp $smtp = new-object Net.Mail.SmtpClient($smtpServer) $smtp.Send($message) #$message.dispose() $Result.Clear() $Result.Dispose() $Result = $null $results = $null } $NextRunUpdateScript = "UPDATE [dbo].[EmailAlerts] SET [LastRun] = GETDATE(), [NextRun] = dbo.[GetNextScheduledRunDate] (StartDate,NextRun,Schedule,Occurrence,IsWeekendAlert) WHERE AlertName = "+ "'" + $AlertName + "'" $NextRunSQL = $dccn.CreateCommand() $NextRunSQL.CommandText = $NextRunUpdateScript $NextRunSQL.ExecuteNonQuery() } Catch { # Write-Host "Error Occured - Script" $ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message $ErrorMessage = $ErrorMessage.replace("'","''") $CounterName = $Counter + $AlertName $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"', GETDATE()" $ErrorLogSQL = $dccn.CreateCommand() $ErrorLogSQL.CommandText = $ErrorLogScript $ErrorLogSQL.ExecuteNonQuery() $NextRunUpdateScript = "UPDATE [dbo].[EmailAlerts] SET [LastRun] = GETDATE(), [NextRun] = dbo.[GetNextScheduledRunDate] (StartDate,NextRun,Schedule,Occurrence,IsWeekendAlert) WHERE AlertName = "+ "'" + $AlertName + "'" $NextRunSQL = $dccn.CreateCommand() $NextRunSQL.CommandText = $NextRunUpdateScript $NextRunSQL.ExecuteNonQuery() } } } } Catch { # Write-Host "Error Occured - Main" $ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message $ErrorMessage = $ErrorMessage.replace("'","''") $CounterName = $Counter + $AlertName $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"', GETDATE()" $ErrorLogSQL = $dccn.CreateCommand() $ErrorLogSQL.CommandText = $ErrorLogScript $ErrorLogSQL.ExecuteNonQuery() } Finally { $dccn.Close() }
You can find above script here too.  

3. Sample notification queries
Download sample email alert queries here.
  • [AlertName]: Name of Alert [AlertScript]: Queries
  • [InstanceIDs]: Comma separated InstancesIds where script will be executed against 
  • [EmailTo]: Query output which send to 
  • [EmailCC]: Query output which send in CC 
  • [EmailBCC]: Query output which send in BCC 
  • [FromEmail]: Email notification come from 
  • [IsActive]: Flag to disable/enable the counter 
  • [Schedule]: When script will run, It has possible values are Year, Month, Week, Day, Hour, Minute, Second 
  • [Occurrence] : When it will occur or frequency 
  • [StartDate] : Start date and time of collector counter 
  • [LastRun] : When it last ran, It will update by PS1 script 
  • [NextRun]: Next schedule to run data collector, It will be calculated by PS1 script 
  • [EndDate]: counter execution end date 
  • [IsWeekendAlert]: Notification should be sent in weekend or not 
  • [Note]: Remarks which you want to put in notification 
  • [smtp]: smtp id for email
 Change Server and Instance Ids in the sample queries. You can add  more counters and schedule it which you want email notification for. I am still enahancing it and you can put your comment for the same if any.


Continue Reading

WMI Data Collector for DBA Monitoring and Alerts - Powershell

Sometime before i shared a script to collect data for SQL Server and databases using a power-shell script. Now i am sharing a same but it is for WMI data collectors instead of SQL data, which used for reporting, alert and monitoring purpose for all the Servers. Let me share a script here.

How to use?
1. Create a DBARepository database and following tables there.
CREATE DATABASE [DBARepository]
GO

USE [DBARepository]
GO

CREATE TABLE [dbo].[WMIServers](
 [InstanceID] [smallint] IDENTITY(1,1) NOT NULL,
 [ServerName] [varchar](100) NULL,
 [IsActive] [bit] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[WMICollectors](
 [WMIID] [smallint] IDENTITY(1,1) NOT NULL,
 [WMIName] [varchar](100) NULL,
 [WMIItems] [varchar](4000) NULL,
 [PostExecScript] [varchar](4000) NULL,
 [IsActive] [bit] NULL,
 [Schedule] [varchar](30) NULL,
 [Occurrence] [smallint] NULL,
 [StartDate] [datetime] NULL,
 [LastRun] [datetime] NULL,
 [NextRun] [datetime] NULL,
 [InstanceIDs] [varchar](20) NULL,
 [WMITable] [varchar](50) NULL,
 [EmailTo] [varchar](100) NULL,
 [WMIScript] [varchar](4000) NULL,
 [smtp] [varchar](30) NULL,
 [FromEmail] [varchar](50) NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[DBAErrorLog](
 [CounterName] [varchar](200) NULL,
 [ServerName] [varchar](100) NULL,
 [ErrorMessage] [varchar](4000) NULL,
 [ErrorDate] [datetime] NULL,
 [IsSolved] [bit] NULL
) ON [PRIMARY]

GO

[dbo].[WMIServers] - Includes Servers for which you want collect the wmi data
[dbo].[WMICollectors] - Includes the queries with particular schedule which collect the wmi data from specified servers, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug

2. Create following PS1 file and schedule in SQL Server Agent or Windows Task.
 
<#
File : WMICollector.ps1
Created By : Paresh Prajapati
Created date : 01/09/2015
Purpose : Collect a WMI data of specified servers using WMI queries
#>

Try
{
$Counter = 'WMICollectors - '
$DCInstance = '.'
$DCDatabase = 'dbarepository'
$DCQuery = "SELECT [WMIName],[WMIScript],[WMIItems],[InstanceIDs],[Schedule],[Occurrence],[WMITable],[EmailTo],[smtp],[FromEmail],[PostExecScript] FROM [WMICollectors] 

WHERE IsActive = 1 AND InstanceIDs IS NOT NULL AND StartDate <= GETDATE() AND ISNULL(NextRun,0) <= GETDATE()"
$dccn=new-object System.Data.SqlClient.SQLConnection
#$dcconstring = "Server=$DCInstance;Database=$DCDatabase;user id=$DCUserName;password=$DCPassword" 
$dcconstring = "Server=$DCInstance;Database=$DCDatabase;Integrated Security=sspi" 

#$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Username $DCUserName  -Password $DCPassword -Query $DCQuery
$WMIScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $DCQuery

$dccn.ConnectionString=$dcconstring 
$dccn.Open()

if ($WMIScripts.Count -ne 0)
{
foreach ($WMIScript in $WMIScripts) 
{
 Try
 {   
    $dt = new-object System.Data.DataTable
    $Result = New-Object System.Data.DataTable
    
    $Columns = ""
    $Column = ""
    $results = ""
    
    $InstanceIDs = $WMIScript.InstanceIDs.ToString()
    $WMIName = $WMIScript.WMIName.ToString()
    $Script = $WMIScript.WMIscript
    $WMIItems = $WMIScript.WMIItems
    $EmailTo = $WMIScript.EmailTo.ToString()
    $Schedule = $WMIScript.Schedule.ToString()
    $Occurrence = $WMIScript.Occurrence.ToString()
    $WMITable = $WMIScript.WMITable.ToString()
    $smtp = $WMIScript.smtp.ToString()
    $FromEmail = $WMIScript.FromEmail.ToString()
    $PostExecScript = $WMIScript.PostExecScript.ToString()

    IF ($InstanceIDs -eq "-1")
    {
        $Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] = -1"
    }
    ELSEIF ($InstanceIDs -eq "0")
    {
        $Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] > 0"
    }
    ELSE 
    {
        $Query = "SELECT [ServerName] FROM [WMIServers] WHERE [IsActive] = 1 AND [InstanceID] > 0 AND [InstanceID] IN (" +  $InstanceIDs + ")"
    }

        
    $Instances = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $Query

    $Columns = $WMIItems
    
    # Creating  a data table : Start 
    # Adding a ServerName column to data table    

    $Columns = "ServerName" + "," + $Columns

    $Column = $WMIItems.split(',')

    if ($emailTo -ne $null -and $emailTo -ne "")
     {
        $dt.columns.add("ServerName") | Out-Null

        for($j=0;$j -lt $Column.Count; $j++)
        {
                $Col = $Column[$j]
                $dt.columns.add($Col) | Out-Null
                
        }
    }
    
    # Creating  a data table : end   
    
    foreach ($Instance in $Instances) 
    {
     Try
     {              
        $Server = $Instance.ServerName
        $WmiQuery = $Script
        $Expression = $WMIScript.WMIItems
        $Values = ""

        $wi  = Get-WmiObject -ComputerName $Server -query $WmiQuery   # | Format-Table -Auto # $Expression 
         
     # Filling a SQL table from array : Start

     if ($WMITable -ne $null -and $WMITable -ne "")
     {
             
        for($i=0;$i -lt $wi.Count; $i++)
        {
            
            $Values  = ""
            
            for($j=0;$j -lt $Column.Count; $j++)
            {
                $Col = $Column[$j]
                $Values =  $Values + "'" + $($wi.Item($i)).$Col + "'" + ","
                
            }
            
            $Values = $Values.substring(0,$Values.Length-1)
            
            $Values = "'" + $Server + "'" + "," + $Values

            $WmiCommand ="INSERT into "+$WMITable+" ("+$Columns+") VALUES ("+$Values+")"
            $WQL = $dccn.CreateCommand()
            $WQL.CommandText = $WmiCommand
            $WQL.ExecuteNonQuery()| out-null

         } 
       
       }

      # Filling a SQL table from array : End

      # Filling a data table from array : Start
     
     if ($emailTo -ne $null -and $emailTo -ne "")
     {
        
        for($i=0;$i -lt $wi.Count; $i++)
        {
            
            $row = $dt.NewRow()

            $row.ServerName = $Server
            

            for($j=0;$j -lt $Column.Count; $j++)
            {
                $Col = $Column[$j]
                $row.$Col = $($wi.Item($i)).$Col
                
            }
            
            $dt.rows.add($row)  | Out-Null
           
           
        } 

        if($Result -eq $null)
         {
            
            $Result =  $dt.Clone()
            $Result.Clear()
          }
          
          $Result.Merge($dt)
          $dt.Clear()
                    
       }             
        # Filling a data table from array : End

    }
     Catch
    {
       # Write-Host "Error Occured - Instance"
   
       $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
       $ErrorMessage = $ErrorMessage.replace("'","''")
       $CounterName = $Counter + $WMIName
       $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', 

GETDATE()"
       $ErrorLogSQL = $dccn.CreateCommand()
       $ErrorLogSQL.CommandText = $ErrorLogScript
       $ErrorLogSQL.ExecuteNonQuery()

    }
    }
    
    if ($emailTo -ne $null -and $emailTo -ne "")
     {

        $results = $result | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray| ConvertTo-Html | out-string 
        $results = $results.Replace("","
") if($Result -ne $null -and $Result.Rows.Count -gt 0) { $emailFrom = $FromEmail $subject = $WMIName $emailbody = $results $message = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $emailbody) $message.IsBodyHTML = $true $smtpServer = $smtp $smtp = New-Object Net.Mail.SmtpClient($smtpServer) $smtp.Send($message) } } $Result.Clear() $Result.Dispose() $results = $null $dt.clear() $dt.Dispose() $Columns = "" $Column = "" IF ($PostExecScript -ne $null -and $PostExecScript -ne "") { $PostDCSQL = $dccn.CreateCommand() $PostDCSQL.CommandText = $PostExecScript $PostDCSQL.ExecuteNonQuery() } $NextRunUpdateScript = "UPDATE [dbo].[WMICollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate])) WHERE WMIName = "+ "'" + $WMIName + "'" $NextRunSQL = $dccn.CreateCommand() $NextRunSQL.CommandText = $NextRunUpdateScript $NextRunSQL.ExecuteNonQuery() } Catch { # Write-Host "Error Occured - Script" $ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message $ErrorMessage = $ErrorMessage.replace("'","''") $CounterName = $Counter + $WMIName $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"', GETDATE()" $ErrorLogSQL = $dccn.CreateCommand() $ErrorLogSQL.CommandText = $ErrorLogScript $ErrorLogSQL.ExecuteNonQuery() $NextRunUpdateScript = "UPDATE [dbo].[WMICollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate])) WHERE WMIName = "+ "'" + $WMIName + "'" $NextRunSQL = $dccn.CreateCommand() $NextRunSQL.CommandText = $NextRunUpdateScript $NextRunSQL.ExecuteNonQuery() } } } } Catch { # Write-Host "Error Occured - Main" $ErrorMessage = "Line: " + $_.InvocationInfo.ScriptLineNumber + " - Message: " + $_.Exception.Message $ErrorMessage = $ErrorMessage.replace("'","''") $CounterName = $Counter + $WMIName $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName, ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"', '"+$ErrorMessage+"', GETDATE()" $ErrorLogSQL = $dccn.CreateCommand() $ErrorLogSQL.CommandText = $ErrorLogScript $ErrorLogSQL.ExecuteNonQuery() } Finally { $dccn.Close() }
3. Sample collector tables and collector queries.
-- Master Records of servers and databases
SET IDENTITY_INSERT WMIServers ON

INSERT INTO [dbo].[WMIServers]
(
 [InstanceID],
 [ServerName],
 [IsActive]

)
SELECT 1,'Server1', 1
UNION
SELECT 2,'Server2', 1

SET IDENTITY_INSERT WMIServers OFF

GO

-- Wmi Collector sample table
-- Add ServerName always first to get server name 
CREATE TABLE [dbo].[DiskInfo](
 [ServerName] [varchar](50) NULL,
 [Name] [varchar](50) NULL,
 [VolumeName] [varchar](50) NULL,
 [Compressed] [varchar](10) NULL,
 [DriveType] [varchar](50) NULL,
 [FreeSpace] [bigint] NULL
) ON [PRIMARY]

GO

-- Collector sample records
INSERT INTO [dbo].[WMICollectors]
(
  [WMIName] ,
  [WMIScript],
  [WMIItems],
  [PostExecScript],
  [IsActive],
  [Schedule],
  [Occurrence],
  [StartDate],
  [LastRun],
  [NextRun],
  [InstanceIDs],
  [WMITable],
  [EmailTo],
  [smtp],
  [FromEmail]
)
SELECT 
'Disk Info',
'SELECT * FROM Win32_LogicalDisk',
'Name,VolumeName,Compressed,DriveType,FreeSpace',
NULL,
1,
'day',
1,
GETDATE(),
NULL,
NULL,
'1,2',
'DiskInfo',
'prajapatipareshm@gmail.com',
'sampleSmtp.com'
'prajapatipareshm@gmail.com'

GO

Note: WMI Queries Items and WMI tables must have same number of columns with addition of Server Name

  •  [WMIName]: Name of WMI data collector
  •  [WMIScript] : WMI Query
  •  [WMIItems] : Name of WMI query columns or items which you want get
  •  [PostExecScript] : Additional script which can be performed on collected data
  •  [IsActive]: Flag to data collector script to run or not
  •  [Schedule]: When script will run, It has possible values are Year, Month, Week, Day, Hour, Minute, Second
  •  [Occurrence] : When it will occur or frequency
  •  [StartDate] : Start date and time of collector counter
  •  [LastRun] : When it last ran, It will update by PS1 script
  •  [NextRun]: Next schedule to run data collector, It will be calculated by PS1 script
  •  [InstanceIDs]: Comma separated InstancesIds where script will be executed to collect data
  •  [smtp]: smtp address to send an email
  •  [EmailTo]: To receive an email for WMI data
  •  [FromEmail]: From Email, an email will send
You can add more WMI collector tables and queries with particular schedule which you want to capture. This script I have created for need for monitoring,alert and reporting. Still, I am enhancing it, you can also share your ideas for its enhancement. Enjoy Data Collector!
Continue Reading

Import Excel data using SSIS packages - SQL Server

I was working on data import using different different methods and one of the method i want to share here. This method is SSIS package to import data from Excel file.

I have created a SSIS package and captured screen shots for the steps, you can find below.
















I will share more methods to data import in SQL Server database. Enjoy data importing!
Continue Reading

Use statement is not supported to switch between databases, Use a new connection to connect to different database - SQL Azure Error

A long ago, when I was worked with SQL Azure database, I received below error.

Use statement is not supported to switch between databases. Use a new connection to connect to different database.

It is due to, I was directly trying to make a different database connection using USE statement in query analyzer where MASTER database connection already made. See below screen shot of error.


After I changed a database connection as below and it was succeeded.



Let's share experience of SQL Azure.
Continue Reading

SQL Server Data Collector for DBA Monitoring and Reporting - Powershell

Recently I have worked with Powershell script to collect required data from specified databases and specified servers, which used for reporting and monitoring purpose in SQL Server. Let me share a script here.

How to use?
1. Create a DBARepository database and following tables there.
CREATE DATABASE [DBARepository]
GO

USE [DBARepository]
GO

CREATE TABLE [dbo].[SQLServerInstances](
 [InstanceID] [smallint] IDENTITY(1,1) NOT NULL,
 [ServerName] [varchar](100) NULL,
 [DBName] [varchar](100) NULL,
 [IsActive] [bit] NULL,
 [IsActive] [nvarchar](200) NULL,
 [Password] [nvarchar](200) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DataCollectors](
 [DCID] [smallint] IDENTITY(1,1) NOT NULL,
 [DCName] [varchar](100) NULL,
 [DCScript] [varchar](4000) NULL,
 [PostExecScript] [varchar](4000) NULL,
 [IsActive] [bit] NULL,
 [Schedule] [varchar](30) NULL,
 [Occurrence] [smallint] NULL,
 [StartDate] [smalldatetime] NULL,
 [LastRun] [smalldatetime] NULL,
 [NextRun] [smalldatetime] NULL,
 [InstanceIDs] [varchar](20) NULL,
 [DCTable] [varchar](50) NULL
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[DBAErrorLog](
 [CounterName] [varchar](200) NULL,
 [ServerName] [varchar](100) NULL,
 [ErrorMessage] [varchar](4000) NULL,
 [ErrorDate] [datetime] NULL,
 [IsSolved] [bit] NULL
) ON [PRIMARY]

GO

[dbo].[SQLServerInstances] - Includes Servers and databases for which you want collect the data
[dbo].[DataCollectors] - Includes the queries with particular schedule which collect the data from specified servers and databases, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug

2. Create following PS1 file and schedule in SQL Server Agent or Windows Task.
 
<#
File : DataCollector.ps1
Created By : Paresh Prajapati
Created date : 10/10/2015
Purpose : Collect a data from specified databases of specified servers using sql queries
#>

Try
{

$Counter = 'DataCollectors - '
$DCInstance = '.'
$DCDatabase = 'dbarepository'
$DCQuery = "SELECT DCName, DCScript,PostExecScript,InstanceIDs,DCTable,Schedule,Occurrence FROM [dbo].[DataCollectors] WHERE IsActive = 1 AND InstanceIDs IS NOT NULL AND StartDate <= GETDATE() AND ISNULL(NextRun,0) <= GETDATE()"
$dccn=new-object System.Data.SqlClient.SQLConnection
#$dcconstring = "server=$DCInstance;database=$DCDatabase;user id=uuu;password=ppp"
$dcconstring = "Server=$DCInstance;Database=$DCDatabase;Integrated Security=sspi" 

#$DCScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Username uuu  -Password ppp -Query $DCQuery
$DCScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $DCQuery 
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $dcconstring

$dccn.ConnectionString=$dcconstring 
$dccn.Open()

if ($DCScripts -ne $null)
{

 foreach ($DCScript in $DCScripts) 
 {
  Try
  { 
    
    $dt = new-object "System.Data.DataTable"
    
    $InstanceIDs = $DCScript.InstanceIDs.ToString()
    $DCName = $DCScript.DCName.ToString()
    $Script = $DCScript.DCScript.ToString()
    $PostExecScript = $DCScript.PostExecScript.ToString()
    $DCTable = $DCScript.DCTable.ToString()
    $Schedule = $DCScript.Schedule.ToString()
    $Occurrence = $DCScript.Occurrence.ToString()
    
    
    IF ($InstanceIDs -eq "-1")
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] = -1"
    }
    ELSEIF ( $InstanceIDs -eq "0")
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] > 0"
    }
    ELSE 
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] > 0 AND [InstanceID] IN (" +  $InstanceIDs + ")"
    }

    $Instances = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $Query

    foreach ($Instance in $Instances) 
    {
     Try
     {   
        $Server = $Instance.ServerName.ToString()
        $DBName = $Instance.DBName.ToString()
        $UserName = $Instance.UserName.ToString()
        $Password = $Instance.Password.ToString()


        $cn = new-object System.Data.SqlClient.SqlConnection "server=$Server;database=$DBName;Integrated Security=sspi"
        $cn.Open()
        $DCSql = $cn.CreateCommand()
        $DCSql.CommandText = $Script
        $rdr = $DCSql.ExecuteReader()
        $dt.Load($rdr)
        $rdr.Close()
        $rdr.Dispose()
        $cn.close()
     }
     Catch
     {
      # Write-Host "Error Occured - Instance"

        if ($cn.State -eq "Open")
        {
           $cn.close()
        }

       $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
       $ErrorMessage = $ErrorMessage.replace("'","''")
       $CounterName = $Counter + $DCName
       $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
       $ErrorLogSQL = $dccn.CreateCommand()
       $ErrorLogSQL.CommandText = $ErrorLogScript
       $ErrorLogSQL.ExecuteNonQuery()

     }
    }
 
   $bulkCopy.DestinationTableName = $DCTable
   $bulkCopy.BatchSize = 100
   $bulkCopy.BulkCopyTimeout = 100
   $bulkCopy.WriteToServer($dt)
   $dt.clear()
   $dt.Dispose()
   

   IF ($PostExecScript -ne $null -and $PostExecScript -ne "")
   {
    $PostDCSQL = $dccn.CreateCommand()
    $PostDCSQL.CommandText = $PostExecScript
    $PostDCSQL.ExecuteNonQuery()
   }

   $NextRunUpdateScript = "UPDATE [dbo].[DataCollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate])) WHERE DCName = "+ "'" + $DCName + "'"
   $NextRunSQL = $dccn.CreateCommand()
   $NextRunSQL.CommandText = $NextRunUpdateScript
   $NextRunSQL.ExecuteNonQuery()
  }
  Catch
  {
      # Write-Host "Error Occured - Script"

       $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
       $ErrorMessage = $ErrorMessage.replace("'","''")
       $CounterName = $Counter + $DCName
       $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
       $ErrorLogSQL = $dccn.CreateCommand()
       $ErrorLogSQL.CommandText = $ErrorLogScript
       $ErrorLogSQL.ExecuteNonQuery()

       $NextRunUpdateScript = "UPDATE [dbo].[DataCollectors] SET [LastRun] = GETDATE(), [NextRun] = DATEADD("+$Schedule+","+$Occurrence+",ISNULL([NextRun],[StartDate])) WHERE DCName = "+ "'" + $DCName + "'"
       $NextRunSQL = $dccn.CreateCommand()
       $NextRunSQL.CommandText = $NextRunUpdateScript
       $NextRunSQL.ExecuteNonQuery()
       
  }
 }
}
}
Catch
{
   # Write-Host "Error Occured - Main"

   $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
   $ErrorMessage = $ErrorMessage.replace("'","''")
   $CounterName = $Counter + $DCName
   $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
   $ErrorLogSQL = $dccn.CreateCommand()
   $ErrorLogSQL.CommandText = $ErrorLogScript
   $ErrorLogSQL.ExecuteNonQuery()

}
Finally
{

   $dccn.Close()
}

3. Sample collector tables and collector queries.
-- Master Records of servers and databases
SET IDENTITY_INSERT SQLServerInstances ON

INSERT INTO [dbo].[SQLServerInstances]
(
 [InstanceID],
 [ServerName],
 [DBName],
 [IsActive]

)
SELECT 1,'Server1', 'master', 1
UNION
SELECT 2,'Server1', 'DBARepository', 1
UNION
SELECT 3,'Server2', 'master', 1

SET IDENTITY_INSERT SQLServerInstances OFF

GO

-- Collector sample table
CREATE TABLE [dbo].[DatabaseStatistics](
 [ServerName] [varchar](50) NULL,
 [DBName] [varchar](50) NULL,
 [DBSize_MB] [decimal](18, 2) NULL,
 [CreatedDate] [date] NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TableStatistics](
 [ServerName] [varchar](30) NULL,
 [DBName] [varchar](50) NULL,
 [TableName] [varchar](100) NULL,
 [RowsTotal] [bigint] NULL,
 [TotalSpace_MB] [int] NULL,
 [UsedSpace_MB] [int] NULL,
 [UnusedSpace_MB] [int] NULL,
 [CreatedDate] [date] NULL
) ON [PRIMARY]

GO

-- Collector sample records
INSERT INTO [dbo].[DataCollectors]
(
 [DCName],
 [DCScript],
 [PostExecScript],
 [IsActive],
 [Schedule],
 [Occurrence],
 [StartDate],
 [LastRun],
 [NextRun],
 [InstanceIDs],
 [DCTable]
)
SELECT 
'Database Statistics',
'SELECT 
  @@Servername AS ServerName,
  db.[name] AS DBName, 
  Sum(( Cast(( ( ( Cast(af.[size] AS NUMERIC(18, 4)) * 8192 ) / 1024 ) / 
                    1024 ) AS
                        NUMERIC(18, 2)) )) AS FileSize_MB,
  GETDATE() AS CreatedDate
FROM   sys.sysdatabases db 
       INNER JOIN sys.sysaltfiles af 
               ON db.dbid = af.dbid 
GROUP  BY db.[name] ',
NULL,
1,
'day',
1,
GETDATE(),
NULL,
NULL,
'1,3',
'DatabaseStatistics'

UNION 

SELECT 
'Table Statistics',
'SELECT 
 @@Servername AS ServerName,
 DB_NAME() AS DatabaseName, 
 Schema_name(t.schema_id) + ''.'' + t.name        AS TableName, 
 p.rows                                         AS Rows, 
 (Sum(a.total_pages) * 8)/1024                        AS TotalSpace_MB, 
 (Sum(a.used_pages) * 8)/1024                          AS UsedSpace_MB, 
 ((Sum(a.total_pages) - Sum(a.used_pages)) * 8)/1024 AS UnusedSpace_MB ,
 GETDATE() AS CreatedDate
FROM   sys.tables t 
 INNER JOIN sys.indexes i 
   ON t.object_id = i.object_id 
 INNER JOIN sys.partitions p 
   ON i.object_id = p.object_id 
      AND i.index_id = p.index_id 
 INNER JOIN sys.allocation_units a 
   ON p.partition_id = a.container_id 
GROUP  BY Schema_name(t.schema_id) + ''.'' + t.name, 
   p.rows',
NULL,
1,
'hour',
12,
GETDATE(),
NULL,
NULL,
'2',
'TableStatistics'

GO

Note: Data collector script and table must have same number of columns

  •  [DCName]: Name of data collector
  •  [DCScript] : Data collector Script
  •  [PostExecScript] : Additional script which can be performed on collected data
  •  [IsActive]: Flag to data collector script to run or not
  •  [Schedule]: When script will run, It has possible values are Year, Month, Week, Day, Hour, Minute, Second
  •  [Occurrence] : When it will occur or frequency
  •  [StartDate] : Start date and time of collector counter
  •  [LastRun] : When it last ran, It will update by PS1 script
  •  [NextRun]: Next schedule to run data collector, It will be calculated by PS1 script
  •  [InstanceIDs]: Comma separated InstancesIds where script will be executed to collect data
  •  [DCTable]: Name of data collector table to collect output data of query
You can add more collector tables and queries with particular schedule which you want to capture. This script I have created for need for monitoring and reporting. Still, I am enhancing it, you can also share your ideas for its enhancement. Enjoy Data Collector!
Continue Reading