dtcSN – SCOM-basierte SQL Server Dokumentation
Die Datenbank dtcSN dient als zentrale Steuerungs- und Dokumentationsdatenbank für SQL Server-Umgebungen, die über Microsoft SCOM (System Center Operations Manager) überwacht werden. Ziel ist es, Administratoren eine konsolidierte Sicht auf SQL Server-Instanzen, zugehörige Windows-Computer und Datenbanken zu geben – angereichert mit kundenspezifischen Metadaten, Lebenszyklusinformationen (Support-Ende) und Ausfallkennzeichen (NotFoundSince).
Das System besteht aus einem Satz von 10 Prozeduren, die in einer definierten Reihenfolge Daten aus dem OperationsManager Data Warehouse (OperationsManagerDW) lesen, aufbereiten und in lokale Kern-Tabellen überführen. Das Design setzt auf MERGE-Statements zur Erkennung neuer, geänderter oder nicht mehr vorhandener Objekte (weicher Löschung via NotFoundSince).
vManagedEntity, vManagedEntityProperty etc. sind im Test als leere Platzhalter definiert. In der Produktion verweisen sie auf die entsprechenden Tabellen der OperationsManagerDW-Datenbank (z. B. SELECT * FROM [OperationsManagerDW].dbo.vManagedEntity).
1. Manuell zu pflegende Steuertabellen
Bevor die automatisierten Prozeduren laufen können, müssen folgende Tabellen mit unternehmensspezifischen Werten gefüllt werden:
| Tabelle | Zweck | Inhaltsbeispiel |
|---|---|---|
ManagedSQLEntityType | Definiert, welche SCOM-Entity-Typen als SQL Server-Instanzen erkannt werden sollen (z. B. Microsoft.SQLServer.WindowsDBEngine). | Aktiv=1 für relevante Typen. |
ManagedEntityCompType | Legt fest, welche SCOM-Entity-Typen als Windows-Computer klassifiziert werden (meist Microsoft.Windows.Computer). | RowId 62 ist der Standard. |
ManagedEntityDatabaseType | Filtert Datenbank-Entities (typisch: Microsoft.SQLServer.Database). Nur aktive Typen werden synchronisiert. | Aktive=1 für SQL-Datenbanken. |
Customer | Legacy-Kundenstamm (Name, Langname, Adresse). | Kunde A mit ID 1. |
CustomerDomain | Verknüpft eine Domäne (z. B. adventure-works.com) mit einem Kunden. Die Funktionen GetCustomerName ermitteln darüber den Kunden zu einer SQL-Instanz. | Domain %testcustomer1.local% → CustomerID 1. |
ExcludeDB | Listet Namen von Datenbanken auf, die nicht importiert werden (typisch: master, model, msdb, tempdb, ReportServer). | Systemdatenbanken werden ausgeschlossen. |
ManagedSQLEntityType, ManagedEntityCompType, ManagedEntityDatabaseType und ExcludeDB.
2. Die zehn Kernprozeduren im Überblick
Die Prozeduren sind logisch aufeinander aufgebaut und müssen in der angegebenen Reihenfolge ausgeführt werden. Die Nummerierung (01, 01a, 02, …) gibt die Abhängigkeit vor.
| Nr. | Prozedur | Aufgabe |
|---|---|---|
| 1 | _01_InsertSQLServer | Identifiziert SQL Server-Instanzen aus SCOM (basierend auf ManagedSQLEntityType) und schreibt Basisdaten (ManagedEntityRowId, Maschine, Domain, Pfad) in die Tabelle SQLServer. Setzt NotFoundSince, wenn eine Instanz nicht mehr im SCOM vorkommt. |
| 2 | _01a_GetPropertiesForAllSQLEntries | Liest für alle vorhandenen SQL Server-Instanzen die detaillierten Eigenschaften (Edition, Version, ServicePack, TCP-Ports, Installationspfade etc.) über die Prozedur ReadPoperties aus und aktualisiert SQLServer. |
| 3 | _02_InsertComputer | Ermittelt Windows-Computer, auf denen die SQL Server-Instanzen laufen (SQLServer.Maschine JOIN mit vManagedEntity + ManagedEntityCompType) und befüllt die Tabelle Computer. |
| 4 | _02a_GetpropertiesForAllComputerEntries | Holt Eigenschaften der Computer (DNS-Name, IP-Adresse, Prozessoren, Betriebssystem etc.) über ReadPoperties. |
| 5 | _02b_GetWindowsOSForAllComputerEntrys | Ergänzt in der Tabelle Computer die Spalte WindowsOS mit dem Betriebssystemnamen aus der SCOM-Entity vom Typ Microsoft.Windows.Computer (Standard-Typ-ID 62). |
| 6 | _03_InsertSQLDatabases | Liest alle Datenbank-Entities, die über ManagedEntityDatabaseType als aktiv markiert sind (und nicht in ExcludeDB stehen) und schreibt sie in die Tabelle SQLDatabase. |
| 7 | _03a_GetPropertiesForAllSQLDBS | Holt Eigenschaften der Datenbanken (Collation, Recovery Model, Owner, Autogrow-Einstellungen etc.) über ReadPoperties. |
| 8 | SetCaseSensitiv | Korrigiert den Pfad (Path) in SQLDatabase, indem er vom zugehörigen SQL Server übernommen wird (nötig, falls SCOM unterschiedliche Schreibweisen liefert). |
| 9 | ReadPoperties | Zentrale Hilfsprozedur: Liest das XML-Property-Feld einer SCOM-Entity aus, parst die einzelnen Eigenschaften und schreibt sie in die entsprechende Ziel-Tabelle. Arbeitet set‑basiert (bzw. mit Batch‑Cursor) und vermeidet SQL-Injection durch Escaping. |
| 10 | ShowPoperties | Nur für Diagnose/Entwicklung: Zeigt alle verfügbaren Eigenschaften einer ManagedEntityRowId an – hilfreich, um neue Property-Namen zu erkennen, wenn SCOM Management Packs aktualisiert werden. |
3. Detaillierter Leseablauf (Datenfluss)
Die nachfolgende Grafik (textuell) veranschaulicht, wie die Daten von SCOM über die Views in die Kern-Tabellen gelangen:
[OperationsManagerDW] dtcSN (Zieldatenbank)
│ │
├─ vManagedEntity ────────────► _01_InsertSQLServer ──► SQLServer
│ │ _02_InsertComputer ───► Computer
│ └─ _03_InsertSQLDatabases ────────────────► SQLDatabase
│
├─ ManagedEntityProperty (XML) ──┬─ ReadPoperties (SQLServer) ───► SQLServer (Details)
│ ├─ ReadPoperties (Computer) ────► Computer (Details)
│ └─ ReadPoperties (SQLDatabase) ─► SQLDatabase (Details)
│
└─ ManagedEntityTypeProperty (Metadaten zu Property-GUIDs)
Schritt-für-Schritt:
- Initiale Extraktion der Basisdaten:
_01_InsertSQLServerund_02_InsertComputerlesen über die Views die Grundinformationen (vollständiger Name, Pfad, Maschinenname, Domäne) und erkennen neue/verschwundene Objekte. - Detailanreicherung:
_01a_GetPropertiesForAllSQLEntries,_02a_GetpropertiesForAllComputerEntriesund_03a_GetPropertiesForAllSQLDBSrufen für jede Zeile die ProzedurReadPopertiesauf, die das XML-Property-Feld parst und die einzelnen Attribute (z. B.Edition,Version,ServicePackVersion) aktualisiert. - Nachbearbeitung:
SetCaseSensitivharmonisiert Pfade;_02b_GetWindowsOSForAllComputerEntrysliefert das Betriebssystem.
Alle Schritte sind als inkrementelle Synchronisation ausgelegt: Einmal geladene Objekte werden bei jedem Lauf aktualisiert, nicht mehr vorhandene werden über NotFoundSince als „verschwunden“ markiert.
4. Fokus: Die Prozedur ReadPoperties
Diese Prozedur ist das Herzstück der Detailanreicherung. Sie wird mit einer @ManagedEntityRowId und einem @Target-Tabellennamen aufgerufen.
- Sie prüft, ob die Ziel-Tabelle existiert.
- Aus der SCOM-Property-XML (Spalte
PropertyXMLinvManagedEntityProperty) extrahiert sie alle Property-GUIDs und die zugehörigen Werte. - Über einen JOIN mit
vManagedEntityTypePropertywerden die systeminternen GUIDs in lesbarePropertySystemName(z. B.Edition,Version) übersetzt. - Nur Properties, die als Spalten in der Ziel-Tabelle existieren, werden übernommen.
- Das Update erfolgt set-basiert in Batches (je 100 Zeilen) – dadurch wird ein Cursor über alle Zeilen vermieden, die Performance erhöht und das Transaktionslog geschont.
- Um SQL-Injection zu verhindern, werden einfache Anführungszeichen in den Property-Werten escaped (
''). - Nach dem Update wird
Modifiedauf die aktuelle Uhrzeit gesetzt.
Die ursprüngliche Version aus dem Legacy-Skript arbeitete zeilenweise mit einem Cursor. Die verbesserte Fassung nutzt STRING_AGG und temporäre Batch-Tabellen, was die Laufzeit bei tausenden Entities drastisch reduziert.
5. Diagnose-Hilfe: ShowPoperties
Mit dieser Prozedur können Sie für eine beliebige ManagedEntityRowId aus SCOM alle vorhandenen Properties in lesbarer Form anzeigen:
EXEC dbo.ShowPoperties 1359226; -- fiktive RowIdDie Ausgabe enthält Spalten wie PropertySystemName und den aktuellen PropertyValue. Dies ist besonders nützlich, wenn:
- Ein neues SCOM Management Pack installiert wurde und zusätzliche Property-Namen verfügbar sind.
- Sie die Tabelle
SQLServerum eine neue Spalte erweitern möchten (siehe Abschnitt 6). - Fehler bei der Zuordnung auftreten – Sie sehen sofort, ob ein Property überhaupt geliefert wird.
ShowPoperties auf einer repräsentativen Instanz aus, um die exakten Property-Namen zu ermitteln (z. B. Edition oder VmMaxCpuPercent). Diese Namen müssen später als Spalten in den Kern-Tabellen vorhanden sein.
6. Erweiterung der Kern-Tabellen um neue Properties
Wenn SCOM durch ein Update oder ein neues Management Pack zusätzliche Eigenschaften für SQL Server, Computer oder Datenbanken liefert, können Sie diese einfach in dtcSN ergänzen, ohne die Prozeduren grundlegend ändern zu müssen.
Vorgehen in drei Schritten:
- Ermittlung des Property-Namens: Rufen Sie
ShowPopertiesfür eine Beispiel-Entity auf und notieren Sie den gewünschtenPropertySystemName(z. B.BackupDirectory). - ALTER TABLE – fügen Sie eine neue Spalte mit passendem Datentyp (meist
nvarchar(4000)odervarchar(256)) hinzu:ALTER TABLE dbo.SQLServer ADD BackupDirectory nvarchar(4000) NULL; - Update der Prozeduren: In
_01a_GetPropertiesForAllSQLEntries(oder der entsprechenden a-Prozedur für Computer/Datenbanken) muss die neue Spalte imUPDATE-Block ergänzt werden:
Die Prozedur... T.BackupDirectory = CASE WHEN P.PropertySystemName = 'BackupDirectory' THEN P.PropertyValue ELSE T.BackupDirectory END, ...ReadPopertiesbenötigt keine Anpassung, da sie dynamisch alle vorhandenen Spalten der Ziel-Tabelle abgleicht.
Die gleiche Technik gilt für Computer und SQLDatabase. Achten Sie darauf, den Property-Namen exakt so zu schreiben, wie er von SCOM geliefert wird (Groß-/Kleinschreibung beachten).
7. SQL-Agent-Job: Ausführungsreihenfolge
Für den nächtlichen (oder stündlichen) Synchronisationslauf wird ein SQL-Agent-Job empfohlen. Die Schritte müssen zwingend in der unten stehenden Nummerierung ablaufen, da spätere Prozeduren auf Ergebnissen früherer aufbauen.
-- ============================================================
-- SQL-Agent-Job "dtcSN_Synchronisation"
-- Ausführung täglich um 02:00 Uhr (Beispiel)
-- ============================================================
-- Schritt 1: SQL Server Grunddaten erfassen
EXEC dbo._01_InsertSQLServer;
-- Schritt 2: SQL Server Details (Properties) laden
EXEC dbo._01a_GetPropertiesForAllSQLEntries;
-- Schritt 3: Windows-Computer aus SQL Server-Kontext ermitteln
EXEC dbo._02_InsertComputer;
-- Schritt 4: Computer-Details (Properties) laden
EXEC dbo._02a_GetpropertiesForAllComputerEntries;
-- Schritt 5: Betriebssystem-Informationen ergänzen
EXEC dbo._02b_GetWindowsOSForAllComputerEntrys;
-- Schritt 6: Datenbank-Basisinformationen extrahieren
EXEC dbo._03_InsertSQLDatabases;
-- Schritt 7: Datenbank-Details (Properties) laden
EXEC dbo._03a_GetPropertiesForAllSQLDBS;
-- Schritt 8: Pfad-Korrektur (Case-Sensitivität)
EXEC dbo.SetCaseSensitiv;
-- Optional: Tägliches Logging / Reporting
-- SELECT COUNT(*) FROM SQLServer WHERE NotFoundSince IS NULL; ...
8. Häufige Probleme & Lösungen
| Problem | Mögliche Ursache | Lösung |
|---|---|---|
Keine Daten in SQLServer | ManagedSQLEntityType ist leer oder enthält falsche ManagedEntityTypeRowId. | Prüfen Sie mit SELECT DISTINCT ManagedEntityTypeRowId, ManagedEntityTypeSystemName FROM vManagedEntityTypeAll die vorhandenen Typen und tragen Sie die richtigen ein. |
| Properties werden nicht aktualisiert (NULL) | Spaltenname in der Ziel-Tabelle weicht vom PropertySystemName ab. | Mit ShowPoperties den exakten Namen ermitteln und ggf. die Spalte umbenennen. |
| Timeout oder langsamer Lauf | Fehlende Indizes auf ManagedEntityRowId in den Kern-Tabellen. | Indizes aus dem Bereitstellungsskript nachträglich anlegen (siehe IX_SQLServer_ManagedEntityRowId etc.). |
Doppelte Einträge in Computer | Ein Computer hostet mehrere SQL-Instanzen. | Die Prozedur _02_InsertComputer enthält bereits eine Duplikatsbereinigung (ROW_NUMBER()). Sollten dennoch Dubletten auftreten, führen Sie das Skript erneut aus. |
Keine Berechtigung auf OperationsManagerDW | Der SQL-Agent oder der Benutzer SLAReporting hat nur Leseberechtigung auf die DW-Tabellen. | Mindestens SELECT auf die Views vManagedEntity, ManagedEntityProperty und ManagedEntityTypeProperty erforderlich. |
9. Fazit & Ausblick
Das dtcSN-Framework stellt eine robuste, erweiterbare Brücke zwischen SCOM und unternehmensinterner SQL-Dokumentation dar. Durch die klare Trennung von Basisdaten, Detail-Properties und manuellen Steuertabellen lässt es sich an nahezu jede SCOM-Umgebung anpassen. Die Verwendung von Batches, gezielten Indizes und einer durchdachten Fehlerbehandlung (eigene ErrorLog-Tabelle) macht die Lösung produktionsreif.
In Zukunft könnten zusätzliche Module ergänzt werden (z. B. Analyse von SQLDatabase-Größen, automatische Alerting bei ablaufendem Support, oder Integration in ein CMDB). Der modulare Aufbau der Prozeduren erlaubt es, einzelne Komponenten zu erweitern, ohne das Gesamtsystem zu gefährden.
© 2026 – dtc-sql.de | Skriptversion 2.0 – Verbesserte Batches, Indizes und Fehlerbehandlung