Komprimierung von Partitionen

Datenbanken wachsen. Wenn der Speicherplatz knapp wird, können einzelne Tabellen komprimiert werden, jedoch sollte dies mit Vorsicht angegangen werden, denn wenn hoch frequentierte Tabellen komprimiert werden, kostet das unnötig Zeit und CPU Ressourcen. Daher sollte diese Technologie eher bei partitionierten Tabellen zum Einsatz gebracht werden, wenn man ältere und nicht häufig verwendete Daten von den aktuellen differenziert werden können. Die Partitionen können dann durch die Zeilen/Seiten Komprimierung des SQL Servers komprimiert werden.

Hinweis:
Dieser Artikel bezieht sich auf die folgende Artikel in den Beispielen:
Patritionierung von bestehenden Tabellen
Partitionierung von SQL Server Tabellen

Nimmt man das Beispiel „Patritionierung von bestehenden Tabellen“ als Grundlage und fügt dort weitere Daten ein (pro Partition 2000 Zeilen):
DECLARE @counter INT = 9
WHILE @counter < 10000
BEGIN
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2008', '20080308', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'März 2009', '20090308', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Februar 2010', '20100209', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2011', '20110120', '100');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (@counter, 'Januar 2012', '20120115', '300');
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (@counter, @counter);
SET @counter = @counter + 1
END

Nun ergibt sich folgende Speicherbelegung (SQL Server Bericht, Speicherbelegung durch Partitionen):

Wenn nun die Partitionen durch folgendes Statement komprimiert werden …
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 1 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 2 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 3 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[buchungen] REBUILD PARTITION = 5 WITH(DATA_COMPRESSION = ROW )

… ergibt sich folgendes Bild:

Gesamt ergibt das in diesem Beispiel ~100 KB an Speicherplatz-Gewinn. Die Komprimierung kann auch hervorragend durch den Assistenten des SMSS durchgeführt werden.

Partitionierung von bestehenden Tabellen

In dem Artikel Partitionierung von SQL Server Tabellen habe ich bereits beschrieben, wie man die Partionierung auf eine neue Tabelle anwendet. In der Praxis führt man solche Technologien doch eher an bestehenden Tabellen ein, wenn man merkt, das die Tabelle doch größer als erwartet wird. Daher stellt sich die Frage: Wie partitioniere ich eine bestehende Tabelle?

Gründsätzlich könnte man die bestehende Tabelle auslagern, neu erstellen und dann wieder alle Daten reinkopieren. Das ist u.U nicht immer ganz einfach, besonders dann, wenn die Tabelle referentielle Integritäten besitzt (Fremdschlüssel, bzw. „Verlinkungen in andere Tabellen“) und man durch ein Löschen und Neuerstellen doch mehr Arbeit produziert, als notwendig.

Zuerst erstellen wir uns die Umgebung (auf Basis des Beispiels von dem Artikel: Partitionierung von SQL Server Tabellen):
CREATE TABLE dbo.buchungen (
[uid] INT NOT NULL PRIMARY KEY,
[name] VARCHAR(50),
[datum] DATETIME DEFAULT GETDATE() NOT NULL,
[wert] MONEY);
GO

CREATE TABLE dbo.buchungen_Log (
[uid] INT NOT NULL PRIMARY KEY,
[buchungen_uid] INT NOT NULL);
GO

ALTER TABLE dbo.buchungen_Log ADD CONSTRAINT FK_buchungen_Log_buchungen FOREIGN KEY
(buchungen_uid) REFERENCES dbo.buchungen
(uid)
GO

INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (1, 'Januar 2008', '20080308', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (2, 'März 2009', '20090308', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (3, 'Februar 2010', '20100209', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (4, 'Januar 2011', '20110120', '100');
INSERT INTO dbo.buchungen (uid, name, datum, wert) VALUES (5, 'Januar 2012', '20120115', '300');
GO
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (1, 1);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (2, 2);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (3, 3);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (4, 4);
INSERT INTO dbo.buchungen_Log (uid, buchungen_uid) VALUES (5, 5);
GO

Nun haben wir die Tabelle „Buchungen“ die die zu partitionierenden Daten enthält und auch den Fremschlüssel für die Tabelle „buchungen_log“ stellt.
Ansich geht man nun wir bei der Partitionierung einer neuen Tabelle vor, indem man zuerst die Dateigruppen und die jeweiligen Dateien erstellt:

ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
GO

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2009.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2010.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2011.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2012.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\Archiv.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun benötigt man die Partitionierungs-Funktion und das Schema für die Datenverteilung:
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2009]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2010]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2011]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv2012]
ALTER DATABASE [Produktion] ADD FILEGROUP [Archiv]
GO

ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2009',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2009.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2009]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2010',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2010.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2010]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2011',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2011.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2011]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'2012',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\2012.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv2012]
GO
ALTER DATABASE [Produktion] ADD FILE ( NAME = N'Archiv',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL1\MSSQL\DATA\Archiv.ndf',
SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [Archiv]
GO

Nun wird die Partionierungsfunktion wie auch das Schema erstellt, um die Datentrennung zu regeln:
CREATE PARTITION FUNCTION ArchivierungsFunktion (DATETIME) AS RANGE RIGHT
FOR VALUES (
'20090101' -- Kleiner als 01.01.2009
, '20100101' -- Kleiner als 01.01.2010, größer als 01.01.2009
, '20110101' -- Kleiner als 01.01.2011, größer als 01.01.2010
, '20120101' -- Kleiner als 01.01.2012, größer als 01.01.2011
);

CREATE PARTITION SCHEME ArchivierungsSchema AS PARTITION ArchivierungsFunktion
TO ('Archiv', 'Archiv2009', 'Archiv2010', 'Archiv2011', 'Archiv2012');

Nun benötigt man einen Primär Schlüssel, wo jedoch auch das Datums Feld (Kennzeichen für die Partionierung) enthalten ist. Dafür muss jedoch der Schlüssel erstmal entfernt werden, wie auch die Fremschlüssel Verbindung:
ALTER TABLE [dbo].[buchungen_Log] DROP CONSTRAINT [FK_buchungen_Log_buchungen]
GO
ALTER TABLE [dbo].[buchungen] DROP CONSTRAINT [PK__buchunge__DD7012647F60ED59]
GO

Nun kann der Primärschlüssel erstellt werden, der dann an die Partionierungsfunktion gehängt wird (dieser muss ein Gruppierter Index sein, eine HEAP Tabelle (ohne Gruppierten Index) wird hier nicht unterstützt, da die Daten sonst nicht verschoben werden können):
ALTER TABLE dbo.buchungen ADD CONSTRAINT
PK_buchungen PRIMARY KEY CLUSTERED
([uid], [datum]) on ArchivierungsSchema(datum);

Kontrolliert werden kann dies folgendermaßen:
SELECT
sys.filegroups.name 'Partitionsname' , sys.partitions.rows 'Zeilenanzahl'
FROM
sys.partitions INNER JOIN sys.allocation_units
ON sys.allocation_units.container_id = sys.partitions.hobt_id
INNER JOIN sys.filegroups ON sys.filegroups.data_space_id = sys.allocation_units.data_space_id
WHERE sys.partitions.object_id = OBJECT_ID('dbo.buchungen')

Das wars.

Datenbank Snapshots

Ab Microsoft SQL Server 2005 ist es möglich Datenbank Snapshots zu erstellen (ab der Enterprise Edition). Durch diese Technologie wird ein Abbild der Quell-Datenbank in eine Datendatei (je nach Menge der enthaltenen Datendateien) geschrieben, womit es möglich ist, die Datenbank abzufragen ohne das Laufende Änderungen aus der eigentlichen Datenbank in das Ergebnis aus dem Snapshpt einfließen. Der Datenbank Snapshot ist schreibgeschützt (verfügt über kein Transaktionsprotokoll) und es können keine Daten- oder Strukturellen Änderungen am Snapshot vorgenommen werden, lediglich Lesende Anweisungen sind zugelassen und es können auch keine Benutzer oder Rollen hinzugefügt werden.

Zur Praxis: Ein Datenbank Snapshot wird folgendermaßen erzeugt:
CREATE DATABASE DBSNAPSHOT ON (name=LOGISCHER_DATEINAME, filename='PHSISCHER_PFAD_UND_DATEINAME') AS SNAPSHOT OF DATENBANK
Durch diesen Befehl wird erstmal ein Container erzeugt in dem keine Datenseiten enthalten sind. Sobald eine Datenseite in der Quell-Datenbank verändert wird, wird diese in den Snapshot rüberkopiert. Das bedeutet, das Abfragen auf den Snapshot auf unveränderte Daten in der Produktiven Datenbanken in die eigentliche Datenbank umgeleitet werden. Sobald Daten verändert worden sind, werden diese Datenseiten aus dem Snapshot gelesen.
Strukturelle Änderungen (DDL) die auf der Produktiven Datenbank vorgenommen werden, werden nicht in den Snapshot übernommen, sondern sind lediglich in der eigentlichen Datenbank enthalten, da diese nicht zum Zeitpunkt des Snapshots existierten.

Besteht eine Datenbank aus meheren Datendateien, müssen diese ebenfalls mit angegeben werden. Es können keine Dateien oder Dateigruppen ausgelassen werden.

Möchte man eine Datenbank wiederherstellen, muss zuerst der Datenbank Snapshot entfernt werden, da ansonsten keine Rücksicherung möglich ist. Das begründet sich durch die Verlinkung der Seiten, da der Snapshot auf die unveränderten Datenseiten der Produktiven Datenbank zurückgreift und der Snapshot sonst inkonsistent wäre. Ein Snapshot wird durch ein einfaches DROP DATABASE entfernt.

Weiter kann man auch aus einem Datenbank Snapshot Datenänderungen zurücknehmen, indem man die Datenbank aus dem Snapshot wiederherstellt:
RESTORE DATABASE DATENBANK FROM DATABASE_SNAPSHOT = 'DBSNAPSHOT'
Dadurch werden alle im Snapshot enthaltenen Datenseiten in die eigentliche Datenbank zurückgeschrieben, bzw. überschrieben.

Wichtig: Wenn man dies auf einer Datenbank macht, die mit einer Transaktionsprotokollsicherung gesichert wird, muss danach eine Vollsicherung durchgeführt werden, da ab diesem Zeitpunkt keine konsistente Vollsicherung mehr vorliegt.

Für das Verwalten/Erstellen/Löschen von Snapshots benötigt man die Server-Sicherheitsrolle „dbcreator“.

Fazit:
Snapshots eigenen sich hervorragend für Auswertungszwecke von Daten aus einem Live System, sofern hier nicht das Motto gilt: „Traue keiner Statistik, die Du nicht selbst gefälscht hast“. :-)
Ansonsten eignet sich dies auch als „kleines Backup“ vor einer größeren Datenänderung.