Wenn man größere Datenbanken hat, kann es durchaus vorkommen, das mehrere Leute an derselben Datenbank arbeiten, mit mehrfachen Kopien und Entwicklungsumgebungen. Zwischen diesen Datenbanken kommt es von Natur aus sehr schnell zu Schiefständen, was sowohl die Daten selbst als auch die Struktur angeht. Arbeiten zum Beispiel zwei Personen am selben Datenbankobjekt, kann es sein, dass dies erst viel zu spät auffällt, nämlich dann, wenn das Ergebnis auf die produktive Datenbank übertragen werden soll und einer von beiden Prozessen nicht mehr funktioniert.

In diesem Blogbeitrag wird gezeigt, wie mit zwei einfachen Mitteln diese Situation umgangen werden kann. Das Erste sind die On-Board-Mittel von Microsoft SSIS, welche als erstes unter die Lupe genommen werden sollen. Das Zweite, und gerade am Anfang sicherlich deutlich schwierigere, ist das konsequente und saubere Arbeiten mit Change Skript, also den Skripten, in welchen die zu ändernden oder erstellenden Objekte in SQL beschrieben sind.

Das SSIS Paket

In der folgenden Abbildung wird das SSIS-Paket und seine einzelnen Komponenten erklärt:

Abbildung 1: Gesamtaufbau des SSIS Paketes

Datenbank sichern

In diesem Schritt wird ein Backup der aktuell produktiven Datenbank erzeugt und als Datei auf einer lokalen Festplatte abgelegt. Der Speicherort lässt sich beliebig anpassen und auch eine Speicherung in einem Azure Blob Storage wäre denkbar.

Abbildung 2: Definition des Datenbank Backups

Über die unterschiedlichen Arten des Backups haben wir bereits einen Blogbeitrag geschrieben.
Für den vorliegenden Fall benötigen wir ein vollständiges Backup, weswegen sich ein SSIS Baustein anbietet.
Zunächst wird aus der SSIS Toolbox der Task „Datenbank sichern gewählt“.

Abbildung 3: Auswählen des Datenbank Backup Tasks

Diesen konfigurieren wir für eine bestimmte Datenbank und das Sichern auf die Festplatte

Abbildung 4: Konfiguration des Backups

Da ohne weitere Einstellungen das SSIS Paket einen Zeitstempel an die Datei anhängen würde, und wir diese nicht ohne weiteres in kommenden Schritten auswählen könnten, wird ein fixer Name vergeben.
Wichtiger Hinweis: Der gezeigte Prozess sollte keinesfalls als Ersatz für eine solide Backupstrategie angesehen werden!
Zudem wird dem Task noch mitgegeben, dass er eventuell vorhandene Sicherungsdateien überschreiben soll.

Abbildung 5: Konfiguration des Backupzieles

Als letzter, zwar optionaler, aber aus meiner Sicht zu empfehlender Punkt wird das Paket noch so eingestellt, dass die erzeugte Datei komprimiert wird, um Speicherplatz zu sparen.

Abbildung 6: Komprimierung der Backup Datei

Datenbank löschen

In diesem Schritt wird die vorhandene Entwicklungsdatenbank gelöscht.

Abbildung 7: Löschen der aktuellen Entwicklungsumgebung

Um sicherzustellen, dass die Datenbank auch gelöscht werden kann und nicht noch aktive Verbindungen offen sind, werden vor dem Löschen alle Verbindungen beendet. Dies lässt sich mit dem eigentlichen Löschbefehl in einem SQL kombinieren.

USE [master]; 

DECLARE @kill varchar(8000) = '';  
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
FROM sys.dm_exec_sessions 
WHERE database_id = db_id('Database_DEV') 

EXEC(@kill); 
DROP DATABASE IF EXISTS Database_DEV

Lediglich der Name der Entwicklungsdatenbank muss in diesem Skript noch angepasst werden.

Datenbank wiederherstellen

Nachdem nun erfolgreich die produktive Datenbank gesichert und die Entwicklungsdatenbank gelöscht wurde, kann das aktuelle Backup an der Stelle der Entwicklungsdatenbank wiederhergestellt werden.

USE [master] 
RESTORE DATABASE [Database_DEV]  
FROM DISK = N'xxxxxxxx\BackupPrdDB.bak'  
WITH FILE = 1,  
MOVE N'Database_prod' TO N'C:\Program Files\Microsoft SQL
Server\MSSQL14.SQL2017\MSSQL\DATA\Database_DEV.mdf',  
MOVE N'Database_prod_log' TO N'C:\Program Files\Microsoft SQL
Server\MSSQL14.SQL2017\MSSQL\DATA\Database_DEV_log.ldf',  
NOUNLOAD,  
STATS = 5

