Wenn die mitgelieferte Replikation oder „Linked Server“ des SQL-Servers nicht zum Replizieren von Tabellen genutzt werden können, sondern eine Replikation von Hand eingerichtet werden muss, ist das meist sehr aufwändig: Für jede replizierte Tabelle werden alle Konfigurationen einzeln editiert. Dieser Beitrag stellt eine Methode vor, um ein dynamisches Replikationssystem für Tabellen mit Hilfe von SSIS-Paketen einzurichten.

In immer mehr Kundenumgebungen ist es nicht mehr möglich, mit „Linked Server“ SQL-Abfragen, z. B. zwischen Test und Produktivsystem, abzusetzen. Zusätzlich existieren meist keine Automatismen, um Daten von einem Produktivsystem auf ein Testsystem zu kopieren. Auch ein automatisches Backup und Restore ist in der Regel schwierig, da man dabei auf einem Test-/Entwicklungssystem ggf. den Entwicklungsstand überschreiben würde.

Dadurch sind die Datenstände auf den Test-/Entwicklungssystemen nicht immer aktuell und stellen für einen Test keine valide Grundlage dar.

Wenn „Linked Server“-Zugriffe nicht erlaubt sind, muss man sich anderer Bordmittel vom SQL-Server bedienen. Meist fällt hier die Wahl auf SSIS-Pakete. Allerdings erfordert ein manuell editiertes SSIS-Paket, dass bei einem Datenfluss-Task die Eingangs- und Ausgangsspalten exakt definiert werden. Für eine Replikation, die einmalig erfolgen soll und ggf. hunderte von Tabellen umfasst, kann das ein sehr großer Aufwand sein.

Innerhalb der Objekte des SSIS-Tool-Katalogs gibt es ein Werkzeug, um Datenbankobjekte zu kopieren: den „Transfer SQL Server Objects Task“. Damit können Objekte einer SQL-Server-Datenbank zwischen zwei Systemen kopiert werden. Wie alle Objekte im SSIS verfügt auch der „Transfer SQL Server Objects Task“ über eine Vielzahl von Eingabeparametern, welche dynamisch editiert und vorkonfiguriert werden können. Hierzu zählt bislang allerdings nicht die Liste der gewünschten Tabellen, die repliziert werden sollen.

Im Folgenden wird eine Lösung des Problems dargestellt, um mit Hilfe des „Script-Task“ ein virtuelles SSIS-Paket zu bauen und diesem dann die Eingabeobjekte zu übergeben. Somit ist es möglich, die Replizierung von Objekten ohne spätere Anpassung des SSIS-Pakets durch eine Datenbanktabelle dynamisch zu steuern.

Steuerungstabelle

Innerhalb einer beliebigen Datenbank wird eine Steuerungstabelle erzeugt:

