Überwachen von SQL Server mit PowerShell Core Object Setup

Problem

In SQL Server können viele Dinge überwacht werden, z. B. Sicherungen, SQL Agent-Jobs, Konfigurationsänderungen usw. Es gibt verschiedene Tools, die mit SQL Server mitgeliefert werden, sowie Tools von Drittanbietern, mit denen diese Dinge nachverfolgt werden können. Das einzige Problem bei den meisten Ansätzen besteht darin, dass zur Überwachung von SQL Server verschiedene Techniken verwendet werden. Wir werden uns daher mit der Erstellung einer PowerShell-Überwachungslösung für SQL Server befassen. Dies gibt uns eine kostenlose Überwachungsoption sowie eine Option, die wir nach Bedarf anpassen können.

Lösung

Dem folgenden Modul, das wir zusammenstellen werden, werden die Kernkomponenten erstellt, die von anderen Teilen der Lösung verwendet werden. Wie bereits erwähnt, verwenden wir PowerShell, um die Daten zu erfassen und die Datenbankobjekte auf einem zentralen Datenbankserver zu erstellen.

Dieses PowerShell-Modul erstellt die Basisdatenbankobjekte, die für den Rest der Module benötigt werden. Es erstellt die Datenbank zum Speichern der Daten, einige Tabellen und fügt die Liste der Server ein, die wir überwachen möchten.

Datenbankobjekte

Bevor wir mit der Datenerfassung beginnen, werden die Kerndatenbankkomponenten mit diesem ersten PowerShell-Skript eingerichtet.

Folgende Objekte werden mit dem PowerShell-Skript erstellt:

Datenbank

  • monitoringDBA – Dies ist der Name der zentralisierten Sammlungsdatenbank, kann jedoch bei Bedarf geändert werden (siehe Abschnitt settings.ini).

    • Hinweis: Die Datenbank wird mit den Einstellungen der Modelldatenbank erstellt

Schemata

  • audit – Dieses Schema enthält alle Datenbankobjekte, die für Überwachungszwecke verwendet werden

  • inventory – Dieses Schema enthält Datenbankobjekte, die die Liste der SQL Server-Instanzen enthalten

  • monitoring – Dieses Schema enthält Datenbankobjekte, die zum Sammeln der Daten verwendet werden

Tabellen

  • monitoring.ErrorLog – Hiermit werden alle aufgetretenen Fehler erfasst

    • serverid – knüpft an inventory.MasterServerList.serverid an

    • script – Dies ist das Skript, das ausgeführt wurde

    • message – Dies ist die vom Skript generierte Fehlermeldung

    • error_timestamp – Dies ist das Datum und die Uhrzeit des Fehlers

  • inventory.MasterServerList – Hier wird eine Liste der zu überwachenden Server gespeichert

    • serverid – Generierte eindeutige ID

    • server_name – Name des Servers, auf dem SQL installiert ist

    • instance – Name der SQL Server-Instanz

    • ip – IP-Adresse der SQL Server-Instanz

    • port – Dies ist die Portnummer für die SQL Server-Instanz

    • is_active – 1 = aktiv, 0 = inaktiv

Settings.ini

Diese Datei wird verwendet, um den zentralen Server und die Datenbank zu konfigurieren, in denen alle gesammelten Daten gespeichert werden.

In dieser Datei müssen zwei Parameter aktualisiert werden:

  • centralServer – Dies ist der Server- und Instanzname unter dem Sie die Inventardatenbank erstellen möchten

  • inventoryDB – Hiermit wird der Name der Datenbank angegeben, die erstellt werden und die gesammelten Daten speichern soll. Wenn die Datenbank bereits vorhanden ist, wird die Datenbank nicht erstellt.

Wenn Sie das PowerShell-Skript ausführen, wird standardmäßig versucht, über eine vertrauenswürdige Verbindung eine Verbindung zum Server herzustellen. Dabei werden die folgenden Informationen in der Datei Settings.ini verwendet:

[General]
centralServer=QEOSQLSI_2019\SQL_2019_1
inventoryDB=monitoringDBA

Instances.txt

