MSDB Datenbanken

In dieser Tutorial-Reihe werden wir häufige Fragen im Zusammenhang mit der SQL Server msdb-Datenbank behandeln. Dies ist eine der Standard-Systemdatenbanken, die Teil jeder SQL Server-Installation sind. In diesem Tutorial erfahren Sie, wofür die msdb-Datenbank verwendet wird, wie sie konfiguriert und gewartet wird und welche Daten und Objekte in der msdb-Datenbank gespeichert sind.

Die folgenden Abschnitte werden in diesem Lernprogramm behandelt:

SQL Server MSDB Database Übersicht
SQL Server MSDB Database Lokation
SQL Server MSDB Database Größe und Wachstum
SQL Server MSDB Database Wartung
SQL Server MSDB Database Tabellen
SQL Server MSDB Database Objekte
SQL Server MSDB Database Berechtigungen
SQL Server MSDB Database Backup
SQL Server MSDB Database Wiederherstellungs-Modell
SQL Server MSDB Database Wiederherstellung
SQL Server MSDB Database Notfallplan

Lassen Sie uns loslegen!

Überblick

In diesem Abschnitt behandeln wir Fragen zum Zweck der MSDB-Datenbank, ob Sie einen SQL Server ohne eine MSDB-Datenbank ausführen können und vieles mehr.

Was ist die MSDB-Datenbank und was ist ihr Zweck?

Die MSDB-Datenbank ist eine von 4 sichtbaren Systemdatenbanken - Master, Model und TempDB bilden dabei die übrigen 3. Ihr Zweck ist es, die Historie einer Reihe von üblichen DBA-Aktivitäten wie Backups und Wiederherstellungen zu verfolgen. Sie enthält auch alle Daten für den SQL Server Agent, einschließlich der Jobs, Schritte, Operatoren, Warnungen und der Ausführungshistorie. Manchmal wird MSDB zum Speichern von SSIS-Paketen verwendet, obwohl diese auf moderneren Instanzen eher in einer SSIS-Katalogdatenbank gespeichert werden.

Wenn Sie den Befehl sp_helpdb ausführen, können Sie sehen, dass MSDB die Datenbank-ID Nummer 4 ist.

Kann ich SQL Server ohne eine MSDB-Datenbank betreiben?

Ein SQL Server kann für einen kurzen Zeitraum ohne eine Online-MSDB-Datenbank laufen, da die meisten gängigen SQL Server-Aktivitäten wie die Abfrage einer Benutzerdatenbank keine MSDB erfordern. Dies kann während der Wiederherstellung der MSDB auftreten oder wenn die Datenbank aufgrund einer Beschädigung nicht geladen werden kann.

Die in der folgenden Abbildung gezeigte Instanz von SQL Server wurde gestartet, nachdem die Daten- und Protokolldateien für MSDB absichtlich in falsche Werte umbenannt worden waren, so dass SQL Server sie nicht finden konnte. Alle anderen Datenbanken sind ordnungsgemäß online gegangen.

In diesem Fall sind die meisten gängigen Datenbankabfragen wie SELECT- und INSERT-Anweisungen, die an andere Datenbanken gerichtet sind, erfolgreich. Operationen, die von MSDB abhängen, zeigen ein seltsames Verhalten.

Wenn Sie beispielsweise versuchen, eine Datenbank zu sichern, wird eine Datei im Ziel gespeichert, aber die Anweisung schlägt fehl und zeigt an, dass diese Informationen nicht in MSDB aufgezeichnet werden konnten.

Processed 360 pages for database 'sampleDB', file 'sampleDB' on file 1. 
100 percent processed. 
Processed 2 pages for database 'sampleDB', file 'sampleDB_log' on file 1. 
Msg 945, Level 14, State 2, Line 1 
Database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details. 
Msg 3009, Level 16, State 1, Line 1 
Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful. 
BACKUP DATABASE successfully processed 362 pages in 0.305 seconds (9.248 MB/sec).

In diesem Szenario ist der Versuch, den SQL Server-Agenten zu starten, erfolgreich - auch wenn die MSDB nicht online ist! Der Versuch, die Liste der Agentenaufträge in SSMS anzuzeigen, führt jedoch zu einem Fehler.

Alternativ können Sie SQL Server Trace Flag 3608 verwenden, um SQL Server anzuweisen, nur die Master-Datenbank und keine anderen System- oder Benutzerdatenbanken zu starten. Dieses Flag wird in der Regel nur in extremen Wiederherstellungsszenarien verwendet, würde aber technisch gesehen die Ausführung eines SQL-Servers ohne MSDB ermöglichen.

Ist die Version der MSDB-Datenbank mit der Version, der Edition und dem Patch-Level von SQL Server identisch?

