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!
