Skip to main content
Version: 5.4

Using Service Broker messaging

Service Broker messaging is a feature of SQL Server that enables OPC Router to receive notifications when data changes in a database. The broker uses a queue to send notifications to OPC Router.

note

In order to successfully configure the database connection, the user establishing the connection must have certain permissions. Please check whether the user has these permissions or request them.

The following permissions are required at the database level:

ALTER, CREATE MESSAGE TYPE, CREATE CONTRACT, CREATE QUEUE, CREATE SERVICE

note

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

CREATE LOGIN [<nutzername>] WITH PASSWORD = &#x27;<passwort>&#x27;;
GO
-- Change the context to the database to be used in the OPC Router</passwort></nutzername>.<nutzername><passwort>
USE <datenbank>;
-- Create a user for the login</datenbank></passwort></nutzername>.
<nutzername><passwort><datenbank>CREATE USER [<nutzername>] FOR LOGIN [<nutzer>];
GO
note

To use Service Broker messaging without DB_Owner rights, please execute the following SQL script and adjust the values accordingly:

-- Change the context to the database to be used in OPC Router
USE <datenbank>;
GO
CREATE SCHEMA [<schema>] AUTHORIZATION [<nutzername>];
GO
ALTER USER [<nutzername>] WITH DEFAULT_SCHEMA = [<schema>];
GO
GRANT SELECT to [<nutzername>];
GO
GRANT CREATE PROCEDURE to [<nutzername>];
GO
GRANT CREATE QUEUE to [<nutzernutzername>];
GO
GRANT CREATE SERVICE to [<nutzername>];
GO
GRANT VIEW DEFINITION 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 [<nutzer>];
GO
-- Optional UPDATE to allow use of the Datachange trigger
-- GRANT UPDATE TO [<nutzer>];
-- GO

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

Requirements for using notifications​

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

The broker is active by default for databases. The current status can be queried using the following command.

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

Limitations​

warning

If the connection to the broker is interrupted, e.g., due to an ID change, the router falls back to polling and a connection to the broker is only reestablished after restarting the runtime.