SQL_Kurs

ALTER DATABASE – Die wichtigsten Befehle im Überblick

Datenbankoptionen, Dateiverwaltung, Kompatibilitätslevel, Sicherheit, Hochverfügbarkeit – alles mit Praxisbeispielen

📌 Was ist ALTER DATABASE?

Der Befehl ALTER DATABASE ist eines der mächtigsten Werkzeuge in SQL Server. Mit ihm ändern Sie die Eigenschaften einer Datenbank – von grundlegenden Zuständen (online/offline, read‑only) über Dateiverwaltung bis hin zu Sicherheits- und Leistungsoptionen. Die Syntax ist umfangreich, aber gut strukturiert. In diesem Artikel finden Sie die wichtigsten Varianten mit Erläuterungen und Best Practices.

💡 Hinweis: Viele Änderungen erfordern, dass niemand sonst die Datenbank verwendet. Planen Sie solche Operationen in Wartungsfenstern. Einige Optionen können online geändert werden, andere nicht.

🔄 Datenbankzustand ändern

Diese Optionen sind die am häufigsten genutzten – sie steuern, ob eine Datenbank verfügbar ist und wer sie nutzen kann.

-- Datenbank offline nehmen (für Wartungsarbeiten, Verschiebung von Dateien)
ALTER DATABASE [MeineDB] SET OFFLINE;
-- Datenbank wieder online schalten
ALTER DATABASE [MeineDB] SET ONLINE;

-- Datenbank auf schreibgeschützt setzen (Reporting, Archiv)
ALTER DATABASE [MeineDB] SET READ_ONLY;
-- Wieder beschreibbar machen
ALTER DATABASE [MeineDB] SET READ_WRITE;