CREATE TABLE [dbo].[T_ReplicationControl](
        [ReplicationControlID] [INT] IDENTITY(1,1) NOT NULL,
        [SortNumberID] [INT] NOT NULL,
        [SourceServerName] [VARCHAR](512) NOT NULL,
        [SourceDatabaseName] [VARCHAR](512) NOT NULL,
        [SourceTableSchemaName] [VARCHAR](512) NOT NULL,
        [SourceTableName] [VARCHAR](512) NOT NULL,
        [DestinationServerName] [VARCHAR](512) NOT NULL,
        [DestinationDatabaseName] [VARCHAR](512) NOT NULL,
        [AppendReplaceDrop] [INT] NOT NULL,
        [IncludeDependentObjects] [INT] NOT NULL,
        [ActiveFLag] [INT] NULL,
        [SaveToFile] [INT] NOT NULL,
CONSTRAINT [PK_T_S_ImportTablesFromProductionSystem]
        PRIMARY KEY CLUSTERED ( [ReplicationControlID] ASC )

• ReplicationControlID: Identitätsspalte
• SortNumberID: Anhand dieser ID wird die Reihenfolge der Abarbeitung von Objekten vorgegeben (ggf. wichtig bei Objekten, die aufeinander referenzieren)
• SourceServerName, SourceDatabaseName, SourceTableSchemaName, SourceTableName: Adressinformationen des Quellobjekts
• DestinationServerName, DestinationDatabaseName: Adressinformationen des Zielobjekts (da im vorgestellten Verfahren Objekte ausschließlich kopiert werden, ist der Schema- und der Tabellenname des Zielobjekts immer gleich dem des Quellobjekts)
• AppendReplaceDrop: Gibt an, ob mit der Kopie alle Daten im Zielsystem gelöscht und neu erstellt werden sollen, oder ob – mit der Kopie des Deltas – ein Abgleich der Daten zwischen Quell- und Zielsystem erfolgen soll.
  1 = Append (Kopie des Deltas)
  2 = Replace (Löschen und Ersetzen der Daten)
  3 = Drop and recreate (Löschen der Tabelle, Neuerstellen und Kopieren aller Daten)
• IncludeDependentObjects: Gibt an, ob abhängige Objekte mit erstellt werden sollen.
• ActiveFLag: Gibt an, ob der Eintrag aktiv ist und bei der nächsten Ausführung beachtet wird.
• ActiveFLag: Gibt an, ob zusätzlich zur Replikation die Konfiguration als ein SSIS-Paket abgespeichert werden soll. Hier wird dann der Pfad eingetragen, unter dem das SSIS-Paket abgespeichert werden soll. Ein leeres Feld oder NULL bedeutet, dass auf das Speichern verzichtet werden soll.

System-Modelldefinition

Ausgangssystem

Das System besteht aus einem SSIS-Datenfluss-Task, welcher die Steuerungstabelle ausliest, und einem Schleifendurchlauf, der die in der Steuertabelle enthaltenen Tabellen nacheinander vom Quell- ins Zielsystem kopiert. Dazu wird, wie in Abbildung 1 sichtbar, ein SSIS-Script-Task benutzt.

Abb. 1: Schaubild zur Ablaufsteuerung

Parameter und Variablen

Um später die Datenquelle für die Steuertabelle variabel außerhalb des Paketes steuern zu können, legt man für den Connection-String der OLEDB-Verbindung „Control_Connection“ einen Projektpa-rameter an. Dieser muss dann in der OLEDB-Verbindung über die Eigenschaften referenziert werden.

Abb. 2: Referenzierung des Projektparameters

Im weiteren Verlauf werden verschiedene Variablen benötigt:

Abb. 3: Variablen für den weiteren Verlauf

Auslesen der Steuertabelle

Im SSIS-Datenfluss-Task „Get ReplicationControl“ wird die Steuertabelle in ein Recordset geschrieben. Das Ziel ist die angelegte „Control_ResultSet“-Variable. Wichtig ist hierbei die Reihenfolge der zugewiesenen Spalten.

Abb. 4: Steuertabelle im Recordset

Abb. 5: Ziel ist die angelegte „Control_ResultSet“-Variable

Abb. 6: Reihenfolge der zugewiesenen Spalten

Schleifendurchlauf

Im „Foreach-Schleifencontainer“ wird die Variable „Control_ResultSet“ ausgelesen und jede Zeile des Resultsets den vorher definierten Variablen zugewiesen. Dafür ist es jetzt wichtig zu wissen, welche Reihenfolge die Eingabespalten der Recordset-Variable haben, um diese den einzelnen SSIS-Variablen korrekt zuzuordnen.

Abb. 7: Variablenzuordnung im „Foreach-Schleifencontainer“

Script-Task

Für die Übergabe an den „Script-Task“ werden dann die Variablen aus dem Variablenkatalog ausgewählt.

Abb. 8: Auswahl von Variablen aus dem Variablenkatalog

Im anschließenden SSIS-Script-Task-Editor müssen außerdem noch 3 zusätzliche Verweise eingerichtee werden.

Abb. 9: Zusätzliche Verweise im SSIS-Script-Task-Editor

Script

Im ersten Teil des Scripts, werden die SSIS-Variablen in .NET-Variablen konvertiert. Danach werden neue Variablen definiert, um anschließend das neue SSIS-Paket zu definieren. Im Zielpaket wird es immer eine Quell- und eine Ziel-Verbindung geben, deshalb müssen Variablen für die Connection-Strings angelegt werden. Dazu kommen Objekte zum Aufbau des SSIS-Pakets.

Bei der Definition der String-Collection für die replizierte Tabelle ist es wichtig, die Tabelle mit Schema anzugeben und beides, Schema und Tabelle, in eckige Klammern zu setzen.
Die Parameter für das Objekt „tssoTask“ spiegeln die Parameteroptionen des „Transfer SQL Server Objects Task“ innerhalb der SSIS-Objekteigenschaften wider.

Abschließend werden die Parameter für das SSIS-Objekt „Transfer SQL Server Objects Task“ definiert.
Mit dem Statement „pkg.Execute();” wird dann das Paket ausgeführt.

Damit im Falle eines Fehlers das ausgeführte Paket betrachtet werden kann, wurde der Abschnitt „Save To File“ geschaffen. Hier wird das Paket in dem hinterlegten Pfad abgespeichert, soweit dieser in der Steuerungstabelle vorgesehen ist. Der Pfad ist im Beispiel als „C:\temp“ deklariert.

public void Main()
{
    // Zuweisung der SSIS Variablen zu .NET Variablen
    String SourceServerName = Dts.Variables["User::Control_SourceServerName"].Value.ToString();
    String SourceDatabaseName = Dts.Variables["User::Control_SourceDatabaseName"].Value.ToString();
    String SourceTableSchemaName = Dts.Variables["User::Control_SourceTableSchemaName"].Value.ToString();
    String SourceTableName = Dts.Variables["User::Control_SourceTableName"].Value.ToString();
    String DestinationServerName = Dts.Variables["User::Control_DestinationServerName"].Value.ToString();
    String DestinationDatabaseName = Dts.Variables["User::Control_DestinationDatabaseName"].Value.ToString();

    int AppendReplaceDrop = Convert.ToInt32(Dts.Variables["User::Control_AppendReplaceDrop"].Value);
    int IncludeDependentObjects = Convert.ToInt32(Dts.Variables["User::Control_IncludeDependentObjects"].Value);
    int SaveToFile = Convert.ToInt32(Dts.Variables["User::Control_SaveToFile"].Value);

    // Hilfsvariablen für die Connection-Strings der Quell- und Zielsysteme
    String SourceConnStr = String.Empty;
    String DestConnStr = String.Empty;

    DataTable dt = new DataTable();
    Package pkg = new Package();
    Connections conns = pkg.Connections;

    StringCollection tablesColl = new StringCollection();
    // Wichtig sind hier die eckigen Klammern
    tablesColl.Add("[" + SourceTableSchemaName + "].[" + SourceTableName + "]");

    SourceConnStr = "SqlServerName=" + SourceServerName + ";UseWindowsAuthentication=True;UserName=;";
    DestConnStr = "SqlServerName=" + DestinationServerName + ";UseWindowsAuthentication=True;UserName=;";

    ConnectionManager cmSource = pkg.Connections.Add("SMOServer");
    cmSource.Name = "SMOSourceServer";
    cmSource.ConnectionString = SourceConnStr;

    ConnectionManager cmDestination = pkg.Connections.Add("SMOServer");
    cmDestination.Name = "SMODestinationServer";
    cmDestination.ConnectionString = DestConnStr;

    Executable execPackage = pkg.Executables.Add("STOCK:TransferSqlServerObjectsTask");
    TaskHost tssoTask = execPackage as TaskHost;

    tssoTask.Name = SourceTableName.Replace(".", "_");
    tssoTask.Properties["SourceConnection"].SetValue(tssoTask, cmSource.Name);
    tssoTask.Properties["SourceDatabase"].SetValue(tssoTask, SourceDatabaseName);
    tssoTask.Properties["DestinationConnection"].SetValue(tssoTask, cmDestination.Name);
    tssoTask.Properties["DestinationDatabase"].SetValue(tssoTask, DestinationDatabaseName);
    tssoTask.Properties["CopyAllObjects"].SetValue(tssoTask, false);
    tssoTask.Properties["CopyAllTables"].SetValue(tssoTask, false);

    tssoTask.Properties["TablesList"].SetValue(tssoTask, tablesColl);

    if (AppendReplaceDrop == 3) tssoTask.Properties["DropObjectsFirst"].SetValue(tssoTask, true);
    else tssoTask.Properties["DropObjectsFirst"].SetValue(tssoTask, false);

    if (AppendReplaceDrop == 0) tssoTask.Properties["CopyData"].SetValue(tssoTask, false);
    else tssoTask.Properties["CopyData"].SetValue(tssoTask, true);

    tssoTask.Properties["CopySchema"].SetValue(tssoTask, true);

    switch (AppendReplaceDrop)
    {
    case 1:
        tssoTask.Properties["ExistingData"].SetValue(tssoTask, 1); //"Append"
        break;
    case 2:
        tssoTask.Properties["ExistingData"].SetValue(tssoTask, 0); //"Replace"
        break;
    default:
        break;
    }

    if (AppendReplaceDrop == 3)
    {
    tssoTask.Properties["CopyPrimaryKeys"].SetValue(tssoTask, true);
    tssoTask.Properties["CopyIndexes"].SetValue(tssoTask, true);
    tssoTask.Properties["CopyTriggers"].SetValue(tssoTask, true);
    }
    else
    {
    tssoTask.Properties["CopyPrimaryKeys"].SetValue(tssoTask, false);
    tssoTask.Properties["CopyIndexes"].SetValue(tssoTask, false);
    tssoTask.Properties["CopyTriggers"].SetValue(tssoTask, false);
    }

    if (IncludeDependentObjects == 1) tssoTask.Properties["IncludeDependentObjects"].SetValue(tssoTask, false);

    pkg.Execute();

    // Save to File
    if (SaveToFile == 1)
    {
    string xmlLocation = @"C:\temp\";
    string xmlFileName = "TransferSqlServerObjectsTask_"
        + "_" + SourceServerName.Replace(".", "_").Replace("\\", "_")
        + "_" + SourceDatabaseName.Replace(".", "_").Replace("\\", "_")
        + "_" + SourceTableSchemaName.Replace(".", "_").Replace("\\", "_")
        + "_" + SourceTableName.Replace(".", "_").Replace("\\", "_")
        + ".dtsx";
    string XmlFile = xmlLocation + xmlFileName + ".dtsx";

    XmlDocument myDoc = new XmlDocument();
    pkg.Name = "TransferSqlServerObjectsTask_"
        + "_" + SourceTableName.Replace(".", "_").Replace("\\", "_");

    pkg.SaveToXML(ref myDoc, null, null);
    myDoc.Save(XmlFile);
    }

    pkg.Dispose();

    Dts.TaskResult = (int)ScriptResults.Success;
}

Zusammenfassung

Das vorgestellte Verfahren kann beliebig angepasst werden und stellt eine erste funktionsfähige Replikation dar. Das Beispiel der vorgestellten Lösung liegt als SSIS-Solution bei und kann beliebig um Funktionen erweitert werden.

Momentan wird in jedem Schleifendurchlauf nur eine Tabelle behandelt und für jede einzelne Tabelle ein virtuelles SSIS-Paket erzeugt. Da man im „Transfer SQL Server Objects Task“ beliebig viele Objekte gleichzeitig hinterlegen kann (siehe „StringCollection tablesColl”) und die Kompilierung und Ausführung eines Pakets sehr viel Zeit beansprucht, sollten wir zukünftig nach einer bessere Möglichkeit suchen, um in einem Schleifendurchlauf mehrere Tabellen als Variablen an den Script-Task zu übergeben.

Auf die Control-Tabelle könnte auch aus dem Script-Task zugegriffen und die Tabellen-Collection befüllt werden. Damit wäre das Auslesen der Control-Tabelle und des Schleifenoperators nicht mehr nötig.

Auch sind im „Transfer SQL Server Objects Task“ mehr als nur Tabellen übertragbar. Das Tool kann benutzt werden, um fast jedes Objekt einer SQL-Server-Datenbank zu übertragen (mit Ausnahme von verschlüsselten Objekten).