Monitoring SQL Server with PowerShell Core Object Setup

问题

我们可以在SQL Server中监视许多内容，例如备份，SQL Agent Jobs，配置更改等。还有一些不同的工具可用于跟踪SQL Server附带的这些内容以及第三方工具。大多数方法的一个问题是，使用了几种不同的技术来监视SQL Server，因此在本技巧系列中，我们将着眼于为SQL Server创建PowerShell监视解决方案。这为我们提供了免费的监视选项，以及我们可以根据需要自定义的选项。

本系列的每个技巧都将解决数据集中式数据收集的不同方面，因此让我们开始吧。

解

我们将要组装在一起的第一个模块创建了将由解决方案的其他部分使用的核心组件。如前所述，我们将使用PowerShell收集数据并在集中式数据库服务器中创建数据库对象。

 

 

该PowerShell模块将创建其余模块所需的基础数据库对象。它创建数据库来存储数据，一些表并插入我们要监视的服务器列表。

数据库对象

在开始收集数据之前，将使用此第一个PowerShell脚本设置核心数据库组件。

将使用PowerShell脚本创建以下内容：

数据库

  • DBA-这是集中式收集数据库的名称，但是可以根据需要更改（请参阅settings.ini部分）
    • 注意：将使用模型数据库的设置来创建数据库

模式

  • 审核 -此架构将保存用于审核目的的所有数据库对象
  • 清单 -此架构将保存包含SQL Server实例列表的数据库对象
  • 监视 -此架构将保存用于收集数据的数据库对象

表

    • monitoring.ErrorLog - this captures any errors that are encountered
      • serverid - ties back to inventory.MasterServerList.serverid
      • script - this is the script that was running
      • message - this is the error message generated from the script
      • error_timestamp - this is the date and time of the error
    • inventory.MasterServerList - this stores a list of servers to monitor
      • serverid - system generated unique id
      • server_name - name of the server where SQL is installed
      • instance - name of the SQL Server instance
      • ip - ip address of the SQL Server instance
      • port - this is the port number for the SQL Server instance
      • is_active - 1 = active, 0 = inactive

Settings.ini

This file is used to configure the central server and database that will be used to store all of the collected data.

There are two parameters that you will need to update in this file:

  • centralServer - this is the server and instance name where you want to create the inventory database
  • inventoryDB - this is used to specify the name of the database that will get created and store the collected data. If the database already exists it will not create the database.

By default, when you run the PowerShell script it will try to connect to the server using a trusted connection, using the following information that is in the Settings.ini file.

 

 

[General]
centralServer=DESKTOP-QUDLLRR\SQL2
inventoryDB=DBA

If you want to use a SQL login then you will need to configure the Settings.ini file as follows and specify the SQL login and password to use to connect to the central server.

[General]
centralServer=DESKTOP-QUDLLRR\SQL2
inventoryDB=DBA
[Optional]
username=login
password=pwd

Instances.txt

The instances.txt file is used to load the data into the inventory.MasterServerList table. The format of the data in the text file is as follows:

  • ServerName, InstanceName, IPAddress, Port

Here is an example of two instances that we will monitor. Note that you need to use single quotes around each item and separate each item with commas.

  • The first row below is for server DESKTOP-QUDLLRR using the default instance of SQL Server, so we specify the default instance as MSSQLSERVERwith ip 10.0.0.35 and uses the default port 1433
  • The second row is also for server DESKTOP-QUDLLRR using a named instance of SQL2with ip 10.0.0.35, but uses port 50287
'DESKTOP-QUDLLRR','MSSQLSERVER','10.0.0.35',1433
'DESKTOP-QUDLLRR','SQL2','10.0.0.35',50287

This text file can be updated with new instance information and the PowerShell script can be run again or you can just add new rows directly to table inventory.MasterServerList with the necessary information.

PowerShell Script

The PowerShell script that creates the above objects and inserts data into the inventory.MasterServerList table is called:

  • Create-Master-Server-List.ps1

The script will use the Setting.ini file to determine what SQL Server instance to use for the central server and also the name of the database you want to use for the central data collection.

 

 

It will create the database, tables and load data from the Instances.txt file into inventoryMasterServerList.

How to Use

After you have entered records into the Instances.txt file for your SQL Server instances and configured the Settings.ini file you should put all three files in the same folder. In my setup, I put all of these files in the "C:\temp" folder.

  • Instances.txt
  • Settings.ini
  • Create-Master-Server-List.ps1

If you want to change the working directory and use something other then "C:\temp" you will need to edit the PowerShell script and modify the first line as shown below. This will be the case for all future modules too.

powershell script parameters

Navigate to the folder where you created the files and you can run the PowerShell script as follows:

Option 1

  • Right click on Create-Master-Server-List.ps1 and select Run with PowerShell

Option 2

  • Open a command window and navigate to the folder where you saved the above files and run
powershell "C:\temp\Create-Master-Server-List.ps1"

This doesn't need to be scheduled to run on a set basis, since this only creates core objects and inserts the instances to monitor which can be updated directly to table inventory.MasterServerList if there are changes or you want to add additional instances to monitor.

Check Creation of Database and Objects

After running the PowerShell script, we should two tables that are created. In this example, I used "DBA" as my central monitoring database and the two tables that get created are inventory.MasterServerList and monitoring.ErrorLog.

list of database objects

If we query the inventory.MasterServerList table, we should see the rows that have been inserted.  In my case, it inserted 2 rows as shown below.

list of instances to monitor

