Service Broker für Benachrichtigungen bei Datenänderungen

Die Service Broker-Nachrichtenübermittlung ist eine Funktion des Microsoft SQL Server, mit der der OPC Router über Änderungen in einer Tabelle informiert werden kann.

Statt in einem festen Takt immer wieder zu prüfen, ob neue Daten vorhanden sind, erhält der OPC Router vom SQL Server eine Meldung, sobald sich relevante Daten geändert haben. Dadurch reagiert die Verbindung schneller und erzeugt weniger unnötige Abfragen.

Technisch arbeitet der SQL Server dabei mit internen Benachrichtigungen und Warteschlangen. Für die Projektierung im OPC Router ist vor allem wichtig, dass der SQL Server diese Funktion verwenden darf und dass der konfigurierte Benutzer die nötigen Rechte besitzt.

Wann ist diese Funktion sinnvoll?

Die Service Broker-Nachrichtenübermittlung ist vor allem dann sinnvoll, wenn:

• Polling-Last auf der Datenbank reduziert werden soll
• eine Tabelle nicht einfach nur zyklisch, sondern möglichst ereignisnah überwacht werden soll

Wenn diese Funktion nicht verfügbar ist oder nicht genutzt werden kann, arbeitet der OPC Router stattdessen mit Polling.

hinweis

Um die Datenbankverbindung erfolgreich konfigurieren zu können, muss der verwendete Benutzer über bestimmte Berechtigungen verfügen. Prüfen Sie deshalb vorab, ob das Konto diese Rechte besitzt oder fordern Sie sie bei der Datenbankadministration an.

Für die Benachrichtigungsfunktion selbst werden in der Regel folgende Rechte benötigt:

• SELECT auf die überwachten Tabellen
• SUBSCRIBE QUERY NOTIFICATIONS
• RECEIVE auf die verwendete Queue für Benachrichtigungen
• REFERENCES auf den Contract http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification

Wenn Queue und Service noch nicht vorhanden sind und vom verwendeten Benutzer selbst angelegt werden sollen, werden zusätzlich typischerweise diese Rechte benötigt:

• CREATE QUEUE
• CREATE SERVICE

Wenn der Service Broker für die Datenbank erst noch aktiviert werden muss, ist zusätzlich ALTER auf Datenbankebene erforderlich.

Benutzer und Berechtigungen vorbereiten

In vielen Projekten richtet die Datenbankadministration einen eigenen Benutzer für den OPC Router ein. Falls dieser Benutzer noch nicht existiert, kann er mit einem SQL-Skript angelegt werden.

hinweis

Ein Benutzer für den OPC Router kann mit folgendem SQL-Skript erstellt werden:

CREATE LOGIN [<Nutzername>] WITH PASSWORD = '<Passwort>';
GO
-- Wechseln des Kontexts zu der Datenbank, die im OPC Router verwendet werden soll
USE <Datenbank>;
GO
-- Erstellen eines Nutzers für den Login
CREATE USER [<Nutzername>] FOR LOGIN [<Nutzername>];
GO

hinweis

Wenn Sie die Service-Broker-Nachrichtenübermittlung ohne db_owner-Rechte nutzen möchten, führen Sie das folgende SQL-Skript aus und passen Sie die Werte an Ihre Umgebung an.

Das folgende Beispiel vergibt ein praxistaugliches Rechte-Set für die Benachrichtigungsfunktion. Es enthält die Rechte für den laufenden Betrieb sowie optionale Rechte für die Einrichtung, falls Queue, Service oder Broker noch nicht vorbereitet sind.

-- Wechseln des Kontexts zu der Datenbank, die im OPC Router verwendet werden soll
USE <Datenbank>;
GO

-- Optional: eigenes Schema für den Benutzer anlegen
CREATE SCHEMA [<Schema>] AUTHORIZATION [<Nutzername>];
GO
ALTER USER [<Nutzername>] WITH DEFAULT_SCHEMA = [<Schema>];
GO

-- Rechte für den laufenden Betrieb der Benachrichtigungsfunktion
GRANT SELECT to [<Nutzername>];
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [<Nutzername>];
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [<Nutzername>];
GO
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [<Nutzername>];
GO

-- Optionale Rechte, falls Queue und Service vom Benutzer selbst angelegt werden sollen
-- GRANT CREATE QUEUE TO [<Nutzername>];
-- GO
-- GRANT CREATE SERVICE TO [<Nutzername>];
-- GO

-- Optional UPDATE für den die Nutzung des Datachange Triggers erlauben
-- GRANT UPDATE TO [<Nutzername>];
-- GO

-- Optionale Berechtigung, um den Broker für die Datenbank aktivieren zu können
-- GRANT ALTER TO [<Nutzername>];
-- GO

Voraussetzungen für die Benachrichtigungen

1. Der im Plug-in konfigurierte Benutzer muss die notwendigen Berechtigungen besitzen, um den Broker für die Datenbank zu aktivieren, oder der Broker muss bereits aktiv sein.

hinweis

Ob der Broker für eine Datenbank aktiv ist, können Sie mit folgendem Befehl prüfen:

 SELECT IS_BROKER_ENABLED FROM SYS.DATABASES WHERE NAME = '<DatenbankName>'

2. Die vom Trigger überwachte Tabelle darf keine View sein und keine berechneten Spalten enthalten.

Was bedeutet das für die Praxis?

Wenn alle Voraussetzungen erfüllt sind, kann der SQL Server den OPC Router direkt über relevante Änderungen informieren. Das ist besonders hilfreich bei Verbindungen, die schnell auf neue Datensätze reagieren sollen.

Wenn die Funktion nicht eingerichtet werden kann, ist das kein grundsätzlicher Blocker für das Projekt. Die Überwachung kann weiterhin über Polling erfolgen, nur eben mit zyklischen Abfragen statt mit ereignisnahen Meldungen.

Limitationen

warnung

Wenn die Verbindung zum Broker abbricht, zum Beispiel durch eine ID-Änderung, fällt der OPC Router auf Polling zurück. Eine neue Broker-Verbindung wird dann erst nach einem Neustart der Runtime aufgebaut.