Monitoring one server is all well and good, but it would be nice to extend the monitoring suite to monitor any number of servers. One way of accomplishing this is to have a server table on the MGT server with columns for each monitoring process. A powershell script and a couple of stored procs can be written to add the target server to the table and create the necessary DBA database and stored procs on the target server. We also need a new powershell function which can handle SQL scripts with GO statements in them. The powershell script that collects data, and the Disk & Log stored procs & tables that we previously created need to be altered slightly to iterate through each server.

 

Amending the Disk & Log stored procs & tables

We're going to use a ServerID for each server we add. this ID will get generated using an IDENTITY column below. Before we do that we need to amend the stored procs and tables created previously on MGT server to return and store the ServerID. This server ID links the data collection tables to the server table we will create below.

 

Changes to Tables SQLMon_DiskDataFiles & SQLMon_DiskLogFiles

Add a column

 

[ServerID] [int] NOT NULL

 

Changes to Stored Procs spSQLMon_DiskDataFiles_SEL & spSQLMon_DiskLogFiles_SEL.

Add this parameter

 

@ServerID INT

 

Add this column to the temp tables #DataFiles & #LogFileSize in each stored proc:

 

[ServerID] INT NULL

 

After the Insert temp table statements add the statement:

 

For spSQLMon_DiskDataFiles_SEL:

 

UPDATE      #DataFiles

SET         ServerID = @ServerID

 

For spSQLMon_DiskLogFiles_SEL:

 

UPDATE      #LogFileSize

SET         ServerID = @ServerID

 

The Server Table

Create a table on the DBA database of the MGT server that will contain the servers and monitoring processes to run on a particular sever:

 

CREATE TABLE [dbo].[PSMon_Servers](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [ServerName] [varchar](50) NOT NULL,

[ServerDB] [varchar](50) NOT NULL,

      [SQLInstance] [varchar](10) NOT NULL,

      [SQLName] [varchar](50) NOT NULL,

      [DBFiles] [tinyint] NOT NULL

) ON [PRIMARY]

 

The DBFiles column is either 1 or 0. A default can be created on the column to either run or not for each server entered:

 

ALTER TABLE [dbo].[PSMon_Servers] ADD  CONSTRAINT [DF_PSMon_Servers_DiskUsage]  DEFAULT ((1)) FOR [DBFiles]

 

A Powershell Function to Handle GO Statements

Before we write the powershell script to create the database and objects, we will need a function to run scripts that have GO statements in them. This is a simple function but it isn’t well known. The important thing to note is the execution is at the server object level. Here is the function listing. Add it to your powershell profile:

 

function SQLGO {

 

param ($ServerName, $DB, $SQL)

   

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server=" + $ServerName + ";Database=" + $DB + ";Integrated Security=True"

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlConnection)

$SqlServer.ConnectionContext.ExecuteNonQuery($SQL) | out-null 

$SqlConnection.Close()      

}

The Powershell Script

We need to do a couple of things here. A database with the necessary select stored procs needs to be created on the target server. An entry then needs to be inserted into the PSMon_Servers table.

 

We need to write a SQL script to create the DBA database and put it in a folder where we can run it from the powershell script. We also need to add the select stored procs we wrote (and then amended) earlier (spSQLMon_DiskDataFiles_SEL & spSQLMon_DiskLogFiles_SEL). The powershell script will connect to the target server, and create the database & stored procs. Don’t forget the permissions on the stored procs!

 

Here is the script to create the DBA database. Default settings are fine. We also add the SQL agent login user. Don’t run it yet, the Powershell script will take care of that. This script needs to run first so I prefix the filename with ‘01’:

 

USE master

GO

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'DBA')

BEGIN

      CREATE DATABASE DBA

      ALTER AUTHORIZATION ON DATABASE::DBA to sa

      ALTER DATABASE DBA SET RECOVERY SIMPLE

END

GO

USE DBA

GO

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLAgentLogin')

CREATE LOGIN [SQLAgentLogin]FROM WINDOWS WITH DEFAULT_DATABASE=[DBA], DEFAULT_LANGUAGE=[us_english]

GO

USE [DBA]

GO

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N' SQLAgentLogin')

CREATE USER [SQLAgentLogin] FOR LOGIN [SQLAgentLogin] WITH DEFAULT_SCHEMA=[dbo]

GO

 

The powershell script takes a number of parameters:

 

$MGT:    management server

$SN:       target server name (without instance or port)

$SDB:     target server DB where monitoring objects are kept (DBA in this example)

$SQLI:    target SQL instance name

$SQLN:  target SQL name (name used to connect to SQL Server)

 

The script then iterates through the path where the database and stored proc create scripts are kept, and runs them on the target server.

 

Finally the script runs this stored proc to insert the server entry into the PSMon_Servers table:

 