Im Laufe der Jahre hat MSDB zwischen den Hauptversionen von SQL Server einige kleine Überarbeitungen durchlaufen. In SQL Server 2008 wurde beispielsweise die Spalte "compressed_backup_size" hinzugefügt, um die Größe einer Datenbanksicherungsdatei aufzuzeichnen, wenn sie komprimiert wurde, da dies eine neue Funktion für diese Version war. Die für die Speicherung von SSIS-Paketen benötigten Objekte wurden 2005 hinzugefügt, und die Objekte zur Speicherung von DTS-Paketen wurden später weggelassen, als die Funktion entfernt wurde.

Die Edition und der Patch-Level haben keinen Einfluss auf MSDB, selbst wenn es sich um die Express-Edition handelt, die einige für MSDB erforderliche Funktionen wie den SQL Server Agent nicht enthält.

Können Sie die MSDB-Datenbank löschen und sollten Sie dies aus irgendeinem Grund tun?

SQL Server kann nicht über einen längeren Zeitraum ohne eine MSDB-Datenbank ausgeführt werden. Sie kann also nicht gelöscht, offline genommen oder abgetrennt werden. Außerdem kann sich der Autor keinen Grund vorstellen, warum man die MSDB-Datenbank löschen sollte, es sei denn, man möchte sie sofort wiederherstellen, was mit einer Wiederherstellung unter Verwendung der Option REPLACE möglich wäre.

DROP DATABASE msdb;
 
ALTER DATABASE msdb SET OFFLINE;
 
EXEC master.dbo.sp_detach_db @dbname = N'msdb';

Kann man die MSDB-Datenbank umbenennen und sollte man dies aus irgendeinem Grund tun?

Die MSDB-Datenbank kann nicht umbenannt werden. SQL Server und SQL Server Agent erwarten, dass die Datenbank online und unter dem Namen MSDB verfügbar ist. Auch hier kann sich der Autor keinen Grund vorstellen, warum man die MSDB-Datenbank umbenennen sollte.

exec sp_renamedb 'msdb', 'anythingElse';

Datenbank Location

Im folgenden Abschnitt dieser Reihe befassen wir uns mit dem Speicherort der Dateien, das Verschieben der MSDB-Datenbankdateien und die Dateien, aus denen die MSDB-Datenbank besteht.

Welche physischen Dateien, Namen, Speicherorte usw. unterstützen die MSDB-Datenbank?

Die MSDB-Datenbank besteht aus 2 Dateien - einer Datendatei und einer Protokolldatei. Die Datendatei hat den logischen Namen "MSDBData" und den Standarddateinamen "MSDBData.mdf". Die Protokolldatei heißt "MSDBLog" bzw. "MSDBLog.ldf".

Dies ist die Mindestanzahl von Dateien für jede Datenbank, und MSDB bildet hier keine Ausnahme. SQL Server erlaubt das Hinzufügen weiterer Dateien zu MSDB, aber es ist schwer vorstellbar, dass eine MSDB-Datenbank so groß wird, dass mehrere Dateien erforderlich sind.

Mit SQL Server können Sie entweder die physischen oder die logischen Namen dieser beiden Dateien umbenennen, aber es gibt wirklich keinen Grund, eine solche Operation durchzuführen. Es hat keinen Zweck und würde die Umgebung unnötig kompliziert machen.

Wo finde ich die MSDB-Datenbank auf der Festplatte und in SSMS?

Es gibt mehrere Möglichkeiten, die Dateien in SSMS zu finden. Sie können aus dem DMO sysfiles gelesen werden.

select name, filename from MSDB.sys.sysfiles;

Die Dateien werden auch im Fenster mit den Datenbankeigenschaften aufgelistet, wie im vorherigen Screenshot zu sehen.

Beim Durchsuchen der Festplatte sehen die Dateien wie jeder andere Satz von Datenbankdateien aus.

Macht es einen Unterschied, wo sich die MSDB-Datenbankdateien befinden (müssen sie auf C:, SAN usw. liegen)?

Die MSDB-Datenbankdateien sollten zusammen mit den anderen Datenbankdateien desselben Typs gespeichert werden. Das bedeutet im Allgemeinen, dass sie NICHT auf dem Laufwerk C: gespeichert werden. Die Screenshots in diesem Beitrag zeigen nur das Laufwerk C:, da sie von einer lokalen Instanz auf dem Laptop des Autors stammen, die nur ein Laufwerk C: enthält.

Seit SQL Server 2012 bietet das Installationsprogramm die Möglichkeit, auszuwählen, wo die Systemdatenbankdateien gespeichert werden sollen. Davor wurden sie zusammen mit den SQL Server-Binärdateien installiert und mussten nach der Installation an den Zielspeicherort verschoben werden.

Kann die MSDB-Datenbank verschoben werden?

