Neben der Nutzung von verwalteter Massendatenreplikation mit Invantive Data Replicator oder Azure Data Factory, können Sie auch eine Kopie von Tabellen in den über 70 unterstützten Plattformen in eine Microsoft SQL Server-Umgebung erstellen. Dieser Beitrag beschreibt, wie Sie eine automatisierte tägliche Kopie von Exact Online zu einem On-Premise Microsoft SQL Server konfigurieren können.
Für SQL Server auf Azure oder AWS können Sie diesen Ansatz ebenfalls verwenden, aber auch Data Replicator, eine Invantive Cloud-Anwendung oder den Exact Online Azure Data Factory Connector von Invantive Cloud
Invantive Cloud bietet ebenfalls ETL-Funktionalität ähnlich wie Azure Data Factory. Für ein Beispiel siehe:
Die Exact Online-Kopie enthält in diesem Beispiel:
- Hauptbuchkonten,
- alle Transaktionen und ihre Zeilen,
- Kostenstellen-Definitionen,
- Mitarbeiter,
- Verkaufsaufträge inklusive Zeilen,
- Verkaufsrechnungen inklusive Zeilen
- Konten sowie deren Adressen und Klassifizierungen,
- Artikel, Artikelgruppen und Lagerbestände,
- Preislisten.
Das Hinzufügen einer der tausend anderen Tabellen ist einfach: Sie kopieren das SQL-Statement und ändern den Namen der Tabelle. Die Liste der über 1.100 verfügbaren Exact Online-Tabellen finden Sie unter:
Die Schritte in diesem Artikel stehen für sich allein, aber angesichts der vielen und komplexen Komponenten, die dabei eine Rolle spielen, empfiehlt es sich, die kostenlose einstündige Einführung zu nutzen, um schnell loslegen zu können. Die kostenlose Einführung kann gebucht werden über:
Software installieren
Zunächst müssen Sie Invantive Data Hub auf einem PC oder Server installieren. Meistens geschieht dies mit einer Testversion von Invantive Data Hub, Sie können aber auch Invantive Query Tool mit einer Premium-Lizenz verwenden. Einen Download finden Sie unter https://download.invantive.com, zum Beispiel https://go.invantive.eu/download/invantive-data-hub-mp-windows.
Starten Sie anschließend Invantive Data Hub und konfigurieren Sie eine Testlizenz, die 30 Tage lang unbegrenzten Zugriff auf nahezu die gesamte Invantive SQL-Funktionalität und die Treiber gewährt.
Umgebung vorbereiten
Sie benötigen Anmeldedaten, um sich bei Exact Online und SQL Server anzumelden, sowie eine Datenbank, um die Kopie zu laden. Führen Sie die folgenden Schritte aus:
- Erhalten Sie Zugriff auf eine Exact Online-Umgebung mit einem oder mehreren Unternehmen mithilfe eines Anmeldecodes mit Passwort.
- Bewahren Sie den QR-Code und den zugehörigen Secret Key des Benutzers auf (Erklärung).
- Registrieren Sie eine Exact Online-Anwendung.
- Öffnen Sie SQL Server Management Studio und melden Sie sich als Benutzer „sa“ an.
- Erstellen Sie eine neue Datenbank mit dem Namen
exactonline. - Erstellen Sie einen neuen Datenbankbenutzer
exactonlinemit SQL-Authentifizierung. - Machen Sie den neuen Datenbankbenutzer zum
db_ownerder neuen Datenbank.
Verteilte Datenbank konfigurieren
Standardmäßig konfiguriert Invantive Data Hub automatisch mehr als 70 Verbindungen für Plattformen. Dies sind jedoch alles Datenbanken, die jeweils eine einzelne Plattform ansprechen, wie zum Beispiel Salesforce. Um Daten von der Exact Online-Plattform nach Microsoft SQL Server zu kopieren, müssen Sie eine sogenannte „verteilte Datenbank“ definieren. Eine verteilte Datenbank bündelt mehrere Datencontainer wie ganz Exact Online Niederlande, Salesforce und SQL Server in einer übergreifenden virtuellen Datenbank.
Erstellen Sie eine Datei settings-distributed.xml in %USERPROFILE%invantive. Wenn Sie %USERPROFILE% in die Adressleiste des Windows-Explorers eingeben, gelangen Sie zum richtigen Ordner mit dem Namen C:\users\LOGONCODE.
Das XML-basierte Dateiformat settings.xml wird beschrieben unter Settings.xml XML-formaat voor virtuele SQL databases.
Der Inhalt dieser Datei lautet:
<?xml version="1.0" encoding="utf-16"?>
<settings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
version="5"
forcedefault="false"
>
<group name="***BEDRIJFSNAAM***" >
<connection name="exact-online-nl-sql-server"
>
<database order="0"
alias="eol"
provider="ExactOnlineAll"
defaultUserLogonCode="EXACTONLINEUSERCODE"
defaultPassword="EXACTONLINEUSERPASSWORD"
connectionString="api-url=https://start.exactonline.nl;api-client-id=***CLIENT ID***;api-redirect-url=https://clientredirect.invantive.com;totp-secret=TOTPSECRET"
AllowConnectionStringRewrite="false"
/>
<database alias="sqlserver"
provider="SqlServer"
connectionString="Data Source=***HOST***;Database=***DATABASE NAME***;UID=***DATABASE USER LOG ON CODE***;PWD=***DATABASE USER PASSWORD***"
AllowConnectionStringRewrite="false"
/>
</connection>
</group>
</settings>
Die Anmeldung nutzt den Implicit Grant Flow.
Die Verwendung des Code Grant Flow mit einem OAuth-Refresh-Token ist ebenfalls möglich, erfordert jedoch aufgrund der heutigen Einschränkungen bei Exact Online einige Hintergrundkenntnisse. Wenn nicht parallel gearbeitet wird, genügt es, das Client Secret in der Connection-String als client-secret=secret hinzuzufügen.
Vergessen Sie nicht, die folgenden Platzhalter zu ersetzen:
- BEDRIJFSNAAM: der gewünschte Firmenname, z. B. ‘Acme Corp.’.
- CLIENT ID: die Client-ID, wie sie unter Apps verwalten auf apps.exactonline.com hinterlegt ist.
- HOST: der Name oder die IP-Adresse Ihres SQL Server-Anbieters.
- DATABASE NAME: der Name der von Ihnen erstellten Datenbank, also
exactonline. - DATABASE USER LOG ON CODE: der Name des Benutzerkontos, also
exactonline. - DATABASE USER PASSWORD: das Passwort des Benutzerkontos.
- EXACTONLINEUSERCODE: der Exact Online-Anmeldecode.
- EXACTONLINEUSERPASSWORD: das Passwort des Exact Online-Benutzers.
- TOTPSECRET: das TOTP-Secret, das zum QR-Code gehört.
SQL-Kopierskript
Das folgende Invantive SQL-Skript erstellt und aktualisiert die Kopie der Exact Online-Unternehmen in SQL Server.
Speichern Sie den folgenden Inhalt als exact-online-copy.sql in einem Ordner mit dem Namen C:\test:
local remark Wenn beim Replizieren ein Fehler auftritt,
local remark dann mit dem Replizieren fortfahren und den Fehler
local remark am Ende des Prozesses melden.
local on error continue
local remark Alle verfügbaren Exact Online-Unternehmen auswählen.
use all@eol
local remark Die HTTP-Anfragen nicht in einem Cache speichern, um
local remark bei sehr großen Umgebungen Speicher zu sparen.
set use-http-memory-cache@eol false
create or replace table eol_salesorders@sqlserver
as
select *
from exactonlinerest..salesordersbulk@eol
create or replace table eol_salesorderlines@sqlserver
as
select *
from exactonlinerest..salesorderlinesbulk@eol
create or replace table eol_salesinvoices@sqlserver
as
select *
from exactonlinerest..salesinvoicesbulk@eol
create or replace table eol_salesinvoicelines@sqlserver
as
select *
from exactonlinerest..salesinvoicelinesbulk@eol
create or replace table eol_addresses@sqlserver
as
select *
from exactonlinerest..addressesbulk@eol
create or replace table eol_accounts@sqlserver
as
select *
from exactonlinerest..accountsbulk@eol
create or replace table eol_items@sqlserver
as
select *
from exactonlinerest..itemsbulk@eol
create or replace table eol_stockpositions@sqlserver
as
select *
from stockpositionsincremental@eol
create or replace table eol_pricelists@sqlserver
as
select *
from exactonlinerest..pricelists@eol
create or replace table eol_itemgroups@sqlserver
as
select *
from exactonlinerest..itemgroups@eol
create or replace table eol_costunits@sqlserver
as
select *
from exactonlinerest..costunits@eol
create or replace table eol_glaccounts@sqlserver
as
select *
from exactonlinerest..glaccountsincremental@eol
create or replace table eol_employees@sqlserver
as
select *
from exactonlinerest..employees@eol
create or replace table eol_accountclassifications@sqlserver
as
select *
from exactonlinerest..accountclassifications@eol
create or replace table eol_transactionlinesbulk@sqlserver
as
select *
from exactonlinerest..transactionlinesbulk@eol
Verwenden Sie die Exact Online API-Datenmodell-Dokumentation, um nach weiteren benötigten Tabellen zu suchen, oder durchsuchen Sie das grafische Tabellenbeziehungsdiagramm.
Erstellen Sie anschließend eine Batch-Datei, um dieses Skript auszuführen. Der einfachste Weg dafür ist:
- Starten Sie das Invantive Query Tool.
- Melden Sie sich bei der Datenbank exact-online-nl-sql-server in Ihrer Unternehmensgruppe an.
- Öffnen Sie die Datei
c:\test\exact-online-copy.sql. - Wählen Sie im Menü ‘Editor’ und anschließend ‘Create Data Hub Batch Script’.
- Wählen Sie ‘Speichern’.
- Benennen Sie die Batch-Datei
c:\test\exact-online-kopie.bat. - Die resultierende Batch-Datei sieht folgendermaßen aus:
@echo off
rem
rem Skript zum Ausführen einer Invantive SQL-Datei mit möglichen Invantive Script-Anweisungen.
rem
set INVANTIVE_PRG=C:\Program Files (x86)\Invantive Software BV\Invantive Data Hub\Invantive.Producer.QueryEngine.exe
set INVANTIVE_CONN=BEDRIJFSNAAM\exact-online-nl-sql-server
set INVANTIVE_SQL_FILE=c:\test\exact-online-copy.sql
set INVANTIVE_LOG_FILE=c:\test\exact-online-copy.log
set INVANTIVE_LOG_FILE_OVERWRITE=False
set INVANTIVE_INTERACTIVE=False
"%INVANTIVE_PRG%" /connection:"%INVANTIVE_CONN%" /file:"%INVANTIVE_SQL_FILE%" /logfile:"%INVANTIVE_LOG_FILE%" /logoverwrite:"%INVANTIVE_LOG_FILE_OVERWRITE%" /interactive:%INVANTIVE_INTERACTIVE%
Exact Online-Tabellen kopieren
Um die Daten von Exact Online tatsächlich nach SQL Server zu kopieren, führen Sie die Batch-Datei c:\test\exact-online-copy.bat aus.
Dies zeigt etwa Folgendes an:
Die Tabellen in SQL Server werden mithilfe von Bulk-Inserts der aus Exact Online abgerufenen Daten erstellt. Wo nötig, müssen Sie die Select-Klausel anpassen, um Spaltennamen wie ‘User’ in etwas umzuschreiben, das von SQL Server unterstützt wird. Andernfalls schreibt Invantive SQL diese automatisch in einen zulässigen Spaltennamen um.
Nachdem das Laden abgeschlossen ist, gibt es auf den Tabellen einige Indizes, basierend auf der Verteilung und Menge der Daten. Sie können den Tabellen weitere Indizes hinzufügen, um die Leistung zu verbessern, basierend auf Ihrer Kenntnis des Datenmodells und der Umgebung.
Identische Tabellendaten aller ausgewählten Exact Online-Unternehmen werden automatisch in einer Tabelle für alle Unternehmen gespeichert. Dies erleichtert die Konsolidierung zwischen Unternehmen.
Natürlich können Sie die aus Exact Online abgerufenen Datensätze für eine bessere Leistung filtern, zum Beispiel nach Status filtern, um nur offene Verkaufsaufträge abzurufen. Invantive SQL übersetzt Ihre Where-Klauseln automatisch in Filter, die für eine bessere Abrufleistung an Exact Online weitergeleitet werden.
Es wird empfohlen, die Microsoft Windows Aufgabenplanung zu verwenden, um die Batch-Datei täglich auszuführen, wie beschrieben unter https://superuser.com/questions/1178295/starting-invantive-data-hub-scripts-from-windows-task-scheduler.
Unterschiede
Im Vergleich zu Invantive Data Replicator gibt es einige Nachteile, derer Sie sich bewusst sein sollten, wenn Sie diesen Ansatz verwenden:
- Versionsverwaltung: Data Replicator fügt eine automatische Versionsverwaltung der Daten hinzu, um eine 24x7-Verfügbarkeit von Datensätzen zu ermöglichen.
- Datenmodell: Data Replicator pflegt automatisch das Datenmodell und bildet Spalten- und Tabellennamen entsprechend den Möglichkeiten der Zielplattform ab.
- Leistung: Data Replicator stimmt die Daten automatisch ab und partitioniert sie für sehr große Datensätze, sowohl hinsichtlich der Partitionen als auch der einzelnen Partitionsgröße. Dadurch können Sie auch Tausende von Administrationen auf einmal verarbeiten.
- Inkrementell: Data Replicator bietet inkrementelle Ladestrategien wie die Nutzung von Webhooks und Smart Sampling, ausgerichtet auf große Volumina.
- Audit: Data Replicator fügt zusätzliche Logging- und Auditing-Funktionen hinzu, um die Einhaltung gesetzlicher Berichtsanwendungen zu erleichtern.
Verwenden Sie generell Invantive Data Replicator für Datenvolumina zwischen 10 GB und 5 TB und/oder mehr als 100 Unternehmen. Verwenden Sie Data Hub für kleinere Volumina.
