Partitionierung großer Tabellen & Auslagerung in eine eigene Datenbank

1. Warum partitionieren?

Die Partitionierung teilt eine Tabelle oder einen Index in kleinere, verwaltbare Einheiten – sogenannte Partitionen – auf, ohne die logische Struktur zu ändern. Jede Partition kann auf einer eigenen Dateigruppe gespeichert werden. Die Vorteile liegen auf der Hand:

• Schnellere Lade- und Löschvorgänge: Statt DELETE mit Logging zu verwenden, können ganze Partitionen per TRUNCATE oder SWITCH entfernt werden.
• Verbesserte Wartung: Indexrebuilds oder Statistiken können partitionweise ausgeführt werden.
• Gezielte Archivierung: Ältere Partitionen lassen sich mit einem einzigen Metadatenbefehl in eine andere Tabelle oder Datenbank verschieben.
• Bessere Backup-Strategien: Dateigruppen mit historischen Partitionen können separat und seltener gesichert werden.

Voraussetzungen und Konzepte

Für die Partitionierung benötigen Sie:

• Eine Partitionsfunktion – definiert die Grenzwerte (z. B. monatliche Datumsgrenzen).
• Ein Partitionsschema – ordnet die Partitionen Dateigruppen zu.
• Die Tabelle oder der Index wird auf dem Schema erstellt (bei nicht partitionierten Tabellen kann nachträglich partitioniert werden, allerdings mit hohem Aufwand).

-- Beispiel: Partitionsfunktion für monatliche Partitionen ab 2024 
CREATE PARTITION FUNCTION pf_OrderDate (datetime) 
AS RANGE RIGHT FOR VALUES ( 
'2024-01-01', '2024-02-01', '2024-03-01', '2024-04-01', 
'2024-05-01', '2024-06-01', '2024-07-01', '2024-08-01', 
'2024-09-01', '2024-10-01', '2024-11-01', '2024-12-01' 
); 
-- Partitionsschema: Dateigruppen (optimalerweise jeweils auf eigenem Laufwerk) 
CREATE PARTITION SCHEME ps_OrderDate 
AS PARTITION pf_OrderDate 
TO (FG_2024_01, FG_2024_02, FG_2024_03, FG_2024_04,  
FG_2024_05, FG_2024_06, FG_2024_07, FG_2024_08, 
FG_2024_09, FG_2024_10, FG_2024_11, FG_2024_12, [PRIMARY]); 

2. Partitionen auslagern – SWITCH & Archivierung

Das Herzstück der effizienten Auslagerung ist der Befehl ALTER TABLE ... SWITCH. Damit können Sie eine komplette Partition einer partitionierten Tabelle in eine leere, nicht partitionierte Zieltabelle verschieben – ohne Datenkopie, nur durch Änderung von Metadaten. Diese Operation dauert Sekundenbruchteile, selbst bei Milliarden von Zeilen.

Schritt-für-Schritt zur Archiv-Datenbank

1. Archiv-Datenbank erstellen (ggf. auf langsamerem Speicher).
2. Zieltabelle mit identischem Schema in der Archiv-DB anlegen (ohne Partitionierung, aber mit Clustered Index).
3. Sicherstellen, dass die Quellpartition aktiv ist – z. B. Partition mit Daten aus '2023-01-01' bis '2023-01-31'.
4. SWITCH ausführen: ALTER TABLE Quelle SWITCH PARTITION 5 TO ArchivDB.dbo.Zieltabelle.
5. Die Zieltabelle enthält nun die Daten – und kann in der Archiv-DB weiterverarbeitet oder komprimiert werden.

-- 1. Archivdatenbank und Tabelle anlegen (vereinfacht) 
CREATE DATABASE ArchiveDB; 
GO 
USE ArchiveDB; 
CREATE TABLE dbo.Orders_Archive ( 
OrderID int NOT NULL, 
OrderDate datetime NOT NULL, 
CustomerID int, 
Amount decimal(10,2) 
); 
CREATE CLUSTERED INDEX IX_OrderDate ON dbo.Orders_Archive(OrderDate); 
GO 
 
-- 2. In der Quelldatenbank: Partition 7 (z.B. März 2024) auslagern 
USE MainDB; 
ALTER TABLE dbo.Orders SWITCH PARTITION 7 TO ArchiveDB.dbo.Orders_Archive; 
-- Optional: Partition leeren (TRUNCATE) nach erfolgreicher Prüfung 