Ja! Die MSDB-Datenbankdateien können verschoben werden. Es gibt 2 Methoden zum Verschieben der MSDB-Datenbankdateien. Die eine ist eine gängige Methode, insbesondere bei einer Neuinstallation mit begrenzten Benutzern oder Betriebszeitanforderungen. Die andere lässt die SQL Server-Instanz weiterlaufen und unterbricht nur die MSDB-abhängigen Teile des Betriebs wie den SQL Server Agent.

Möglichkeit 1

Dies ist die häufigere Methode, die im Allgemeinen durch das Ergebnis einer Google-Suche vorgeschlagen wird.

Schritt 1 - Informieren Sie SQL Server, dass sich die MSDB-Datenbankdaten und/oder die Protokolldatei(en) beim nächsten Start des Dienstes an einem anderen Ort befinden werden.

Während dieses Schritts kann der Dateiname geändert werden, aber es wird nicht empfohlen, den physischen Dateinamen oder die Erweiterung zu ändern.

Der folgende Code ändert den erwarteten Speicherort der MSDB-Datenbankdaten bzw. der Protokolldateien von dem Ort, an dem sie sich derzeit befinden, auf das Stammlaufwerk C. Er geht von den logischen Standardnamen aus.

ALTER DATABASE [MSDB] MODIFY FILE ( NAME = MSDBData , FILENAME = 'C:\MSDBData.mdf' );
ALTER DATABASE [MSDB] MODIFY FILE ( NAME = MSDBLog, FILENAME = 'C:\MSDBLog.ldf' );

Wenn Sie diese Befehle ausführen, erhalten Sie eine eindeutige Ausgabemeldung.

Die Datei "MSDBData" wurde im Systemkatalog geändert. Der neue Pfad wird beim nächsten Start der Datenbank verwendet.

Die Datei "MSDBLog" wurde im Systemkatalog geändert. Der neue Pfad wird beim nächsten Start der Datenbank verwendet.

Schritt 2 - Nachdem SQL Server über die neue(n) Position(en) informiert wurde, stoppen Sie die Dienste SQL Server und SQL Server Agent, damit die Sperre für die 2 (oder mehr) MSDB-Datenbankdateien aufgehoben wird.

Schritt 3 - Verschieben Sie die Datei(en) an den/die vorgesehenen Speicherort(e), der/die dem SQL Server in Schritt 1 mitgeteilt wurde(n).

Schritt 4 -Starten Sie den SQL Server-Dienst.

SQL Server sollte sofort starten. Überprüfen Sie sofort die MSDB in SSMS. Sieht sie so aus wie auf dem Screenshot ganz am Anfang dieses Tutorials mit den Worten "(Recovery Pending)" am Ende? Wenn ja, gibt es ein Problem mit dem Dateispeicherort der MSDB-Dateien. Vielleicht befinden sie sich nicht an dem Ort, an dem der Katalog sie vermutet, oder sie befinden sich am richtigen Ort, aber das Dienstkonto kann sie aufgrund eines Berechtigungsproblems nicht sehen. Wenn Sie Windows verwenden, bietet die Ereignisanzeige manchmal weitere Details darüber, warum einige Datenbanken nicht online gehen, und sollte konsultiert werden. Kümmern Sie sich auf jeden Fall darum, das Problem zu beheben, und überlegen Sie, ob Sie die Dateien gegebenenfalls zurücklegen, um die Instanz wieder zum Laufen zu bringen.

Möglichkeit 2

Stoppen Sie zunächst den SQL Server Agent-Dienst und beenden Sie alle anderen Prozesse, die MSDB verwenden könnten. Sichern Sie dann einfach die MSDB-Datenbank und stellen Sie MSDB sofort mit der Option REPLACE wieder her und verwenden Sie das Schlüsselwort MOVE, um einen neuen Speicherort für alle Dateien auszuwählen.

BACKUP DATABASE [msdb] TO  DISK = N'C:\backups\MSDB.bak';
GO

RESTORE DATABASE [msdb] FROM DISK = N'C:\backups\MSDB.bak' 
WITH  REPLACE,
MOVE N'MSDBData' TO N'C:\Backups\MSDBData_NEW.mdf',  
MOVE N'MSDBLog' TO N'C:\Backups\MSDBLog_NEW.ldf';
GO

Wenn die Wiederherstellung erfolgreich abgeschlossen ist, starten Sie den SQL Server Agent neu.

Größe und Wachstum

Genau wie jede andere SQL Server-Datenbank kann auch die MSDB-Datenbank wachsen. In diesem Abschnitt befassen wir uns mit dem Wachstum und der typischen Größe der MSDB-Datenbank.

