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 using the following script.

CREATE LOGIN [<username>] WITH PASSWORD = '<password>';
GO
-- Switching the context to the database to be used in the OPC Router
USE <database>;
-- Creating a user for the login
CREATE USER [<username>] FOR LOGIN [<username>];
GO

note

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

-- Switch the context to the database to be used in the OPC Router
USE <database>;
GO
CREATE SCHEMA [<schema>] AUTHORIZATION [<username>];
GO
ALTER USER [<username>] WITH DEFAULT_SCHEMA = [<schema>];
GO
GRANT SELECT to [<username>];
GO
GRANT CREATE PROCEDURE to [<username>];
GO
GRANT CREATE QUEUE to [<username>];
GO
GRANT CREATE SERVICE to [<username>];
GO
GRANT VIEW DEFINITION TO [<username>];
GO
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [<username>];
GO
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [<username>];
GO
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [<username>];
GO
-- Optional UPDATE to allow the use of the Datachange trigger
-- GRANT UPDATE TO [<username>];
-- GO

-- Optional permission to enable the broker
-- GRANT ALTER TO [<username>];
-- 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 enabled by default for databases. You can query the current status using the following commands:

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

2. 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.