3. Vollständiges Beispiel – Partitionierte Verkaufstabelle

Angenommen, Sie haben eine Tabelle Sales mit 500 Mio. Zeilen. Sie möchten jeden Monat die älteste Partition in eine Archivdatenbank verschieben. Dazu richten Sie eine Routine ein, die folgende Schritte ausführt:

3.1 Initiale Partitionierung der Quelltabelle

-- Partitionsfunktion (monatlich über 24 Monate) 
CREATE PARTITION FUNCTION pf_SalesDate (datetime) 
AS RANGE RIGHT FOR VALUES (... alle Monatsgrenzen ...); 
 
CREATE PARTITION SCHEME ps_SalesDate 
AS PARTITION pf_SalesDate 
ALL TO ([PRIMARY]); -- oder auf verschiedene FG 
 
CREATE TABLE dbo.Sales( 
SaleID bigint IDENTITY, 
SaleDate datetime NOT NULL, 
ProductID int, 
Amount money, 
CONSTRAINT PK_Sales PRIMARY KEY (SaleID, SaleDate) 
) ON ps_SalesDate(SaleDate); 

3.2 Automatisierte Archivierung (z. B. via SQL Agent)

-- Partitionsnummer der ältesten ermitteln 
DECLARE @PartitionNr int = ( 
SELECT TOP 1 p.partition_number 
FROM sys.partitions p 
WHERE p.object_id = OBJECT_ID('dbo.Sales') 
ORDER BY p.partition_number 
); 
 
-- Auslagern in ArchiveDB.dbo.Sales_Archive (exakt gleiches Schema) 
ALTER TABLE dbo.Sales SWITCH PARTITION @PartitionNr  
TO ArchiveDB.dbo.Sales_Archive; 
 
-- In der Archiv-DB kannst du nun Indizes oder Komprimierung anpassen. 
-- In der Quelle die leere Partition mit der nächsten Grenze verschmelzen (MERGE) 
-- oder für neuen Monat vorbereiten (SPLIT) 

4. Best Practices für Partitionierung & Archivierung

5. Fallstricke und Lösungen

• Fremdschlüssel: Ein SWITCH wird blockiert, wenn Fremdschlüssel auf die Quell- oder Zielpartition verweisen. Entfernen Sie temporär Fremdschlüssel oder planen Sie die Archivierung ohne Verweise.
• Identitätsspalten: Die Zieltabelle muss in der Archiv-DB ebenfalls eine Identitätsspalte haben, aber SET IDENTITY_INSERT ist nicht nötig – der SWITCH übernimmt die Werte.
• Check Constraints: Die Quellpartition und die Zieltabelle müssen übereinstimmende CHECK-Constraints haben, die die Grenzen widerspiegeln (bei SWITCH in andere Datenbank ist besondere Vorsicht geboten).
• Partitionsfunktion ändern: Nach mehreren SWITCH-Operationen sollten Sie überflüssige Partitionen via MERGE zusammenfassen, um die Anzahl der Partitionen zu reduzieren.

Beispiel für SPLIT / MERGE

-- Neue Partition für nächsten Monat hinzufügen 
ALTER PARTITION SCHEME ps_SalesDate NEXT USED FG_2025_01; 
ALTER PARTITION FUNCTION pf_SalesDate() SPLIT RANGE ('2025-01-01'); 
 
-- Alte, leere Partition entfernen (nach Auslagerung) 
ALTER PARTITION FUNCTION pf_SalesDate() MERGE RANGE ('2023-01-01'); 

6. Fazit – Wann lohnt sich die Partitionierung mit Auslagerung?

Die Kombination aus Partitionierung und SWITCH ist ideal für große, wachsende Tabellen mit zeitlicher oder schlüsselbasierter Teilung. Sie minimiert Downtimes, vereinfacht die Datenarchivierung und senkt die Betriebskosten (Backup, Storage, Wartung). Nutzen Sie dieses Muster besonders für:

• Audit- oder Logging-Tabellen (> 100 Mio. Zeilen)
• Faktentabellen im Data Warehouse mit Sliding-Window-Szenarien
• Anwendungen mit gesetzlichen Aufbewahrungsfristen (z. B. DSGVO, GoBD)