I/O Freeze für SQL Snapshot Backups - SQL Server 2022 Features

Die Integration von Snapshot-Backups ermöglicht prompte Datenwiederherstellungen, unabhängig von der Größe der Datenbank, sei es für Datenbanken, Gruppen oder Server-Backups, einschließlich der Point-in-Time-Wiederherstellung.

Die Aktivierung dieses Merkmals setzt den I/O-Stream der Datenbank aus und wird durch die Anweisung ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON eingeleitet. Dieser Artikel vertieft das Verständnis des I/O-Stopps und hebt hervor, dass dieser ausschließlich den Input einschränkt, während weiterhin Leseoperationen auf der Datenbank ausgeführt werden können.

Am Ende dieses Artikels finden Sie ebenfalls die Codeausschnitte mit Erläuterungen auf Git.

Voraussetzungen

Hier erstellen wir zunächst eine Testdatenbank mit einer Tabelle und wenigen Zeilen.

-- Create database
CREATE DATABASE TestDatenbank

-- Create table
CREATE TABLE tabelle1 (spalte1 int)

-- Insert values
INSERT INTO tabelle1 VALUES (1)
INSERT INTO tabelle2 VALUES (2)
INSERT INTO tabelle3 VALUES (3)

Im Anschluss “frieren” wir die zuvor erstellte Datenbank ein und entfernen alle leeren Puffer aus dem Pufferpool und alle Columnstore-Objekte aus dem Columnstore-Objektpool.

-- Freeze database
ALTER DATABASE TestDatenbank SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON

-- Clean buffers
DBCC DROPCLEANBUFFERS

Status der Datenbank und lesende Operationen

Um den Status der Datenbank einzusehen und zu prüfen, ob diese tatsächlich eingefroren ist, rufen wir die Einträge aus der Systemdatenbank “sys.dm_server_suspend_status” ab und betrachten uns die Ausgabe.

-- Select data from table
SELECT * FROM sys.dm_server_suspend_status


-- OUTPUT
db_id               : 5
db_name             : TestDatenbank
suspend_session_id  : 86
suspend_time_ms     : 26
is_diff_map_cleared : True
is_write_io_frozen  : True

Wie die Ausgabe is_write_io_frozen mit Wert True bereits vermuten lässt, sind hier nur schreibende Operationen eingefroren.

Nun testen wir, ob weiterhin lesende Operationen auf der Datenbank möglich sind, indem wir die Werte aus der Tabelle der Testdatenbank abrufen.

-- Select data from table
SELECT * FROM tabelle1


-- OUTPUT
c1
1
2
3

Die Abfrage läuft normal durch und wir erhalten eine Ausgabe.

Schreibende Operationen

Nun versuchen wir eine schreibende Operation auf der Datenbank auszuführen, indem wir weitere Werte in die Tabelle einfügen.

-- Insert values to table
USE TestDatenbank
GO

INSERT INTO tabelle1 VALUES (4)

Wenn wir diese Abfrage ausführen stellen wir fest, dass diese noch keine Ergebnisse liefert. Dazu betrachten wir uns den Status der Abfrage mithilfe der System Stored Procedure sp_who2.

-- Execute sp_who2
EXEC sp_who2


-- OUTPUT
SPID	Status	    Command	
52   	SUSPENDED 	INSERT

Der Status unseres INSERT Statements steht auf SUSPENDED. Die Abfrage wurde also noch nicht ausgeführt. Folgend sind keine schreibenden Operationen auf einer eingefrorenen Datenbank mehr möglich. Dies liegt daran, dass schreibende Operationen, wie INSERTs gesperrt sind. Lesende Operationen sind nach der Abfrage weiterhin möglich.

Snapshot Backup erstellen

Nun erstellen wir das Snapshot Backup der Datenbank. Nach erfolgreichem Abschluss des Backups wird die Datenbank wieder aufgetaut und die schreibenden Operationen mit Status SUSPENDED nachträglich ausgeführt.

-- Create Backup
 BACKUP DATABASE TestDatenbank TO DISK=@fileName WITH METADATA_ONLY
 
 
 -- OUTPUT
Der E/A-Vorgang für die TestDatenbank-Datenbank wurde fortgesetzt. Es ist keine Benutzeraktion erforderlich.
Die Datenbank "TestDatenbank" hat Sperren in der Sitzung 86 aufgehoben.
Database "TestDatenbank" wurde ursprünglich für die Sicherungsmomentaufnahme in der Sitzung 86 angehalten und in Sitzung 86 erfolgreich fortgesetzt.
0 Seiten wurden für die TestDatenbank-Datenbank, Datei "TestDatenbank" für Datei 3, verarbeitet.
BACKUP DATABASE hat erfolgreich 0 Seiten in 0.003 Sekunden verarbeitet (0.000 MB/s).

Fazit

Um die Anwendung dieser neuen Funktion erfolgreich zu gestalten, ist es unerlässlich, die Feinheiten des I/O-Freezes in SQL-Snapshot-Backups zu verstehen. Wie in diesem Beitrag dargestellt, konzentriert sich der durch den Befehl ALTER DATABASE SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON ausgelöste I/O-Freeze gezielt auf Schreibvorgänge, wodurch Lesevorgänge weiterhin möglich sind.

Diese innovative Funktion ermöglicht sofortige Wiederherstellungen und Point-in-Time-Wiederherstellungen für Datenbanksicherungen auf Systemen, die SQL Server 2022 verwenden.

Wenn Sie mehr zu diesem Thema erfahren möchten, stehen Ihnen unsere erfahren Expert:innen bei Rückfragen zur Verfügung. Kontaktieren Sie uns dafür gerne über unser Kontaktformular. Wir freuen uns von Ihnen zu hören!

GitHub

Hier kommen Sie zu den Codebeispielen aus dem Artikel mit weiteren Erläuterungen.

GitHub SQL Features