Hierbei muss darauf geachtet werden, dass der Dateiname sowohl für die Logs als auch die Datenbankdateien angepasst werden muss.

ImportID erzeugen und auslesen

Damit in einer späteren Übersicht nachvollzogen werden kann wann ggf. Fehler auftreten, wird eine ID erzeugt, welche für die Imports eindeutig ist.
Bevor eine ReportingID erzeugt werden kann, ein kurzer Auflug in das Logging und späteres Reporting, für welches eine neue Datenbank angelegt werden muss. Diese soll den Namen „SSIS_Logging_Database“ tragen. (Das Skript zur Erzeugung der Datenbank befindet sich im Anhang).
Für das Logging der Datenbank und auch das spätere Reporting werden Informationen aus dem SSIS Paket in die neu angelegte SQL-Datenbank geschrieben.

Abbildung 8: Konfiguration des SSIS Loggings

Eine genauere Anleitung hat meine Kollege Hr. Werther geschrieben:
https://www.bissantz.de/know-how/crew/ssis-logging-in-deltamaster/

Abbildung 9: Erzeugen und Auslesen der ImportID

An dieser Stelle sollen die SSIS Variablen für dieses Paket eingeführt werden.
(Siehe Anhang SSIS Variablen)
Diese werden an der jeweils benötigten Stelle noch einmal näher in den Fokus gerückt.
Um die ImportID zu erzeugen, wird ein neuer Eintrag in die Tabelle T_Config_ImportID erzeugt.

INSERT INTO  
T_Config_ImportID  
(DateID)  
VALUES  
(GETDATE())

Anschließend wird die maximale ImportID wieder ausgelesen, um sie im Weiteren zu verwenden.

SELECT 
MAX(ImportID) 
FROM 
T_Config_ImportID

Damit die Ergebnisse im SSIS Paket korrket übernommen werden sind noch 2 kleine Einstellungen bzw. Zuweisungen nötig.

Abbildung 10: Konfiguration des ResultSets

Abbildung 11: Zuweisung des ResultSets zur Variablen

Exkurs: Struktur der Change Skripte

Damit im Folgenden das Einspielen der Skripte und auch das hierauf aufbauende Reporting korrekt funktioniert, ist eine stringente Benennung der Dateien notwendig; ebenso, dass pro Skript nur ein Objekt geändert wird. Die Benennung findet anhand der Folgenden Regel statt:
Skriptnummer – Projekt/System – Ersteller -Objektname – Beschreibung
Beispiel: 0001 – OM – WGN – V_FACT_Test – Create VIEW
Auf Basis dieser Regel wird ein Großteil der Variablen gesetzt.

Einspielen der Change Skripte

Im letzten Schritt werden die im definierten Ordner abgelegten Change Skripte eingelesen und auf der Datenbank ausgeführt.

Abbildung 12: ForEach Loop zum einlesen der Skripte

Dies wird erreicht, indem die Quelle für den SQL-Task eine Datenverbindung aufweist, und keine Variable oder Text verwendet.

Abbildung 13: Konfiguration der SQL-Anweisung

Neben dem Skript, welches ausgeführt wird, wird auch das Logging in die Datenbank geschrieben. Dies wird über die Variable SQL erreicht.

Abbildung 14: Einfügen des Logging Datensatzes

Ist das Skript erfolgreich, wird es in den Ordner Tested verschoben. Treten während des Ausführens Fehler auf, wird es in den Ordner Failed verschoben. Tritt ein Fehler auf, so würde im Normalfall das ganze Paket mit einem Fehler abbrechen. Um dies zu vermeiden erfordert es noch einen letzten Konfigurationsschritt. Hierfür werden die Eigenschaften des Loop Containers angepasst.

Abbildung 15: Konfiguration des Failovers

Mit der Einstellung ForceExcecutionResult = Success, wird erreicht das das Paket auch dann weiterläuft, wenn es zu einem „erwartetem“ Fehler kommt.

Abbildung 16: Forced Success für Task

Zusammenfassung

In diesem Blogbeitrag wurde aufgezeigt, wie man mit Changeskripten und on Boardmitteln von Microsoft (SSIS) aus der aktuellen produktiven Datenbank eine Testumgebung mit allen geänderten Objekten aufbauen kann.

Anhang

SSIS Logging Datenbank

USE [master] 
GO 

