Skip to main content
Version: 5.6

Service Broker for Notifications of Data Changes

Service Broker messaging is a feature of Microsoft SQL Server that can be used to notify the OPC Router of changes in a table.

Instead of repeatedly checking for new data at fixed intervals, the OPC Router receives a message from the SQL Server as soon as relevant data has changed. This allows the connection to respond faster and generates fewer unnecessary queries.

Technically, SQL Server uses internal notifications and queues for this. For configuration in the OPC Router, it is particularly important that SQL Server is permitted to use this feature and that the configured user has the necessary permissions.

When is this feature useful?​

Service Broker messaging is particularly useful when:

  • You want to reduce polling load on the database
  • A table needs to be monitored not just cyclically, but as close to the event as possible

If this feature is unavailable or cannot be used, the OPC Router uses polling instead.

note

To successfully configure the database connection, the user must have specific permissions. Therefore, check in advance whether the account has these rights or request them from the database administrator.

The following rights are generally required for the notification function itself:

  • SELECT on the monitored tables
  • SUBSCRIBE QUERY NOTIFICATIONS
  • RECEIVE on the queue used for notifications
  • REFERENCES on the contract http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification

If the queue and service do not yet exist and are to be created by the user themselves, the following rights are typically required in addition:

  • CREATE QUEUE
  • CREATE SERVICE

If the Service Broker for the database still needs to be enabled, ALTER at the database level is also required.

Preparing Users and Permissions​

In many projects, the database administration sets up a dedicated user for the OPC Router. If this user does not yet exist, it can be created using an SQL script.

note

A user for the OPC Router can be created with the following SQL script:

CREATE LOGIN [<nutzername>] WITH PASSWORD = '<passwort>';
GO
-- Switch the context to the database to be used in the OPC Router
USE <datenbank>;
GO
-- Create a user for the login
CREATE USER [<nutzername>] FOR LOGIN [<nutzername>];
GO
note

If you want to use Service Broker messaging without db_owner privileges, run the following SQL script and adjust the values to match your environment.

The following example assigns a practical set of privileges for the notification function. It includes privileges for ongoing operation as well as optional privileges for setup, in case the queue, service, or broker has not yet been configured.

-- Switch the context to the database to be used in the OPC Router
USE <datenbank>;
GO

-- Optional: Create a custom schema for the user
CREATE SCHEMA [<schema>] AUTHORIZATION [<nutzername>];
GO
ALTER USER [<nutzername>] WITH DEFAULT_SCHEMA = [<schema>];
GO

-- Permissions for the ongoing operation of the notification function
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

-- Optional permissions if the user is to create the queue and service themselves
-- GRANT CREATE QUEUE TO [<nutzername>];
-- GO
-- GRANT CREATE SERVICE TO [<nutzername>];
-- GO

-- Optional UPDATE to allow use of the DataChange trigger
-- GRANT UPDATE TO [<nutzername>];
-- GO

-- Optional permission to enable the broker for the database
-- GRANT ALTER TO [<nutzername>];
-- GO

Prerequisites for notifications​

  1. The user configured in the plug-in must have the necessary permissions to enable the broker for the database, or the broker must already be active.
note

You can check whether the broker is active for a database using the following command:

 SELECT IS_BROKER_ENABLED FROM SYS.DATABASES WHERE NAME = '<datenbankname>'
  1. The table monitored by the trigger must not be a view and must not contain any calculated columns.

What does this mean in practice?​

If all prerequisites are met, SQL Server can notify the OPC Router directly of relevant changes. This is particularly helpful for connections that need to react quickly to new data records.

If the feature cannot be set up, this is not a fundamental blocker for the project. Monitoring can still be performed via polling, just with periodic queries instead of event-driven notifications.

Limitations​

warning

If the connection to the broker is lost, for example due to an ID change, the OPC Router falls back to polling. A new broker connection is then established only after the runtime is restarted.