SQL Server Data Collector for DBA Monitoring and Reporting

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!

The SQL Ideas

Search This Blog

SQL Server Data Collector for DBA Monitoring and Reporting - Powershell

Labels

Comments

Post a Comment