Wie sollten die Wachstumseinstellungen der Datenbank und des Transaktionsprotokolls für die MSDB-Datenbank konfiguriert werden?
Das Wachstum der Daten- und Protokolldateien der MSDB-Datenbank sollte denselben Regeln folgen, die auch für die meisten Benutzerdatenbanken gelten. MSDB verhält sich viel mehr wie eine Benutzerdatenbank mit geringem Volumen als die anderen Systemdatenbanken. Eine Dateigröße von 64 MB ist ein guter Ausgangspunkt. Versuchen Sie, die Größe der Protokolldatei so zu wählen, dass sie nicht wachsen muss. Die richtige Größe für eine Instanz hängt von vielen Faktoren ab, wie in den nächsten Abschnitten beschrieben.

Wie groß ist die MSDB-Datenbank normalerweise?
Das kann sehr unterschiedlich sein - so sehr, dass es eigentlich keine typische Größe gibt. Es ist nicht ungewöhnlich, dass MSDB 50 MB oder 3000 MB groß ist. MSDB enthält einige Tabellen, die in bestimmten Szenarien sehr groß werden können, während die Größe in anderen Fällen vernachlässigbar sein kann.

Wie groß kann die MSDB-Datenbank werden?
MSDB speichert die Sicherungs- und Wiederherstellungshistorie. Stellen Sie sich einen Server mit 2000 Datenbanken vor, der alle 15 Minuten ein Log-Backup von allen Datenbanken erstellt. Das sind 192.000 neue Zeilen in den Tabellen backupset und backupmediaset tagtäglich! Wenn man einige Wochen oder Monate der Historie aufbewahrt, kann die Größe der MSDB astronomisch ansteigen.

Wenn die Backups in derselben Datenbank von einem SQL Server Agent-Job aufgerufen werden, bedeutet das 96 Job-Historienzeilen nur für diesen einen Job. Speichert dieser Auftrag die Ausgabe jedes Schritts und hat keine Größenbeschränkung? Vielleicht speichert diese Instanz auch eine große Reihe von SSIS-Paketen in der MSDB. Das kann noch mehr Platz beanspruchen und die Größe von MSDB auf mehrere Gigabyte erhöhen.Ein anderer Server hat vielleicht keine SSIS-Pakete, nur 5 Benutzerdatenbanken und führt stündliche Log-Backups nur für 2 davon durch. In diesem Fall wäre die MSDB um mehrere Größenordnungen kleiner.

Datenbank Wartung

In diesem Teil geht es um die Art der Wartungsroutinen, die Sie für die MSDB-Datenbank durchführen sollten.

Können Sie die Datenbank und das Transaktionsprotokoll für die MSDB-Datenbank verkleinern?
Die Größe der Daten- und Protokolldateien für die MSDB-Datenbank kann geändert werden. Wie bei jeder anderen Datenbank auch, ist dies ein Vorgang, der nicht sehr häufig erforderlich sein sollte. Wenn Sie nach der Lektüre dieses Tutorials eine aggressivere Haushaltsroutine einführen, um einige der Verlaufstabellen zu löschen, dann ist vielleicht eine einmalige Verkleinerung gerechtfertigt.

Der folgende Code würde die Daten- und Protokolldateien der MSDB-Datenbank auf 256 bzw. 64 Megabyte verkleinern. Er geht von den Standardnamen der logischen Dateien aus. Dieser Code würde im Kontext von MSDB ausgeführt werden.

DBCC SHRINKFILE (N'MSDBData' , 256);
DBCC SHRINKFILE (N'MSDBLog' , 64);

Sollten Sie MSDB-Indizes neu erstellen und wie lautet der Code?

Die Daten in MSDB sind manchmal groß und ändern sich häufig, da neue Zeilen in Protokolltabellen eingefügt werden, während ältere Zeilen auslaufen. Daher ist es ratsam, eine traditionelle Indexwartung für MSDB durchzuführen. Wenn es nicht bereits einen anderen Wartungsjob gibt, der MSDB mit einbezieht, könnte ein Wartungsplan angebracht sein. Dieser Screenshot zeigt einen, der mit dem Assistenten und allen Standardwerten nur für MSDB erstellt wurde.

Sollten Sie Integritätsprüfungen für die MSDB-Datenbank durchführen und wie lautet der Code?

Auf jeden Fall, ja! Sie sollte jeden Tag durchgeführt werden. Viele SQL Server-Aufgaben hängen vom SQL Server-Agent ab, und der SQL Server-Agent hängt von MSDB ab. Ohne MSDB wird jeder Sicherungsvorgang mit einem Fehler enden. Für einen SQL Server-DBA ist es von größter Wichtigkeit, MSDB-Beschädigungen so schnell wie möglich zu finden und zu beheben.

Der Code für die Integritätsprüfung der MSDB ist unten aufgeführt. Alternativ könnte ein Schritt hinzugefügt werden, um diesen Vorgang abzuschließen, wenn ein Wartungsplan verwendet wird, wie im vorherigen Schritt gezeigt.

DBCC CHECKDB ('MSDB');

Tabellen

