Gespiegelte Datenbanken Pflegen und Sichern
Seit SQL Server 2005 gibt es die Möglichkeit, Datenbanken live zu spiegeln, und mit der Spiegelung stets ein Hot-Standby der Datenbank mitzuführen, falls es auf dem eigentlichen Datenbankserver zur Katastrophe kommt.
Die Produktions-Datenbank, mit der die Nutzer arbeiten, wird dabei Prinzipal genannt, die gespiegelte Version heißt Mirror oder Spiegel. Es kann auch noch eine dritte Rolle hinzu kommen, der Zeuge (bzw. Witness), der vollautomatisch überwachen kann, ob der Prinzipal noch verfügbar ist, und andernfalls ein sogenanntes Failover auslöst, also den bisherigen Mirror zur eigentlichen Produktions-Datenbank befördert.
Etwas gewöhnungsbedürftig ist dabei, daß man sich nur zum Prinzipal verbinden und mit ihm arbeiten kann. Der Mirror, der ja den identischen Datenzustand besitzt, ist für jegliche Anfragen tabu, er präsentiert sich stets im Zustand "wird wiederhergestellt".
Man kann die Prinzipal-Datenbank problemlos sichern, aber sobald man versucht, die Datenbank zu pflegen (also z.B. das Transaktionslog verkleinern, oder ein Integritätscheck mit Selbstreparatur), bekommt man eine Fehlermeldung, wenn die Spiegelung aktiv ist.
Es ist schon eine ziemliche Herausforderung, diese Aufgabe mit einem Script zu Automatisieren, und weil es im Web nicht so ohne Weiteres beschrieben wird, und ich selbst länger forschen mußte, beschreibe ich hier einmal den Weg, wie dies zu Bewerkstelligen ist.
Wir wollen also im Folgenden einige Schritte per Script automatisieren:
- Vorübergehendes Beenden der Spiegelung
- Volles Pflegeprogramm über die Datenbank laufen lassen
- Datenbank in optimal konditioniertem Zustand sichern
- Datenbanksicherung zum Mirror-Server übertragen
- Spiegelung wiederaufnehmen
Ich habe dazu ein Sql Server Business Inteligence Package erstellt. Lieber wäre mir gewesen, auf dem SQL Server ein Wartungspaket zu erzeugen, jedoch erlaubt dieses offenbar keine Dateioperationen (Dateikopien von A nach B). Die extended stored procedure zum Ausführen eines DOS-Kommandos ist auf den meisten SQL Server-Systemen aus Sicherheitsgründen deaktiviert, daher habe ich mich für das SSBS-Package entschieden.
Man öffne also entweder Microsoft Visual Studio, oder das SQL Server Business Intelligence Developer Studio, welches unter Start / Programme / Microsoft SQL Server 2005 zu finden ist, und erstelle ein leeres Projekt. In dieses Projekt stellen wir jetzt mithilfe der Toolbox 4 Tasks "SQL ausführen", und 2 Tasks "Dateisystem" in folgender Anordnung hinein:
Ins erste Ablaufsteuerungselement packen wir dabei einfach den Aufruf einer stored procedure, die wir selbst bauen:
USE master
GO
IF EXISTS ( SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].spIntensivPflegeMusterDB')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].spIntensivPflegeMusterDB
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROC dbo.spIntensivPflegeMusterDB
AS
SET NOCOUNT ON
-- Spiegelung beenden, falls sie im Moment läuft:
IF EXISTS ( SELECT 1
FROM sys.database_mirroring LEFT OUTER JOIN sys.databases
ON sys.database_mirroring.database_id = sys.databases.database_id
WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR')
AND LOWER(sys.databases.name) = 'musterdb'
)
BEGIN
ALTER DATABASE MusterDB SET PARTNER OFF
END
ALTER DATABASE MusterDB SET SINGLE_USER WITH ROLLBACK AFTER 5
DBCC CHECKDB (MusterDB, REPAIR_REBUILD) WITH NO_INFOMSGS
ALTER DATABASE MusterDB SET MULTI_USER
BACKUP LOG MusterDB WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (MusterDB)
ALTER DATABASE MusterDB SET RECOVERY FULL;
BACKUP DATABASE MusterDB
TO DISK = '\\SQLMASTER\BACKUPS\musterdb.bak'
WITH FORMAT;
BACKUP LOG MusterDB
TO DISK = '\\SQLMASTER\BACKUPS\musterdb_log.bak'
WITH FORMAT
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Warum eine stored procedure draus bauen ? Nun, wenn man den enthaltenen Code so in eine SQL-Task eingibt, bricht das Package mit einem Fehler ab, und ich habe anhand der Meldung das Gefühl, daß die eine oder andere Zeile ihm etwas zu lang ist. Mit dem Weg über die stored procedure geht es.
Am Anfang des Codes prüfen wir, ob die Spiegelung gerade läuft, und falls ja, setzen wir sie mit einem Befehl außer Kraft.
Danach muß die Datenbank vorübergehend in den SINGLE_USER-Mode gesetzt werden, weil die CHECKDB-Funktion dies erfordert. DBCC CHECKDB erledigt den Integritäts-Check mitsamt Auto-Reparatur (dabei werden interne Strukturfehler beseitigt). Direkt nach diesem Befehl gehen wir auch wieder in den MULTI_USER-Mode.
Der BACKUP LOG - Befehl dient zur Verkleinerung des Transaktions-Logs, und der DBCCC SHRINKDATABASE-Befehl verkleinert die Datenbank (sofern möglich). Letzteres macht Sinn bei Datenbanken, in denen auch Löschoperationen laufen. Der SET RECOVERY FULL - Befehl dient der Sicherheit, weil ohne diese Sicherungsart nach dem Recovery die Spiegelung nicht mehr anläuft.
Am Schluß folgen die beiden BACKUP - Befehle, die zum einen die Datenbank, und zum anderen das Log im Dateisystem sichern.
Wenn man diesen Code so wie er ist ausführt, dann hat man anschließend in der master-Datenbank die gewünschte stored procedure.
In das Fenster der ersten Task brauchen wir dann nur noch den Aufruf dieser stored procedure rein zu packen:
Danach folgen zwei Dateioperations-Tasks, mit denen wir unsere frisch erstellten Backups vom Prinzipalserver auf den Mirror-Server kopieren.
Die nächste SQL-Task ("Restore auf dem Mirror") sorgt dann dafür, daß dieses Backup auf dem Mirror-Server restored wird.
RESTORE DATABASE MusterDB
FROM DISK = '\\SQLSLAVE\BACKUPS\musterdb.bak'
WITH REPLACE, NORECOVERY
RESTORE LOG MusterDB
FROM DISK = '\\SQLSLAVE\BACKUPS\musterdb_log.bak'
WITH REPLACE, NORECOVERY
Die nächste Task ("Spiegelung wiederherstellen Mirrorseitig") ist der Befehl, der auf dem Mirror-Server als erstes aufgerufen werden muß, um die Spiegelung wieder aufzunehmen:
ALTER DATABASE MusterDB SET PARTNER = N'TCP://SQLMASTER.mydomain.local:5022'
SQLMASTER.mydomain.local ist dabei der Prinzipal-Server, und die 5022 ist die Portnummer des Endpunkts, über den die Spiegelung läuft (5022 ist ein Standardwert).
Die nächste SQL-Task ("Spiegelung wiederherstellen Prinzipalseitig") ist der letzte Schritt in der Kette und sorgt dafür, daß die Spiegelung nun endgültig wieder losgeht. Aufrufen bitte auf dem Prinzipal-Server:
ALTER DATABASE MusterDB SET PARTNER = N'TCP://SQLSLAVE.mydomain.local:5022'
GO
ALTER DATABASE MusterDB SET WITNESS = N'TCP://SQLZEUGE.mydomain.local:5022'
GO
WITNESS ist dabei die Instanz eines Zeugen, der aber nicht zwingend erforderlich ist.
Jetzt noch einige Tips zum Debuggen, um herauszufinden, ob eine Spiegelung läuft oder nicht. Wir können zunächst feststellen, ob ein Endpunkt eingerichtet ist auf unserem Server (Prinzipal, Mirror oder Witness) mit folgendem Befehl:
SELECT e.name, e.protocol_desc, e.type_desc, e.role_desc, e.state_desc,
t.port, e.is_encryption_enabled, e.encryption_algorithm_desc,
e.connection_auth_desc
FROM sys.database_mirroring_endpoints e JOIN sys.tcp_endpoints t
ON e.endpoint_id = t.endpoint_id
Möchten wir den Zustand der Spiegelungen verschiedener Datenbanken auf unserer Serverinstanz anschauen (Prinzipal oder Mirror), dann gelingt das mit diesem Befehl:
SELECT d.name, d.database_id, m.mirroring_role_desc,
m.mirroring_state_desc, m.mirroring_safety_level_desc,
m.mirroring_partner_name, m.mirroring_partner_instance,
m.mirroring_witness_name, m.mirroring_witness_state_desc
FROM sys.database_mirroring m JOIN sys.databases d
ON m.database_id = d.database_id
WHERE mirroring_state_desc IS NOT NULL
Wenn im Ergebnis die Spalte "mirroring_state_desc" auf SYNCHRONIZED steht, dann haben wir gewonnen und die Spiegelung läuft wieder.
|
