Steuerung+C und Steuerung+V sind zwei Windows-Tastenkombinationen, die wir täglich vielfach verwenden, um eine Kopie einer bestehenden Datei zu erstellen. Doch was im Dateisystem so leicht von der Tastatur geht, scheint im Rahmen eines nächtlichen Lade- und Aufbereitungsprozesses ein wenig komplexer. So möchten wir am Beispiel einer als Blaupause dienenden Datenbank eine Möglichkeit erläutern, diese mittels eines Skriptes in beliebiger Anzahl zu vervielfältigen.
So ist beispielsweise ein sehr restriktives Szenario denkbar, bei dem die Entwicklung in einer Hauptdatenbank geschieht und deren Inhalte dann nachts in verschiedene Kinderdatenbanken für jede Region (Nord, Süd, Ost, West) transportiert werden. Die Einschränkung der Inhalte steuern wir per Prozedur oder im Verfahren des Abzugs aus den Vorsystemen. Und wie werden die strukturellen Änderungen an der Hauptdatenbank in die einzelnen Satelliten publiziert? Dies kann mit folgender Logik umgesetzt werden.
Das Ziel der Automatisierung ist die nächtliche Kopie einer Hauptdatenbank in vier verschiedene Satelliten für die Regionen Nord, Süd, West und Ost. Hierfür werden die *.mdf Datei der Blaupause, sowie beliebig viele zugehörige Log Dateien (*.log) im Dateisystem kopiert, mit einem neuen Namen versehen und als Unterbau für den neu erstellten Klon zur Verfügung gestellt. Soweit die Theorie. In der Praxis kann dies am Beispiel unserer Chair wie folgt aussehen.
Für den Zugriff auf die SQL Server Dateien der zu kopierenden Datenbank, muss zunächst deren Speicherort im Dateisystem ermittelt werden. Dies geschieht in der Prozedur per Auslesen der SQL Server Registry Inhalte:
Speicherort der Datenbank:
EXEC master.dbo.xp_instance_regread N'H- KEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @DirData output, 'no_output'
Speicherort der Logfiles:
EXEC master.dbo.xp_instance_regread N'H- KEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @DirLog output, 'no_output'
Hierbei werden jeweils in die beiden nvarchar Variablen @DirData und @DirLog die in der Registry hinterlegten Speicherorte für Datendatei und Logfile der Hauptdatenbank ausgelesen.
Da nun die Pfade zu den Dateien bekannt sind, muss vor dem eigentlichen Kopierprozess die Quelldatenbank offline gesetzt werden. Wäre sie noch aktiv, kann im Dateisystem nicht auf die Dateien zugegriffen werden. Daher müssen wir in unserem Kopierskript an das Offlinesetzen der Datenbank denken. Sie wird nach erfolgreichem Abschluss der Prozedur wieder automatisch online gesetzt.
select @vSourceDBStatus = state_desc from sys.databases where name = @vSourceDB begin if @vSourceDBStatus <> 'OFFLINE' set @vSQL = N'alter database ' + @vSourceDB + ' set offline with rollback immediate'; exec (@vSQL) end
Nun soll für jede Region der Chair eine Kopie der Quelldatenbank erstellt werden.
Hierfür benötigen wir Zugriff auf die ‚xp_cmdshell‘ – Funktionalität des SQL Servers. Ist diese deaktiviert, kann sie mit Hilfe des Kommandos
EXEC sp_configure'xp_cmdshell', 1 RECONFIGURE
aktiviert werden. Zunächst lesen wir in einen Cursor ‘cur_region’ alle vorhanden Regionen der Dimensionstabelle.
declare cur_region cursor for (select distinct region_de from T_DIM_05_02_Region)
Da nun die vier geographischen Separatoren in der Cursorvariable enthalten sind, können wir diese in einer Schleife abrufen und pro Ausprägung eine Kopie von Daten und Logfile(s) erstellen.
OPEN cur_region FETCH NEXT FROM cur_region INTO @vRegionID WHILE @@FETCH_STATUS = 0 BEGIN -- Zusammenfügen des neuen Datenbanknamens SET @vNewDB = replace(@vSourceDB, '_Master', '') + '_' + @vRegionID -- Prüfen, ob dieser neue Name derzeit schon existiert. -- Falls ja, löschen der existenten Datenbank IF EXISTS(SELECT * FROM sys.databases WHERE name = @vNewDB) BEGIN SET @vSQL = 'DROP DATABASE ' + @vNewDB EXEC (@vSQL) END -- Auslesen des existenten Dateinamens DECLARE cur_dbfile CURSOR FOR ( SELECT physical_name FROM sys.master_files WHERE database_id = DB_ID(N'' + @vSourceDB + '') AND type_desc = 'ROWS' ) OPEN cur_dbfile FETCH NEXT FROM cur_dbfile INTO @vSourceDBFileName WHILE @@FETCH_STATUS = 0 BEGIN -- Zusammenfügen des neuen Dateinamens SET @vNewDBFileName = REPLACE(@vSourceDBFileName, '.mdf', '_' + @vRegionID + '.mdf') SET @vDBFileName = @vNewDBFileName -- Kopieren der Datei SET @XPcommand = 'copy ' + @vSourceDBFileName + ' ' + @vNewDBFileName exec master..xp_cmdshell @XPcommand FETCH NEXT FROM dbfile_cursor INTO @vSourceDBFileName END CLOSE cur_dbfile DEALLOCATE cur_dbfile FETCH NEXT FROM cur_region INTO @vRegionID END CLOSE cur_region DEALLOCATE cur_region
Analog hierzu kann der Name und Speicherot von einem oder mehreren Logfiles der Quelldatenbank kopiert und mit dem Kürzel der Region versehen werden.
Nach der erfolgten Kopie der gewünschten Dateien, können die neuen Datenbanken im SQL Server hinzugefügt werden.
set @vSQL = 'Create Database ' + @vNewDB + ' on (Filename = ''' + @vDBFileName + '''), (Filename = ''' + @vLogFileName + ''') for attach'
Je nachdem, ob in der Quelldatenbank mehrere Logfiles existierten, können diese über eine Schleife ebenfalls in die Satelliten publiziert werden.
WHILE @@FETCH_STATUS = 0 BEGIN SET @vAddLogfileSQL = 'ALTER DATABASE ' + @vNewDB + ' ADD FILE ( NAME = N' + @vLogName + ', FILENAME = N' + @vLogFileName + ' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [PRIMARY]' EXEC (@vAddLogfileSQL) FETCH NEXT FROM ... END
Da es sich bei der zu kopierenden Hauptdatenbank ganz bestimmt um eine mit DeltMaster Modeler konfigurierte Datenbank handelt, prüfen wir am Ende noch unter T_Model_Parameters den eingestellten Namen der zugehörigen OLAP Datenbank und ändern diesen ebenfalls auf den jeweiligen Klon der Masterinhalte.
Am Schluss wird die Quelldatenbank dann wieder online gestellt und unsere eine Masterdatenbank hat erfolgreich vier Kinder bekommen.
set @vSQL = N'alter database ' + @vSourceDB + ' set online';
exec (@vSQL)
In größeren Datenbanken macht es sicherlich Sinn, vor dem Kopieren noch die Fakten- und Dimensionstabellen zu leeren und diese erst im Anschluß mit den jeweilig korrekten Inhalten zu befüllen.
Auf Nachfrage kann gerne das komplette Skript zur Verfügung gestellt werden, worin auch dieses vorige „Abspecken“ der Quelldatenbank berücksichtigt ist.