Die MSDB-Datenbank besteht aus vielen Objekten, und die Möglichkeit, einige dieser Tabellen direkt abzufragen, kann sehr hilfreich sein. Wir werden uns einige der Tabellen ansehen, die für eine Abfrage nützlich sind.

In diesem Tutorial werden einige Gruppen von MSDB-Tabellen vorgestellt, die häufig abgefragt werden.

SELECT * FROM dbo.suspect_pages;

Suspect_pages hilft beim Aufspüren beschädigter Datenseiten oder solcher, die aufgrund eines ausgefallenen Plattensubsystems beschädigt werden könnten. Dabei kann es sich um Seiten handeln, die bei einer normalen Abfrage mit einem 823- oder 824-Fehler gefunden wurden, die durch einen DBCC-Check-Befehl gefunden wurden oder die bei einer Prüfsummenüberprüfung einer Sicherung gefunden wurden. DBAs sollten sich dieser Tabelle bewusst sein und sie gelegentlich abfragen, um sicherzustellen, dass keine unkorrigierten Fehler vorhanden sind.

Für diese Tabelle gibt es eine Grenze von 1000 Zeilen. Hoffentlich ist das keine Zahl, die häufig getestet wird, aber falls doch, werden ältere Zeilen nicht automatisch gelöscht, um Platz für neue zu schaffen. SQL Server hört einfach auf, Informationen aufzuzeichnen, wenn 1000 Zeilen in der Tabelle vorhanden sind. Aus diesem Grund sollten ältere Zeilen von einem DBA gelöscht werden, nachdem er die Ursache für diese Zeilen überprüft und geklärt hat.

Suspect_pages hilft beim Aufspüren beschädigter Datenseiten oder solcher, die aufgrund eines ausgefallenen Plattensubsystems beschädigt werden könnten. Dabei kann es sich um Seiten handeln, die bei einer normalen Abfrage mit einem 823- oder 824-Fehler gefunden wurden, die durch einen DBCC-Check-Befehl gefunden wurden oder die bei einer Prüfsummenüberprüfung einer Sicherung gefunden wurden. DBAs sollten sich dieser Tabelle bewusst sein und sie gelegentlich abfragen, um sicherzustellen, dass keine unkorrigierten Fehler vorhanden sind.

Für diese Tabelle gibt es eine Grenze von 1000 Zeilen. Hoffentlich ist das keine Zahl, die häufig getestet wird, aber falls doch, werden ältere Zeilen nicht automatisch gelöscht, um Platz für neue zu schaffen. SQL Server hört einfach auf, Informationen aufzuzeichnen, wenn 1000 Zeilen in der Tabelle vorhanden sind. Aus diesem Grund sollten ältere Zeilen von einem DBA gelöscht werden, nachdem er die Ursache für diese Zeilen überprüft und geklärt hat.

SELECT backup_set_id, database_name, bset.media_set_id, position, family_sequence_number, physical_device_name
FROM msdb.dbo.backupset bset
  INNER JOIN backupmediafamily bfam ON bset.media_set_id = bfam.media_set_id;

Die Tabelle backupset enthält jedes Mal eine Zeile, wenn eine Sicherung erstellt wird. Die Tabelle backupmediafamily enthält eine Zeile für jede erstellte oder angehängte Sicherungsdatei. Die meisten DBAs sichern eine Datenbank pro Datei und belassen diese Tabellen in einer 1:1-Beziehung. Wenn Sie Striped Backups verwenden oder mehrere Backups in eine einzige Datei packen, sind sie nicht 1:1.

Betrachten Sie diese Ergebnismenge der obigen Abfrage aus einer Striped-Sicherung.

Es gibt 2 Zeilen, aber nur eine einzige Backup-Set-ID, Nummer 1. Das bedeutet, dass es nur eine Sicherung gibt. Die Sicherung wurde an dieselbe Mediensatz-ID, Nummer 1, gesendet, aber dieser Satz enthält 2 Sequenznummern. Dies bedeutet, dass für eine Wiederherstellung der Datenbank beide Dateien benötigt werden und aufgelistet werden müssen.

RESTORE HEADERONLY FROM 
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Stripe1.bak',
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Stripe2.bak';

Betrachten Sie nun diese Ergebnismenge für eine Reihe von Backups, die sich eine einzige Datei teilen.

Es gibt zwei verschiedene Sicherungssätze, 2 und 3, aber einen einzigen Mediensatz und einen einzigen Dateinamen. Das bedeutet, dass sich mehrere Sicherungen eine einzige Datei teilen. Beide Zeilen haben eine Sequenz von 1, was bedeutet, dass es für keine der beiden Sicherungen ein Striping gibt. In diesem Fall wird die Positionsspalte wichtig. "DATEI" 1 ist eine weitere Testsicherung, während “DATEI" 2 eine MSDB-Sicherung ist.

RESTORE HEADERONLY FROM 
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MultiBackupFile.bak'
WITHH FILE = 1;