Checking for Errors

We can query the monitoring.ErrorLog table to find any errors with this module.

For example, if we run the PowerShell script again we should get an error because we are trying to load the same instances which should fail. We can see this below if we query the monitoring.ErrorLog table.

query monitoring errorlog table

 

script block

Get-Content "C:\temp\Settings.ini" | foreach-object -begin {$h=@{}} -process { $k = [regex]::split($_,'='); if(($k[0].CompareTo("") -ne 0) -and ($k[0].StartsWith("[") -ne $True)) { $h.Add($k[0], $k[1]) } }
$server        = $h.Get_Item("centralServer")
$inventoryDB   = $h.Get_Item("inventoryDB")
$usingCredentials = 0

if($server.length -eq 0){
    Write-Host "You must provide a value for the 'centralServer' in your Settings.ini file!!!" -BackgroundColor Red
    exit
}
if($inventoryDB.length -eq 0){
    Write-Host "You must provide a value for the 'inventoryDB' in your Settings.ini file!!!" -BackgroundColor Red
    exit
}

if($h.Get_Item("username").length -gt 0 -and $h.Get_Item("password").length -gt 0){
    $usingCredentials = 1
    $username         = $h.Get_Item("username")
    $password         = $h.Get_Item("password")
}

#Function to execute queries (depending on if the user will be using specific credentials or not)
function Execute-Query([string]$query,[string]$database,[string]$instance){
    if($usingCredentials -eq 1){
        Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -Username $username -Password $password -ErrorAction Stop
    }
    else{
        Invoke-Sqlcmd -Query $query -Database $database -ServerInstance $instance -ErrorAction Stop
    }
}

#Central Database creation/verification
$centralDBCreationQuery = "
IF DB_ID('$($inventoryDB)') IS NULL
CREATE DATABASE $($inventoryDB)
"
Execute-Query $centralDBCreationQuery "master" $server

###############################
#Schemas creation/verification#
###############################
$auditSchemaCreationQuery = "
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'audit')
EXEC('CREATE SCHEMA [audit] AUTHORIZATION [dbo]')
"
Execute-Query $auditSchemaCreationQuery $inventoryDB $server

$inventorySchemaCreationQuery = "
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'inventory')
EXEC('CREATE SCHEMA [inventory] AUTHORIZATION [dbo]')
"
Execute-Query $inventorySchemaCreationQuery $inventoryDB $server

$monitoringSchemaCreationQuery = "
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'monitoring')
EXEC('CREATE SCHEMA [monitoring] AUTHORIZATION [dbo]')
"
Execute-Query $monitoringSchemaCreationQuery $inventoryDB $server

###################################################################################################
#Create the main table where you will store the information about all the instance under your care#
###################################################################################################
$mslTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[inventory].[MasterServerList]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [inventory].[MasterServerList](
	[serverId]                  [int] IDENTITY(1,1) NOT NULL,
	[server_name]               [nvarchar](128) NOT NULL,
	[instance]                  [nvarchar](128) NOT NULL,
	[ip]                        [nvarchar](39) NOT NULL,
    [port]                      [int] NOT NULL DEFAULT 1433,
    [trusted]                   [bit] DEFAULT 1,
    [is_active]                 [bit] DEFAULT 1

CONSTRAINT PK_MasterServerList PRIMARY KEY CLUSTERED (serverId),

CONSTRAINT UQ_instance UNIQUE(server_name,instance)
) ON [PRIMARY]

END
"
Execute-Query $mslTableCreationQuery $inventoryDB $server

#######################################
#Error log table creation/verification#
#######################################
$errorLogTableCreationQuery = "
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[monitoring].[ErrorLog]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
CREATE TABLE [monitoring].[ErrorLog](
    [serverId]        [int]NOT NULL,
    [script]          [nvarchar](64) NOT NULL,
    [message]         [nvarchar](MAX) NOT NULL,
    [error_timestamp] [datetime] NOT NULL
    
    CONSTRAINT FK_ErrorLog_MasterServerList FOREIGN KEY (serverId) REFERENCES inventory.MasterServerList(serverId) ON DELETE NO ACTION ON UPDATE CASCADE
)ON [PRIMARY]
END
"
Execute-Query $errorLogTableCreationQuery $inventoryDB $server

#Logic to populate the Master Server List using a .txt file
$flag = 0
foreach($line in Get-Content .\instances.txt){
    $insertMSLQuery = "INSERT INTO inventory.MasterServerList(server_name,instance,ip,port) VALUES($($line))"
    
    try{
        Execute-Query $insertMSLQuery $inventoryDB $server
    }
    catch{
        $flag = 1
        [string]$message = $_
        $query = "INSERT INTO monitoring.ErrorLog VALUES((SELECT serverId FROM inventory.MasterServerList WHERE CASE instance WHEN 'MSSQLSERVER' THEN server_name ELSE CONCAT(server_name,'\',instance) END = '$($server)'),'Create-Master-Server-List','"+$message.replace("'","''")+"',GETDATE())"
        Execute-Query $query $inventoryDB $server
    }
}
if($flag -eq 1){Write-Host "Check the monitoring.ErrorLog table!"}

Write-Host "Done!"

　　

'DESKTOP-QUDLLRR','MSSQLSERVER','10.0.0.35',1433
'DESKTOP-QUDLLRR','SQL2','10.0.0.35',50287

　　

posted @ 2020-07-02 09:51  Javi  阅读(239)  评论(0)  编辑  收藏  举报