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.
[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.
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' GONote: 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!
Comments
Post a Comment