RESTORE HEADERONLY FROM 
DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\MultiBackupFile.bak'
WITH FILE = 2;

In dieser Tabelle werden alle dbmail-bezogenen Ereignisse aufgezeichnet. Es handelt sich um dieselben Informationen, die in SSMS über die Kontextmenüoption View Database Mail Log abgerufen werden können.

SELECT sp.profile_id, sp.name, spa.account_id, sa.name, sa.email_address, sa.display_name, ISNULL(spp.is_default, 0) IsDefaultProfile
FROM [dbo].[sysmail_profile] sp
  INNER JOIN [dbo].[sysmail_profileaccount] spa ON sp.profile_id = spa.profile_id
  INNER JOIN [dbo].[sysmail_account] sa ON spa.account_id = sa.account_id
  LEFT OUTER JOIN [dbo].[sysmail_principalprofile] spp ON sp.profile_id = spp.profile_id;

In dieser Tabellengruppe werden die Einstellungen der DBMail-Profile und -Konten gespeichert. Die Tabelle sysmail_profile enthält eine Zeile pro Profil, während sysmail_account eine Zeile für jedes Konto enthält. Jede Zeile in der Tabelle sysmail_profileaccount entspricht einem Profil und einem Konto. Dies ermöglicht eine Many-to-many-Beziehung zwischen Profilen und Konten.

Dieses Beispiel zeigt 2 Profile, die sich ein einziges Konto teilen.

Kann ich den Code für die Systemtabellen und -ansichten in MSDB sehen und wenn ja, wie?

Die Systemtabellen in MSDB können mit sp_help oder über das Kontextmenü "Design" im Object Explorer angezeigt werden.

exec sp_help sysjobs;

Die Ansichten haben im Gegensatz zu den Benutzeransichten keine Option Ändern oder Skripten als in ihrem Kontextmenü, aber sie können trotzdem mit sp_helptext angezeigt werden.

exec sp_helptext sysjobs_view;

Kann ich diese Objekte in der MSDB-Datenbank ändern?
SQL Server erlaubt es, die Systemtabellen und -ansichten zu ändern, aber das sollte auf keinen Fall geschehen. Die Assistenten sowie verschiedene Ansichten in SSMS und Azure Data Studio erwarten, dass sich diese Objekte auf eine bestimmte Art und Weise verhalten, und eine Änderung könnte dazu führen, dass diese Funktionen fehlschlagen.

Eine Ausnahme kann für nicht geclusterte Indizes gemacht werden. Auf einer Instanz mit vielen Datenbanken und einem großen Backup-Verlauf oder vielen SQL Server Agent-Aufträgen mit einem großen Verlauf können einige Überwachungsabfragen untragbar langsam laufen, und das Hinzufügen von nicht geclusterten Indizes zur Unterstützung von Berichten oder anderen Vorgängen kann dieses Problem lösen.

Kann ich meine eigenen Objekte in der MSDB-Datenbank speichern?
SQL Server erlaubt das Hinzufügen von Objekten zur MSDB-Datenbank, aber das sollte wahrscheinlich nicht erlaubt werden. Legen Sie stattdessen eine spezielle Benutzerdatenbank mit einem Namen wie "_DBA" an, in der Administratoren Objekte speichern können, die zur Verwaltung der Instanz verwendet werden.

Datenbank Objekte

Zusätzlich zu den Tabellen, in denen die Daten gespeichert sind, enthält MSDB auch mehrere gespeicherte Prozeduren, die Sie direkt ausführen können.

Welches sind die wichtigsten gespeicherten Prozeduren in der MSDB-Datenbank, die SQL Server-Profis kennen sollten?
Auch hier könnte ein ganzes Buch diesen Prozeduren gewidmet werden. Hier ist eine Auswahl der gängigsten Prozeduren.

exec sp_help_jobactivity

Die Prozedur sp_help_jobactivity liefert eine Momentaufnahme aller SQL Server Agent Jobs einschließlich ihres aktuellen Ausführungsstatus und der Details der letzten Ausführung. Sie ist sehr nützlich bei der Überwachung von Jobs, die eigentlich laufen sollten, es aber nicht tun. Solche Aufträge haben einen gültigen start_execution_date-Wert, aber einen NULL-Wert für stop_execution_date.

exec sp_start_job
exec sp_stop_job

Diese beiden Prozeduren starten bzw. stoppen einen SQL Server Agent Job. Sie akzeptieren entweder einen Jobnamen oder eine Job-ID als Parameter. Der Startbefehl akzeptiert optional auch eine Schritt-ID, wenn der Job an einer anderen Stelle als üblich gestartet werden soll.

Diese Prozeduren können nützlich sein, wenn Sie versuchen, bestimmte Aufgaben zu automatisieren oder nach Fehlern automatisch wiederherzustellen.

exec sp_send_dbmail
exec sp_notify_operator

