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_DEVLediglich 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 = 5Hierbei 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_ImportIDDamit 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
GOSSIS Variablen
