Microsoft SQL Server Plug-in

Das Microsoft SQL Server Plug-in im OPC Router ermöglicht die Kommunikation mit Microsoft SQL Servern. Mit diesem Plug-In können Sie Daten aus bestehenden Microsoft SQL Datenbanken lesen, neue Daten schreiben, Daten ändern oder Daten löschen. Sie können auch Daten aus anderen OPC Router Plug-Ins wie OPC UA, MQTT oder SAP an Microsoft SQL Server übertragen. Das Plug-In unterstützt verschiedene SQL-Befehle und Abfragen. Diese können grafisch erstellt werden.​

Authentifizierung

SQL Server-Authentifizierung

Die SQL Server-Authentifizierung ist ein alternativer Authentifizierungsmodus, der verwendet werden kann, wenn die Windows-Authentifizierung nicht verfügbar ist. In diesem Modus wird ein SQL Server-Anmeldename und ein -Passwort verwendet, um die Identität des Benutzers zu überprüfen. Um sich mit der SQL Server-Authentifizierung bei SQL Server zu authentifizieren, muss der Benutzer über ein gültiges SQL Server-Anmeldekonto mit den Berechtigungen verfügen, die für die Ausführung der gewünschten Aufgaben erforderlich sind.

Windows-Authentifizierung

Bei der Windows-Authentifizierung wird die Identität des Benutzers anhand seines Windows-Benutzerkontos überprüft. Das bedeutet, dass sich der Benutzer nicht mit einem separaten SQL Server-Benutzernamen und -Passwort anmelden muss. Stattdessen wird der Benutzer automatisch authentifiziert, wenn er sich mit seinem Windows-Benutzerkonto anmeldet. Diese Authentifizierung ist nur für Windows-Systeme relevant und funktioniert nicht mit Docker oder Linux.

warnung

Wichtig! Auch wenn für den Verbindungstest ein anderer Windows-Benutzer verwendet wurde, muss der OPC Router-Service-Benutzer über die für die Authentifizierung erforderlichen Rechte verfügen.

Change Notification Broker

Der Change Notification Broker ist eine Funktion des SQL Servers, die es dem OPC Router ermöglicht, bei Datenänderungen in einer Datenbank Benachrichtigungen zu erhalten. Der Broker verwendet eine Warteschlange, um Benachrichtigungen an den OPC Router zu senden.

info

Wenn im OPC Router ein Trigger konfiguriert und projektiert ist, verbindet er sich mit der konfigurierten Datenbank und prüft, ob der Change Notification Broker aktiviert ist. Ist dies nicht der Fall, so wird im entsprechenden Trigger eine Warnung angezeigt, dass der OPC Router die Datenbanktabellen regelmäßig abfragt. Die Abfragefrequenz wird durch die im Plug-In eingestellte Eigenschaft bestimmt

info

Um die Konfiguration der Datenbankverbindung erfolgreich durchführen zu können, muss der Benutzer, der die Verbindung herstellt, über bestimmte Berechtigungen verfügen. Bitte prüfen Sie, ob der Benutzer über diese Berechtigungen verfügt oder fordern Sie diese an.
Auf Datenbankebene werden folgende Berechtigungen benötigt: ALTER, CREATE MESSAGE TYPE, CREATE CONTRACT, CREATE QUEUE, CREATE SERVICE

info

Um den Notification Broker ohne DB_Owner Rechte nutzen zu können führen Sie bitte folgendes SQL Script aus:

CREATE SCHEMA [OpcRouterSchema] AUTHORIZATION [OPCRouterUser];
GO
ALTER USER [OPCRouterUser] WITH DEFAULT_SCHEMA = [OpcRouterSchema];
GO
GRANT SELECT to [OPCRouterUser];
GO
GRANT CREATE PROCEDURE to [OPCRouterUser];
GO
GRANT CREATE QUEUE to [OPCRouterUser]; 
GO
GRANT CREATE SERVICE to [OPCRouterUser];
GO
GRANT VIEW DEFINITION TO [OPCRouterUser];
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [OPCRouterUser];
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [OPCRouterUser];
GO
GRANT REFERENCES on CONTRACT::[ to [OPCRouterUser];
GO

Benötigte Rollen und Rechte für Datenbank Transfer Objekte

Aktion/ Transferobject	Beschreibung	Rollen	Rechte
Update		db_datawriter	UPDATE
Select	ohne TransferState	db_datareader	SELECT
Select	mit TransferState	db_datareader, db_datawriter	SELECT, WRITE
Stored Procedure		db_executer	EXECUTE
Delete		db_datawriter	DELETE
Insert		db_datawriter	INSERT

Relevante Timeouts

Ein Timeout ist eine Situation, in der eine Aktion nicht innerhalb einer bestimmten Zeit abgeschlossen werden konnte. Im Falle des OPC Routers kann es bei der Kommunikation mit dem Microsoft SQL Server zu einem Timeout kommen.

Arten von Timeouts

• Connection Timeout: Das Connection Timeout ist die maximale Zeit, die der OPC Router auf eine Verbindung zum Microsoft SQL Server wartet.
• Command Timeout: Das Command Timeout ist die maximale Zeit, die der OPC Router auf die Ausführung eines SQL-Befehls wartet.

warnung

Wenn das Command Timeout erhöht wird, sollte auch das OPC Router Timeout erhöht werden. Andernfalls können sich die Timeouts überschneiden und zu Fehlern führen.

Ursachen von Timeouts

• Hohe Auslastung des Microsoft SQL Servers: Wenn der Microsoft SQL Server stark ausgelastet ist, kann es zu Verzögerungen bei der Ausführung von SQL-Befehlen kommen.
• Netzwerkprobleme: Wenn die Netzwerkverbindung zwischen dem OPC Router und dem Microsoft SQL Server gestört ist, kann es zu Verzögerungen oder Ausfällen in der Kommunikation kommen.
• Fehlerhafte SQL-Befehle: Wenn ein SQL-Befehl fehlerhaft ist, kann er vom SQL Server nicht ausgeführt werden.

Performance im Microsoft SQL Server

Die Performance des Microsoft SQL Servers ist für den OPC Router wichtig, da er die Datenbank zum Speichern und Abrufen von Daten verwendet. Wenn die Performance des SQL Servers beeinträchtigt ist, kann dies zu Verzögerungen beim Lesen und Schreiben von Daten führen.

Faktoren, welche die Performance des SQL Servers beeinflussen:

• Indizes: Indizes sind Datenstrukturen, die die Suche nach Daten in einer Tabelle beschleunigen. Wenn eine Tabelle keine Indizes hat, muss der SQL Server die gesamte Tabelle durchsuchen, um die gewünschten Daten zu finden. Dies kann sehr zeitaufwendig sein, wenn die Tabelle viele Spalten hat oder viele Datensätze enthält.
• Fragmentierung: Fragmentierung ist ein Zustand, in dem die Daten in einer Tabelle nicht in einer kontinuierlichen Reihenfolge gespeichert sind. Dies kann die Leistung von Abfragen beeinträchtigen, da der SQL Server mehr Zeit zum Lesen der Daten benötigt.
• Datenorganisation: Die Datenorganisation ist ein weiterer wichtiger Faktor, der die Leistung des SQL Servers beeinflussen kann. Eine effiziente Datenorganisation kann dazu beitragen, dass der SQL Server Daten schneller finden und verarbeiten kann.

warnung

Die Pflege des Microsoft SQL Servers ist wichtig für die Performance des OPC Routers.

Microsoft SQL Server Performance Guide

Verwendung von Zeitstempeln

Bei der Verwendung von Zeitstempeln ist es wichtig, dass alle überall die gleiche Zeitzone verwendet wird. Wenn Sie beispielsweise einen Zeitstempel in der Zeitzone UTC verwenden, müssen alle anderen Daten, die mit diesem Zeitstempel verglichen werden, ebenfalls in der Zeitzone UTC verwendet werden.

info

Wir empfehlen die Verwendung von UTC

Bei der Verwendung eines lokalen Zeitstempels empfiehlt es sich, im Tab „Erweitert“ des Microsoft SQL Server Plug-Ins das DateTime-Handling explizit auf „Local“ zu setzen.

Der OPC Router bietet drei verschiedene Möglichkeiten, mit Zeitstempeln umzugehen. Bitte beachten Sie, dass der OPC Router bei der Übertragung von Daten Datentypkonvertierungen durchführen muss. Wenn bei der Konvertierung keine Informationen über die Zeitzone verfügbar sind, verwendet der OPC Router normalerweise die Zeitzone des OPC Router-Dienstes. Findet die Konvertierung beim Einfügen in die Datenbank statt, wird die hier eingestellte Option verwendet.

Unspecified	Der OPC Router lässt die Zeitzone so, wie sie ist.
Local	Der OPC Router konvertiert den Zeitstempel in die vom Prozess hinterlegte Zeitzone.
UTC	Der OPC Router konvertiert den Zeitstempel in die UTC-Zeitzone.

Eigenschaften

Name	Beliebiger Name, unter dem dieser Datenbankserver in der Verbindungsprojektierung erscheint. Wählen Sie einen Namen, der für Sie während der Verbindungsprojektierung eindeutig ist.

„Verbindungseinstellungen“-Tab

Host/IP/Datei	Name oder IP Adresse des Microsoft SQL Servers
Port	Auswahl des Ports für die Datenbankanbindung. Bei Bedarf kann der Port gewechselt werden.
Datenquelle	Name der zu verwendenden Datenbank.
Windows-Authentifizierung	Wenn diese Option aktiv ist, wird die Windows-Authentifizierung anstelle der Eigenschaften Benutzer und Passwort verwendet. Unterstützt werden lokale Benutzerkonten, DCOM-Benutzer und Windows-Domäne-Benutzer (NT-Authentifizierung). Um Windows Authentifizierung zu nutzen, muss der User, unter dem der OPC-Router-Dienst gestartet wird (standardmäßig „System“) Zugriffsrechte auf die Datenbank haben. Um projektieren zu können, muss der User, unter dem die Konfigurationsumgebung gestartet wurde, Zugriffsrechte auf die Datenbank haben. „Verbindung testen“ ist in diesem Fall nicht aussagekräftig, da nicht mit dem User „System“ getestet wird, sondern mit dem User, der die Konfigurationsumgebung gestartet hat
Benutzer	Benutzername zur Anmeldung am MS SQL Server
Passwort	Passwort zur Anmeldung am MS SQL Server
Failover Einstellungen	Geben Sie hier die Adresse und den Port eines Failover-Servers an. (Es wird nur der „Connection-String“ angepasst. Die Konfiguration des Failover-Cluster wird direkt am Microsoft SQL Server vorgenommen)

„Erweiterte Verbindungsparameter“-Tab

Command Time-out	Zeit in Sekunden, die der Router auf den Abschluss eines SQL-Befehls wartet. Bei lang andauernden Abfragen kann es erforderlich sein, den Wert zu erhöhen. Beachten Sie bitte, dass der Verbindungstimeout in den Verbindungen, die lange Abfragen anstoßen auch erhöht werden muss.
Connection Time-out	Zeit in Sekunden, die ein Verbindungsaufbau zur Datenbank dauern darf.
Gleichzeitige Verbindungen	Anzahl der Verbindungen, die der Router parallel zur Datenbank aufbauen darf. Je mehr gleichzeitige Verbindungen erlaubt sind, desto schneller ist der Router, aber die Datenbank kann dadurch verlangsamt werden. Fragen Sie Ihren Datenbank-Administrator, wie viele Verbindungen er dem Router zugesteht. Dies hängt selbstverständlich auch von der Größe des Projektes ab.
Polling Intervall	Der Intervall, in dem die Datenbank abgefragt wird. Wenn in der Datenbank der Broker aktiviert ist, erfolgt das Polling automatisch.

„Erweitert“-Tab

Datetime-Handling	siehe #verwendung-von-zeitstempeln
Klammer-Verhalten	Eigenschaft, die bestimmt, wann eckige Klammern in Ihrem Microsoft SQL Server Plugin verwendet werden sollen. Weitere Informationen dazu finden Sie hier.
Parameter Umwandlung	Wenn diese Option aktiv ist, werden die Typen vom OPC Router konvertiert, wenn nicht dann werden sie vom Zielsystem konvertiert.
Encrypt	Diese Eigenschaft bestimmt, ob die Verschlüsselung aktiviert ist. Wenn die Option „Serverzertifikat“ vertrauen deaktiviert ist und die Option „Encrypt“ aktiviert ist, muss der Servername (oder die IP-Adresse) in einem SQL Server SSL-Zertifikat genau mit dem Servernamen (oder der IP-Adresse) übereinstimmen, der in der Verbindungszeichenfolge angegeben wurde. Andernfalls schlägt der Verbindungsversuch fehl.
Serverzertifikat vertrauen	Diese Eigenschaft bestimmt, ob SSL zur Verschlüsselung des Kanals verwendet wird, auch wenn das Zertifikat nicht von einer bekannten Zertifizierungsstelle ausgestellt wurde. Wenn die Option „Serverzertifikat vertrauen“ aktiviert ist und die Option „Encrypt“ deaktiviert ist, wird der Kanal nicht verschlüsselt.
Hostname im Zertifikat	Diese Option kann verwendet werden, um einen anderen erwarteten CN (Namen) oder SAN (alternativen Namen) für das Serverzertifikat anzugeben, wenn der Servername nicht mit dem CN oder SAN im Serverzertifikat übereinstimmt.
QuerySpParameterDefaultValue	Wenn diese Eigenschaft aktiviert ist, fragt der OPC Router die Standardwerte der Parameter von Stored Procedures ab und speichert diese.

Klammer-Verhalten

• In Anführungszeichen setzen, wenn Punkt vorhanden: Dieser Modus ist für die Abwärtskompatibilität gedacht. Wenn ein Punkt (.) in einem Bezeichner enthalten ist, werden eckige Klammern hinzugefügt. Dies ist nützlich, wenn Sie Datenbankobjekte mit Punkten in ihren Namen haben, wie z.B. datenbank.schema.[tabelle.name].
• Nie in Anführungszeichen setzen: In diesem Modus werden keine eckigen Klammern hinzugefügt, unabhängig vom Bezeichner. Dies kann nützlich sein, wenn Sie sicher sind, dass Ihre Bezeichner keine Sonderzeichen oder Leerzeichen enthalten.
• Immer in Anführungszeichen setzen: In diesem Modus werden immer eckige Klammern hinzugefügt. Dies ist nützlich, wenn Ihre Bezeichner Sonderzeichen, Leerzeichen oder reservierte Wörter enthalten können.