Dieses Prozedurenpaar kann zum Versenden von E-Mail-Nachrichten von einem SQL Server aus verwendet werden, wenn DBMail eingerichtet wurde. sp_send_dbmail ist in der Regel die beliebtere Prozedur, da sie alle üblichen E-Mail-bezogenen Optionen wie to, cc, bcc, subject, body und attachments - neben anderen - akzeptiert. Einer der beliebtesten optionalen Parameter ist die Möglichkeit, die Ausgabe einer Abfrage in die Nachricht aufzunehmen.

sp_nofity_operator hat weit weniger Optionen, akzeptiert aber entweder einen Operator-Namen oder eine ID anstelle einer E-Mail-Adresse. Diese Abstraktionsebene kann für einige Administratoren nützlich sein, da die zugrunde liegende E-Mail-Adresse des Operators geändert werden kann, wenn sich die Personalgruppierungen ändern, anstatt den Code zu ändern, um eine neue E-Mail-Adresse zu berücksichtigen. Eine ähnliche Funktionalität kann erreicht werden, wenn beim Schreiben von Code, der sp_send_dbmail verwendet, immer Mailinglisten verwendet werden.

Kann ich den Code für die MSDB-Systemobjekte sehen und wenn ja, wie?
Die Systemprozeduren können im Objekt-Explorer unter "Programmierbarkeit", "Gespeicherte Prozeduren" und schließlich "Gespeicherte Systemprozeduren" eingesehen werden. Über die Option "Ändern" eines Kontextmenüs können Sie die Objektdefinition aufrufen.

Es ist sinnvoll, einige dieser Objekte zu überprüfen, da man auf diese Weise mehr über das Schema von MSDB erfährt, um bessere Abfragen für die Objekte zu schreiben.

Kann ich diese Objekte in der MSDB-Datenbank ändern?
Genau wie die Tabellen und Ansichten können auch die Systemprozeduren von SQL Server geändert werden. Aus denselben Gründen wie die Tabellen und Ansichten sollten auch die Prozeduren nicht geändert werden.

Berechtigungen

In diesem Abschnitt werden die erforderlichen Berechtigungen für den Zugriff auf die Daten und Objekte in der MSDB-Datenbank behandelt.

Wer hat Zugriff auf die MSDB-Datenbank und gibt es verschiedene Berechtigungsstufen?

MSDB ist für jeden Benutzer mit öffentlichem Zugriff auf die Instanz verfügbar. Die Liste der verfügbaren Tabellen ist jedoch begrenzt und schreibgeschützt.

Die gesamte Liste der Tabellen wird mit der Mitgliedschaft in der festen Rolle db_datareader, db_owner oder sysadmin verfügbar gemacht. Schreibrechte für diese Tabellen werden mit db_datawriter, db_owner oder der festen Rolle sysadmin gewährt. Beachten Sie, dass nur die sysadmin-Mitgliedschaft einem Benutzer Zugriff auf die grafische Benutzeroberfläche des SQL Server-Agenten für Aufträge gibt, deren Eigentümer er nicht ist, dass er aber in der Lage ist, die zugrunde liegenden Tabellen mit diesen Berechtigungen auf Datenbankebene direkt zu ändern.

Datenbank Backup

Eine häufig gestellte Frage im Zusammenhang mit MSDB-Datenbanken ist, ob und wie man die Datenbank sichern muss.

Muss ich die MSDB-Datenbank sichern?
Ein klares Ja! Für den Fall, dass die MSDB-Datenbank beschädigt wird oder der Server abstürzt und die MSDB-Datenbank wiederhergestellt werden muss, ist ein Backup die einzige Möglichkeit, dies zu tun. Diese Datenbank enthält auch Daten, die versehentlich von einem administrativen Benutzer beschädigt werden könnten. Wenn eine solche Person einen SQL Server Agent Job, Step oder Operator ändert oder löscht, kann ein Objekt über ein MSDB-Backup wiederhergestellt werden.

Es wird allgemein als beste Praxis angesehen, jeden Tag ein Backup der gesamten MSDB-Datenbank zu erstellen. Falls die MSDB auf einer Instanz außergewöhnlich groß ist, können Differentiale verwendet werden.

Wie kann ich die MSDB-Datenbank sichern?
Die MSDB-Datenbank kann auf die gleiche Weise gesichert werden wie jede andere Datenbank. Der unten stehende Code sichert die Datenbank in eine lokale Datei. Ziehen Sie in Erwägung, einen Speicherort zu verwenden, der nicht lokal auf dem SQL Server liegt - wenn dies möglich ist -, da dies das Risiko eines Verlusts der MSDB-Datenbank im Falle eines Serverausfalls verringert. Es empfiehlt sich, täglich ein Backup zu erstellen. Die Größe der Datei lässt dies im Allgemeinen zu.

