Microsoft SQL Server Plug-in
The Microsoft SQL Server plug-in in the OPC Router enables communication with Microsoft SQL servers. With this plug-in, you can read data from existing Microsoft SQL databases, write new data, change data or delete data. You can also transfer data from other OPC Router plug-ins such as OPC UA, MQTT or SAP to Microsoft SQL Servers. The plug-in supports various SQL commands and queries. These can be created graphically.
Authentication
SQL Server authentication
SQL Server authentication is an alternative authentication mode that can be used if Windows authentication is not available. In this mode, a SQL Server login name and password are used to verify the identity of the user. To authenticate at the SQL Server using SQL Server authentication, the user must have a valid SQL Server account with the permissions required to perform the desired tasks.
Windows authentication
With Windows authentication, the user's identity is verified using their Windows user account. This means that the user does not have to log in with a separate SQL Server username and password. Instead, the user is automatically authenticated when they log in with their Windows user account. This authentication is only relevant for Windows systems and does not work with Docker or Linux.
Important! Even if another Windows user was used for the connection test, the OPC Router service user must have the rights required for authentication.
Change Notification Broker
The Change Notification Broker is a function of the SQL Server that makes it possible for the OPC Router to receive notifications when data was changed in a database. The broker uses a queue to send notifications to the OPC Router.
If a trigger is configured and part of a connection in the OPC Router, it connects to the configured database and checks whether the Change Notification Broker is activated. If this is not the case, a warning is being displayed in the corresponding trigger telling that the OPC Router regularly queries the database tables. The query frequency is determined by the property set in the plug-in
In order to successfully configure the database connection, the user establishing the connection must have certain authorizations. Please check whether the user has these authorizations or request them.
The following authorizations are required
ALTER, CREATE MESSAGE TYPE, CREATE CONTRACT, CREATE QUEUE, CREATE SERVICE
To be able to use the Notification Broker without DB_Owner rights, please execute the following SQL script:
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
Required roles and rights for database transfer objects
| Action/ Transferobject | Description | Roles | Rights |
|---|---|---|---|
| Update | db_datawriter | UPDATE | |
| Select | without TransferState | db_datareader | SELECT |
| Select | with TransferState | db_datareader, db_datawriter | SELECT, WRITE |
| Stored Procedure | db_executer | EXECUTE | |
| Delete | db_datawriter | DELETE | |
| Insert | db_datawriter | INSERT |
Relevant timeouts
A timeout is a situation in which an action could not be completed within a certain amount of time. In the case of the OPC Router, a timeout can occur during communication with the Microsoft SQL Server.
Types of timeouts
- Connection timeout: The connection timeout is the maximum amount of time that the OPC Router waits for a connection to the Microsoft SQL Server.
- Command Timeout: The Command Timeout is the maximum amount of time the OPC Router waits for an SQL command to be executed.
If the command timeout is increased, the OPC Router timeout should also be increased. Otherwise the timeouts may overlap and lead to errors.
Causes of timeouts
- High utilization of the Microsoft SQL Server: If the Microsoft SQL Server is heavily utilized, there may be delays in the execution of SQL commands.
- Network problems: If the network connection between the OPC Router and the Microsoft SQL Server is disrupted, there may be delays or failures in communication.
- Erroneous SQL commands: If an SQL command is faulty, it cannot be executed by the SQL Server.
Microsoft SQL Server Performance
The performance of the Microsoft SQL Server is important for the OPC Router as it uses the database to store and retrieve data. If the performance of the SQL Server is impaired, this can lead to delays when reading and writing data.
Factors that influence the performance of the SQL Server:
- Indexes: Indexes are data structures that speed up the search for data in a table. If a table has no indexes, the SQL server must search the entire table to find the desired data. This can be very time-consuming if the table has many columns or contains many data records.
- Fragmentation: Fragmentation is a condition in which the data in a table is not stored in a continuous order. This can affect the performance of queries, as the SQL server needs more time to read the data.
- Data organization: Data organization is another important factor that can affect SQL Server performance. Efficient data organization can help SQL Server find and process data faster.
The maintenance of the Microsoft SQL Server is important for the performance of the OPC Router.
microsoft-sql-server-performance-guide
Using timestamps
When using timestamps, it is important that the same time zone is used everywhere. For example, if you use a timestamp in the UTC time zone, all other data that is compared with this timestamp must also be used in the UTC time zone.
We recommend using UTC
When using a local timestamp, it is recommended to explicitly set the DateTime handling to "Local" in the "Advanced" tab of the Microsoft SQL Server plug-in.
The OPC Router offers three different options for handling timestamps. Please note that the OPC Router must perform data type conversions when transferring data. If no information about the time zone is available during the conversion, the OPC Router normally uses the time zone of the OPC Router service. If the conversion takes place during insertion into the database, the option set here is used.
| Unspecified | The OPC Router leaves the time zone as it is. |
|---|---|
| Local | The OPC Router converts the time stamp to the time zone defined by the process. |
| UTC | The OPC Router converts the timestamp to the UTC time zone. |
Properties
| Name | Any name under which this database server appears in the connection configuration. Select a name that is unique for you during connection configuration. |
|---|
"Connection settings" tab
| Host/IP/file | Name or IP address of the Microsoft SQL Server |
| Port | Select the port for the database connection. The port can be changed if required. |
| Data source | Name of the database to be used. |
| Windows authentication | If this option is active, Windows authentication is used instead of the user and password properties. Local user accounts, DCOM users and Windows domain users (NT authentication) are supported. In order to use Windows authentication, the user under which the OPC Router service is started (by default "System") must have access rights to the database. To be able to project, the user under which the configuration environment was started must have access rights to the database. "Test connection" is not conclusive in this case, as testing is not carried out with the "System" user, but with the user who started the configuration environment. |
| User | Username for logging on to the MS SQL Server |
| Password | Password for logging on to MS SQL Server |
| Failover settings | Enter the address and port of a failover server here. (Only the "connection string" is adjusted. The failover cluster is configured directly on the Microsoft SQL Server) |
"Advanced connection parameters" tab
| Command time-out | Time in seconds that the router waits for the completion of an SQL command. It may be necessary to increase the value for long queries. Please note that the connection timeout in the connections that trigger long queries must also be increased. |
|---|---|
| Connection time-out | Time in seconds that a connection to the database is allowed take. |
| Parallel connections | Number of parallel connections that the router is allowed to establish to the database. The more parallel connections are allowed, the faster the router is, but the database may be slowed down by it. Ask your database administrator how many parallel connections they allow for the OPC Router. Of course, this also depends on the size of the project. |
| Polling interval | The interval at which the database is polled. If the broker is activated in the database, polling takes place automatically. |
"Advanced" tab
| Datetime handling | See #using-timestamps |
|---|---|
| Bracket behavior | Property that determines when square brackets should be used in your Microsoft SQL Server plugin. You can find more information about this here. |
| Parameter conversion | When this option is active, the types are converted by the OPC Router, if not, they are converted by the target system. |
| Encrypt | This property determines whether encryption is activated. If the "Trust server certificate" option is disabled and the "Encrypt" option is enabled, the server name (or IP address) in a SQL Server SSL certificate must exactly match the server name (or IP address) specified in the connection string. Otherwise, the connection attempt will fail. |
| Trust server certificate | This property determines whether SSL is used to encrypt the channel, even if the certificate was not issued by a known certificate authority. If the "Trust server certificate" option is activated and the "Encrypt" option is deactivated, the channel is not encrypted. |
| Hostname in certificate | This option can be used to specify a different expected CN (name) or SAN (alternative name) for the server certificate if the server name does not match the CN or SAN in the server certificate. |
| QuerySpParameterDefaultValue | When this property is activated, the OPC Router queries the default values of the parameters of stored procedures and saves them. |
Bracket behavior
- Enclose if dot present: This mode is intended for backward compatibility. If a dot (
.) is contained in an identifier, square brackets are added. This is useful if you have database objects with dots in their names, such asdatabase.schema.[table.name]. - Never enclose: In this mode, no square brackets are added, regardless of the identifier. This can be useful if you are sure that your identifiers do not contain special characters or spaces.
- Always enclose: In this mode, square brackets are always added. This is useful if your identifiers may contain special characters, spaces or reserved words.