CREATE DATABASE [SSIS_Logging_Database] 
 CONTAINMENT = NONE 
 ON PRIMARY  
( NAME = N'SSIS_Logging_Database', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SSIS_Logging_Database.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ) 
 LOG ON  
( NAME = N'SSIS_Logging_Database_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\SSIS_Logging_Database_log.ldf' , SIZE = 30720KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB ) 
GO 

ALTER DATABASE [SSIS_Logging_Database] SET COMPATIBILITY_LEVEL = 140 
GO 
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 
begin 
EXEC [SSIS_Logging_Database].[dbo].[sp_fulltext_database] @action = 'enable' 
end 
GO 

ALTER DATABASE [SSIS_Logging_Database] SET ANSI_NULL_DEFAULT OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET ANSI_NULLS OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET ANSI_PADDING OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET ANSI_WARNINGS OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET ARITHABORT OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET AUTO_CLOSE OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET AUTO_SHRINK OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET AUTO_UPDATE_STATISTICS ON  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET CURSOR_CLOSE_ON_COMMIT OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET CURSOR_DEFAULT GLOBAL  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET CONCAT_NULL_YIELDS_NULL OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET NUMERIC_ROUNDABORT OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET QUOTED_IDENTIFIER OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET RECURSIVE_TRIGGERS OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET DISABLE_BROKER  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET DATE_CORRELATION_OPTIMIZATION OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET TRUSTWORTHY OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET ALLOW_SNAPSHOT_ISOLATION OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET PARAMETERIZATION SIMPLE  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET READ_COMMITTED_SNAPSHOT OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET HONOR_BROKER_PRIORITY OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET RECOVERY SIMPLE  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET MULTI_USER  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET PAGE_VERIFY CHECKSUM  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET DB_CHAINING OFF  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET TARGET_RECOVERY_TIME = 60 SECONDS  
GO 
ALTER DATABASE [SSIS_Logging_Database] SET DELAYED_DURABILITY = DISABLED  
GO 

EXEC sys.sp_db_vardecimal_storage_format N'SSIS_Logging_Database', N'ON' 
GO 

ALTER DATABASE [SSIS_Logging_Database] SET QUERY_STORE = OFF 
GO 

USE [SSIS_Logging_Database] 
GO 

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON; 
GO 
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF; 
GO 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY; 
GO 
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0; 
GO 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY; 
GO 
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON; 
GO 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY; 
GO 
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF; 
GO 
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY; 
GO 
USE [SSIS_Logging_Database] 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE TABLE [dbo].[T_Config_Logging]( 
[ImportID] [int] NOT NULL, 
[ScriptID] [varchar](4) NULL, 
[SystemID] [varchar](50) NULL, 
[UserID] [varchar](3) NULL, 
[ObjectID] [varchar](250) NULL, 
[Description] [varchar](250) NULL 
) ON [PRIMARY] 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE VIEW [dbo].[V_Config_Double_Objects] AS 
SELECT 
a.ImportID, 
a.ScriptID, 
a.SystemID, 
a.UserID, 
a.ObjectID, 
a.Description 
FROM 
[T_Config_Logging] a 
WHERE 
EXISTS 
( 
SELECT  
b.[ImportID] 
,b.[ObjectID] 
FROM  
[dbo].[T_Config_Logging] b 
WHERE 
a.ImportID = b.ImportID 
AND	 
a.ObjectID = b.ObjectID 
GROUP BY	 
b.[ImportID] 
,b.[ObjectID] 
HAVING  
COUNT(*) > 1 
) 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE VIEW [dbo].[V_D_DIM_System] AS 
SELECT DISTINCT 
SystemID 
FROM  
dbo.T_Config_Logging 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE VIEW [dbo].[V_D_DIM_User] AS 
SELECT DISTINCT 
UserID 
FROM  
dbo.T_Config_Logging 
GO 

SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 

CREATE TABLE [dbo].[T_Config_ImportID]( 
[ImportID] [int] IDENTITY(1,1) NOT NULL, 
[DateID] [smalldatetime] NOT NULL 
) ON [PRIMARY] 
GO 

ALTER TABLE [dbo].[T_Config_ImportID] ADD CONSTRAINT [DF_T_Config_ImportID_DateID]  
DEFAULT (getdate()) FOR [DateID] 
GO 

USE [master] 
GO 

ALTER DATABASE [SSIS_Logging_Database] SET READ_WRITE  
GO

SSIS Variablen