-- Nur ein Benutzer darf gleichzeitig verbinden (für exklusive Wartung)
ALTER DATABASE [MeineDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- Wieder für mehrere Benutzer freigeben
ALTER DATABASE [MeineDB] SET MULTI_USER;

-- Eingeschränkter Benutzermodus (nur Mitglieder der Rolle db_owner, dbcreator, sysadmin)
ALTER DATABASE [MeineDB] SET RESTRICTED_USER;

⚠️ Achtung: SET SINGLE_USER WITH ROLLBACK IMMEDIATE bricht alle aktiven Verbindungen sofort ab. Nutzen Sie dies nur in Notfällen oder kontrollierten Wartungsfenstern.

📁 Dateiverwaltung – Pfade, Größen, neue Dateien

Mit ALTER DATABASE steuern Sie die physischen Dateien der Datenbank: Sie können vorhandene Dateien verschieben/umbenennen, neue hinzufügen oder alte entfernen.

-- Datei (Daten oder Log) in der Metadaten umleiten (physikalischer Pfad ändert sich erst nach manueller Verschiebung)
ALTER DATABASE [MeineDB]
MODIFY FILE (
  NAME = 'MeineDB_Data',
  FILENAME = 'D:\Data\MeineDB_New.mdf'
);

-- Neue sekundäre Datendatei (NDF) hinzufügen
ALTER DATABASE [MeineDB]
ADD FILE (
  NAME = 'MeineDB_Data2',
  FILENAME = 'E:\Data\MeineDB_Data2.ndf',
  SIZE = 1GB,
  MAXSIZE = 10GB,
  FILEGROWTH = 512MB
);

-- Neue Log-Datei hinzufügen
ALTER DATABASE [MeineDB]
ADD LOG FILE (
  NAME = 'MeineDB_Log2',
  FILENAME = 'F:\Logs\MeineDB_Log2.ldf'
);

-- Eine Datei entfernen (nur möglich, wenn sie keine Daten mehr enthält, z. B. nach DBCC SHRINKFILE EMPTYFILE)
ALTER DATABASE [MeineDB]
REMOVE FILE [MeineDB_Data2];

💡 Best Practice: Verwenden Sie mehrere Datendateien für TempDB und große Benutzerdatenbanken, um Kontention zu reduzieren. Nutzen Sie FILEGROWTH in Megabyte (nicht Prozent), um häufige Wachstumsschübe zu vermeiden.

📜 Wiederherstellungsmodell (Recovery Model)

Das Wiederherstellungsmodell bestimmt, wie SQL Server das Transaktionsprotokoll verwaltet und welche Point-in-Time-Wiederherstellung möglich ist.

-- Vollständiges Wiederherstellungsmodell (für Produktion, mit regelmäßigen Log-Backups)
ALTER DATABASE [MeineDB] SET RECOVERY FULL;

-- Einfaches Wiederherstellungsmodell (Log wird bei Checkpoints automatisch abgeschnitten, keine Point-in-Time)
ALTER DATABASE [MeineDB] SET RECOVERY SIMPLE;

-- Massenprotokollierung (für große Ladevorgänge, reduziert Logging)
ALTER DATABASE [MeineDB] SET RECOVERY BULK_LOGGED;

📌 Wichtig: Wechseln Sie nur nach einem vollständigen Backup von SIMPLE zu FULL. Ohne regelmäßige Log-Backups wächst das Transaktionslog ins Unendliche.

🔢 Kompatibilitätslevel (COMPATIBILITY_LEVEL)

Das Kompatibilitätslevel steuert, welche Optimierungen und Syntaxen der Abfrageoptimierer verwenden darf. Es wird beim Upgrade einer Datenbank auf eine neue SQL Server-Version nicht automatisch erhöht.

-- Aktuelles Level abfragen
SELECT name, compatibility_level FROM sys.databases;

-- Auf SQL Server 2022 stellen (Level 160)
ALTER DATABASE [MeineDB] SET COMPATIBILITY_LEVEL = 160;

-- Auf SQL Server 2019 (150), 2017 (140), 2016 (130), 2014 (120) ...
ALTER DATABASE [MeineDB] SET COMPATIBILITY_LEVEL = 150;

💡 Best Practice: Testen Sie neue Kompatibilitätslevel zuerst in einer Nicht-Produktionsumgebung mit Query Store. Meist bringt ein höheres Level Performancevorteile (z. B. bessere Kardinalitätsschätzung).

⚡ Wichtige Datenbankoptionen für Performance und Wartung

SQL Server bietet zahlreiche Datenbankoptionen, die das Laufzeitverhalten beeinflussen. Hier die wichtigsten:

-- Automatisches Schließen (AUTO_CLOSE) – Vorsicht! Nur für Desktop-Datenbanken
ALTER DATABASE [MeineDB] SET AUTO_CLOSE OFF; -- Standard für Produktion

-- Automatisches Verkleinern (AUTO_SHRINK) – generell nicht empfehlenswert!
ALTER DATABASE [MeineDB] SET AUTO_SHRINK OFF; -- Deaktivieren!

-- Automatische Statistikaktualisierung (sollte immer ON sein)
ALTER DATABASE [MeineDB] SET AUTO_UPDATE_STATISTICS ON;

-- Asynchrone Statistikaktualisierung (reduziert Auswirkungen auf laufende Abfragen)
ALTER DATABASE [MeineDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- Seitenüberprüfung (CHECKSUM, TORN_PAGE_DETECTION, NONE)
ALTER DATABASE [MeineDB] SET PAGE_VERIFY CHECKSUM;

-- Parameter sniffing deaktivieren (Vorsicht, kann zu schlechteren Plänen führen)
ALTER DATABASE [MeineDB] SET PARAMETERIZATION FORCED;

-- Datenbank auf Standard- und benutzerdefinierte Parameterisierung zurücksetzen
ALTER DATABASE [MeineDB] SET PARAMETERIZATION SIMPLE;

🚨 Warnung: AUTO_SHRINK verursacht Fragmentierung und Performanceprobleme. Lassen Sie es deaktiviert. Auch AUTO_CLOSE ist für produktive Datenbanken ungeeignet, da es den Pufferpool ständig leert.

🔒 Transparent Data Encryption (TDE) aktivieren/deaktivieren

TDE verschlüsselt die Datenbankdateien auf Festplattenebene, ohne dass Anwendungen geändert werden müssen. Voraussetzung ist ein Datenbankverschlüsselungsschlüssel (DEK).

-- TDE für eine Datenbank aktivieren
ALTER DATABASE [MeineDB] SET ENCRYPTION ON;

-- TDE deaktivieren
ALTER DATABASE [MeineDB] SET ENCRYPTION OFF;

📌 Wichtig: TDE verursacht eine geringe CPU-Last (ca. 3-5 %). Sichern Sie immer den Verschlüsselungsschlüssel (Certificate) im Master, sonst sind Ihre Daten unwiederbringlich verloren.

📊 Query Store – Performanceüberwachung aktivieren

Der Query Store zeichnet Ausführungspläne und Laufzeitstatistiken auf und ist unabdingbar für die Erkennung von Planregressionen. Ab SQL Server 2016 verfügbar.

-- Query Store aktivieren
ALTER DATABASE [MeineDB] SET QUERY_STORE = ON;

-- Wichtige Query Store-Parameter (optional)
ALTER DATABASE [MeineDB] SET QUERY_STORE (
  OPERATION_MODE = READ_WRITE,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
  DATA_FLUSH_INTERVAL_SECONDS = 900,
  MAX_STORAGE_SIZE_MB = 1024
);

-- Query Store deaktivieren (nicht empfehlenswert)
ALTER DATABASE [MeineDB] SET QUERY_STORE = OFF;

💡 Best Practice: Aktivieren Sie Query Store auf allen Produktionsdatenbanken. Der Overhead ist minimal, die Diagnosemöglichkeiten sind enorm.

🔄 Hochverfügbarkeit und Always On

Für Always On Availability Groups (AG) gibt es spezielle ALTER DATABASE-Befehle, um Datenbanken in eine AG aufzunehmen oder aus ihr zu entfernen.

-- Datenbank für die Verwendung mit Always On vorbereiten (vollständiges Backup erforderlich)
ALTER DATABASE [MeineDB] SET HADR OFF;

-- Datenbank einer Verfügbarkeitsgruppe hinzufügen (auf dem primären Replikat)
ALTER DATABASE [MeineDB] SET HADR AVAILABILITY GROUP = [AG_Name];

-- Datenbank aus der AG entfernen (auf dem primären Replikat)
ALTER DATABASE [MeineDB] SET HADR OFF;

💡 Hinweis: Diese Befehle werden normalerweise über den AG-Assistenten oder T‑SQL-Skripte gesteuert. Direktes SET HADR erfordert entsprechende Berechtigungen.

🌐 Kollation (Collation) ändern

Das Ändern der Datenbankkollation ist mit ALTER DATABASE möglich, betrifft aber nur neue Objekte. Bestehende Spalten müssen separat umgestellt werden.

-- Kollation der Datenbank ändern
ALTER DATABASE [MeineDB] COLLATE Latin1_General_100_CI_AS_SC;

-- Datenbank umbenennen (natürlich auch mit ALTER DATABASE möglich – selten genutzt)
ALTER DATABASE [MeineDB] MODIFY NAME = [MeineNeueDB];

⚠️ Warnung: Die Änderung der Kollation kann zu Konflikten bei Vergleichsoperationen führen. Planen Sie dies sorgfältig.

✅ Best Practices für den Einsatz von ALTER DATABASE

Immer aktuelles Backup erstellen, bevor Sie kritische Änderungen durchführen.
Verwenden Sie ein Wartungsfenster für SET OFFLINE oder MODIFY FILE.
Testen Sie Änderungen zuerst auf einer Testumgebung – besonders Kompatibilitätslevel, Kollation, TDE.
Vermeiden Sie AUTO_SHRINK und AUTO_CLOSE in Produktionsumgebungen.
Aktivieren Sie Query Store auf allen Datenbanken, um Performanceprobleme zu erkennen.
Dokumentieren Sie Änderungen – protokollieren Sie, wer wann welche ALTER DATABASE-Befehle ausgeführt hat.
Nutzen Sie die Windows-Ereignisanzeige und das SQL Server-Protokoll zur Fehlersuche, falls eine Änderung fehlschlägt.

📋 Kurzreferenz: Häufige ALTER DATABASE-Klauseln

Kategorie	Befehl (Beispiel)	Erläuterung
Zustand	`SET OFFLINE / ONLINE`	Datenbank für Wartung sperren / freigeben
Zugriff	`SET READ_ONLY / READ_WRITE`	Schreibschutz ein-/ausschalten
Benutzermodus	`SET SINGLE_USER / MULTI_USER / RESTRICTED_USER`	Zahl der gleichzeitigen Verbindungen beschränken
Dateien	`MODIFY FILE (NAME=..., FILENAME=...)`	Pfad einer Datei ändern (Metadaten)
Dateien	`ADD FILE (...) / ADD LOG FILE (...)`	Neue Daten-/Logdatei hinzufügen
Dateien	`REMOVE FILE [Dateiname]`	Datei löschen (nach Leerung)
Recovery	`SET RECOVERY FULL / SIMPLE / BULK_LOGGED`	Wiederherstellungsmodell
Kompatibilität	`SET COMPATIBILITY_LEVEL = 160`	SQL Server-Version des Optimierers
Performance	`SET PAGE_VERIFY CHECKSUM`	Seitenüberprüfung (empfohlen)
Performance	`SET AUTO_UPDATE_STATISTICS ON`	Automatische Statistiken (Standard)
Sicherheit	`SET ENCRYPTION ON / OFF`	TDE aktivieren/deaktivieren
Query Store	`SET QUERY_STORE = ON`	Leistungsüberwachung einschalten
HADR	`SET HADR AVAILABILITY GROUP = ...`	Datenbank einer AG zuordnen
Kollation	`COLLATE Latin1_General_CI_AS`	Standardsortierung ändern
Umbenennung	`MODIFY NAME = [NeuerName]`	Logischen Datenbanknamen ändern

💡 Tipp: Speichern Sie diese Tabelle als schnelle Referenz. Die genaue Syntax finden Sie in der offiziellen Microsoft-Dokumentation.