Die Datei instance.txt wird verwendet, um die Daten in die Tabelle inventory.MasterServerList zu laden. Das Format der Daten in der Textdatei lautet wie folgt:

  • Servername, Instanzname, IP-Adresse, Port

Hier ist ein Beispiel für zwei Instanzen, die wir überwachen werden. Es muss beachtet werden, dass für jedes Element einfache Anführungszeichen verwendet und jedes Element durch Kommas getrennt werden muss.

  • Die erste Zeile unten bezieht sich auf den Server QEOSQLSI_2019, der die Standardinstanz von SQL Server verwendet. Daher geben wir die Standardinstanz als MSSQLSERVER mit IP 10.0.0.35 an und verwenden den Standardport 1433

  • Die zweite Zeile ist auch für den Server QEOSQLSI_2019 vorgesehen, der eine benannte Instanz von SQL_2019_1 mit IP 10.0.0.35 verwendet, jedoch Port 50287 verwendet

'QEOSQLSI_2019', 'MSSQLSERVER', '10.0.0.35', 1433
'QEOSQLSI_2019', 'SQL_2019_1', '10.0.0.35', 50287

Diese Textdatei kann mit neuen Instanzinformationen aktualisiert und das PowerShell-Skript erneut ausgeführt werden.

PowerShell-Skript

Das PowerShell-Skript, das die oben genannten Objekte erstellt und Daten in die Tabelle inventory.MasterServerList einfügt, heißt:

  • Create-Master-Server-List.ps1

Das Skript verwendet die Datei Setting.ini, um zu bestimmen, welche SQL Server-Instanz für den zentralen Server verwendet werden soll, sowie den Namen der Datenbank, die Sie für die zentrale Datenerfassung verwenden möchten.

Es erstellt die Datenbank, Tabellen und lädt Daten aus der Datei Instances.txt in inventoryMasterServerList.

Anwendung

Nachdem die Daten in die Datei Instances.txt für Ihre SQL Server-Instanzen eingegeben und die Datei Settings.ini konfiguriert ist, sollten alle drei Dateien in demselben Ordner abliegen. In unserem Setup haben wir alle diese Dateien im Ordner “C:\temp\monitoringTest” abgelegt:

  • Instances.txt

  • Settings.ini

  • Create-Master-Server-List.ps1

Wenn Sie das Arbeitsverzeichnis ändern und etwas anderes als “C:\temp\monitoringTest” verwenden möchten, müssen Sie das PowerShell-Skript bearbeiten und die erste Zeile ändern. Dies wird auch für alle zukünftigen Module der Fall sein.

Dein PowerShell-Skript sollte nun wie folgt aussehen:

Get-Content "C:\temp\monitoringTest\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!"

Um Ihr Skript auszuführen, navigieren Sie nun wie folgt zu dem Ordner, in dem Sie die Dateien erstellt haben:

Option 1

  • Klicken Sie mit der rechten Maustaste auf Create-Master-Server-List.ps1 und wählen Mit PowerShell ausführen

Option 2

  • Öffnen Sie eine Kommandozeile und navigieren Sie mit folgendem Befehl zu dem Ordner, in dem Sie die obigen Dateien gespeichert und ausgeführt haben:

C:\temp\monitoringTest\Create-Master-Server-List.ps1

Überprüfen Sie die Erstellung von Datenbanken und Objekten

Nach dem Ausführen des PowerShell-Skripts sollten zwei Tabellen erstellt werden. Die beiden Tabellen, die erstellt werden, sind inventory.MasterServerList und monitoring.ErrorLog.

Wenn wir die Tabelle inventory.MasterServerList abfragen, sollten die eingefügten Zeilen angezeigt werden. In unserem Fall wurden 2 Zeilen wie unten gezeigt eingefügt:

Mit Hilfe der vorherigen Schritte sollten Sie nun dazu in der Lage sein, Ihre eigenen Basisdatenbankobjekte erstellen zu können, die Ihnen bei der weiteren Überwachung verschiedener Informationen helfen können.


→ Hier findest Du den Artikel zum direkten PDF-Download: madafa.de/download/artikel-downloads/


Schreibe einen Kommentar