Ü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).
1. 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:

Mainzer Datenfabrik - Überwachen von SQL Server mit PowerShell Core Object Setup

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.

Überwachen von SQL Server mit PowerShell Core Object Setup

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.