BACKUP DATABASE MSDB TO DISK='C:\MSDB.bak';

Welches Wiederherstellungsmodell sollte die MSDB-Datenbank haben und kann das Wiederherstellungsmodell geändert werden?

Für die MSDB-Datenbank ist standardmäßig ein einfaches Wiederherstellungsmodell ausgewählt. Dies ist in der Regel akzeptabel, aber je nach den Wiederherstellungsanforderungen für die Instanz muss es möglicherweise in ein vollständiges Modell geändert werden. Überlegen Sie, ob sich Dinge wie die Auftragsschritte des SQL Server-Agenten so häufig ändern, dass eine zeitpunktbezogene Wiederherstellung erforderlich ist. Stellen Sie sicher, dass bei einer Änderung des Wiederherstellungsmodells die Protokollsicherungen in regelmäßigen Abständen geplant werden.

Wiederherstellung

Ist eine Wiederherstellung der MSDB-Datenbank überhaupt erforderlich?
Die MSDB-Datenbank muss wiederhergestellt werden, wenn sie bei einer Konsistenzprüfung beschädigt wurde. Eine Wiederherstellung kann auch erforderlich sein, wenn ein Objekt, z. B. ein Agent-Job-Step, unsachgemäß geändert wurde.

Wie kann die MSDB-Datenbank wiederhergestellt werden?
Der erste Schritt bei der Wiederherstellung von MSDB besteht darin, zu entscheiden, ob die gesamte Datenbank wiederhergestellt werden muss oder ob nur ein Objekt oder eine Zeile wiederhergestellt werden muss.

Wenn es sich um eine Beschädigung handelt, ist die Wiederherstellung der gesamten Datenbank wahrscheinlich die beste Methode. Wenn eine Wiederherstellung in Betracht gezogen wird, weil jemand einen SQL Server Agent-Auftrag geändert oder einen Operator gelöscht hat, ist eine Wiederherstellung auf Objektebene sinnvoller.

Um eine Wiederherstellung auf Objektebene durchzuführen, stellen Sie einfach eine aktuelle MSDB-Sicherung in einer neuen Benutzerdatenbank mit einem Namen wie MSDB_Recover wieder her. Anschließend werden die erforderlichen Tabellen abgefragt, um die fehlenden Daten anzuzeigen und diese Informationen zu verwenden, um das defekte Objekt wiederherzustellen. Eine Gruppe allgemeiner Tabellen, die abgefragt werden können, finden Sie weiter oben in diesem Lernprogramm.

Anweisungen für eine vollständige Wiederherstellung der Datenbank finden Sie weiter oben in diesem Artikel im Abschnitt über das Verschieben von MSDB.

Notfallwiederherstellung

Wie für jede SQL Server-Datenbank sollten Sie auch für MSDB einen Notfallwiederherstellungsplan haben.

Sollten Sie Objekte aus der MSDB-Datenbank für die Wiederherstellung im Notfall auslagern?
MSDB wäre mit einer aktuellen Sicherung besser geschützt. Man könnte versuchen, jeden SQL Server Agent Job, jeden Operator, jede Warnung usw. per Skript auszulagern, aber es wäre sehr schwierig, sie auf dem neuesten Stand zu halten. Ein solches Skript würde auch Dinge wie den SQL Server Agent-Verlauf nicht berücksichtigen. Ein vollständiges Backup der MSDB wäre der viel einfachere Weg, um alle in der MSDB gespeicherten Objekte und den Verlauf zu erfassen.

Können Sie die MSDB-Datenbankdateien von einer Instanz auf eine andere kopieren und ersetzen?
Eine Sicherung von MSDB kann auf derselben oder einer neueren Version von SQL Server wiederhergestellt werden. Der SQL Server Agent übernimmt die Aufträge und beginnt mit ihrer Ausführung. Er wird versuchen, die vorhandenen Datenbanken zu protokollieren.

Denken Sie daran, dass verschlüsselte Daten (z. B. Passwörter für DBMail) nur dann geladen werden können, wenn der Hauptschlüssel der Datenbank wiederhergestellt wird, da der aktuelle Schlüssel durch den Service-Master-Schlüssel verschlüsselt wird, der auf einer anderen Instanz anders sein würde.

Zusammenfassung

Hiermit sind wir nun am Ende des Artikels angelangt. Wir hoffen sehr, dass Ihnen diese Einführung in MSDB-Datenbanken gefallen hat. An dieser Stelle empfehlen wir Ihnen, jeden der einzelnen, präsentieren Schritte eigenständig auszuprobieren um etwaige Verständnislücken nachvollziehen zu können. Sollten Sie dennoch weitere Fragen haben, dann stehen wir Ihnen als erfahrene SQL Server Spezialist:innen gerne im Rahmen einer kompetenten Beratung zur Verfügung. Wir freuen uns auf Ihre Kontaktaufnahme!