CREATE PROCEDURE [dbo].[spPSMon_Server_INS]

      @ServerName       VARCHAR(50)

,     @ServerDB         VARCHAR(50)

,     @SQLInstance      VARCHAR(50)

,     @SQLName          VARCHAR(50)

AS

 

IF NOT EXISTS(SELECT * FROM PSMon_Servers

WHERE SQLName = @SQLName

                  AND   SQLName <> '')

BEGIN

      INSERT      PSMon_Servers(

                  ServerName

      ,           ServerDB

      ,           SQLInstance

      ,           SQLName)

      SELECT      @ServerName

      ,           @ServerDB

      ,           @SQLInstance

      ,           @SQLName

END

ELSE

BEGIN

      UPDATE      PSMon_Servers

      SET         ServerName        = @ServerName

      ,           ServerDB          = @ServerDB

      ,           SQLInstance       = @SQLInstance

      WHERE       SQLName           = @SQLName

END

 

The final Powershell script listing is here. Note we use the new function SQLGO to run the SQL script files:

 

param ($OutPath, $MGT, $SN, $SDB, $SQLI, $SQLN, $debug)

 

# create DBA database and monitor objects

$SQLPath = "<path containing SQL scripts>\*"

$DB = "master"

Get-Childitem $SQLPath -include *.sql | Sort-Object fullname | Foreach {

    $_.fullname

    $SQL = Get-Content $_.fullname | Out-String

    SQLGO $SQLN $DB $SQL | out-null

} 

 

# add server to PSMon_Servers table

$DB = "DBA"

$SQL =  "EXEC spPSMon_Server_INS '" + $SN + "', '" + $SDB + "', '" + $SQLI +  "', '" + $SQLN + "'"

$SQL

RunSQL $MGT $DB $SQL | out-null

 

This powershell script can be run whenever a server needs to be added to the list. New columns can be added to the server table as processes are created. The script can be rerun to update current servers in the list. Remember to add the select stored procs to the path containing the SQL Scripts that need to be run on the target server.

 

Ammending the Monitoring Powershell Script

The powershell script ‘PSMonDBFiles.ps1’ that we wrote previously needs to be amended. We need to add a loop to iterate through all servers in the PSMon_Servers table that have the DBFiles column set to 1.

 

The original listing for ‘PSMonDBFiles.ps1’ was:

 

Param($MGT, $MGTDB, $Target, $TargetDB)

 

$SQL = "EXEC spSQLMon_DiskDataFiles_SEL"

$DTout = SQLmon-rtn $Target $TargetDB $SQL

$Table = "SQLMon_DiskDataFiles"

SQLBulkINS $MGT $MGTDB $Table $DTout

 

$SQL = "EXEC spSQLMon_DiskLogFiles_SEL"

$DTout = SQLmon-rtn $Target $TargetDB $SQL

$Table = "SQLMon_DiskLogFiles"

SQLBulkINS $MGT $MGTDB $Table $DTout

 

We don’t need to specify the target or target database so we can remove these two parameters. We will need a stored proc to return all servers that are configured to run the DBFiles process:

 

CREATE PROC [dbo].[spPSMon_ServersGet]

      @Type VARCHAR(20)

AS

SET NOCOUNT ON

 

IF (@Type = 'DBFiles')

BEGIN

      SELECT      SQLName AS ServerFullName

      ,           ServerDB

      ,           id

      FROM        dbo.PSMon_Servers

      WHERE       DBFiles = 1

      ORDER BY    ServerName

END

 

We just need to call this stored Proc and iterate through the results.

 

The ‘PSMonDBFiles.ps1’ script can then be ammended thus:

 

param ($MGT,$MGTDB)

 

# get servers to monitor

$SQL = "EXEC spPSMon_ServersGet 'DBFiles'"

$DB = "DBA"

RunSQL $MGT $DB $SQL | Foreach-Object {

   

      $ServerID = $_.id

      $Target = $_.ServerFullName

$TargetDB = $_.ServerDB

 

 

$SQL = "EXEC spSQLMon_DiskDataFiles_SEL" + $ServerID

$DTout = SQLmon-rtn $Target $TargetDB $SQL

$Table = "SQLMon_DiskDataFiles"

SQLBulkINS $MGT $MGTDB $Table $DTout

 

$SQL = "EXEC spSQLMon_DiskLogFiles_SEL " + $ServerID

$DTout = SQLmon-rtn $Target $TargetDB $SQL

$Table = "SQLMon_DiskLogFiles"

}

 

And that should be it.

 

Conclusion

A bit complicated to set up initially, but I think its well worth it. Whenever you have a new server to monitor all you need to do is run the script with the appropriate parameters. Everything is set up. When you create a new report you just need to add the stored proc to the SQL script folder and add a column & default to the PSMon_Servers table & amend the spPSMon_ServersGet & stored proc.