Configuration
Using Timestamps
When using timestamps, it is important that everyone uses the same time zone. For example, if you use a timestamp in the UTC time zone, all other data compared to this timestamp must also be in the UTC time zone.
note
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 time zone information is available during conversion, the OPC Router normally uses the time zone of the OPC Router service. If the conversion takes place when inserting into the database, the option set here is used.
| Option | Description |
|---|---|
| Unspecified | The OPC Router leaves the time zone as is. |
| Local | The OPC Router converts the timestamp to the time zone stored by the process. |
| UTC | The OPC Router converts the timestamp to the UTC time zone. |
Properties
| Property | Description |
|---|---|
| Name | Any name under which this database server appears in the connection configuration. Choose a name that is unique to you during the connection configuration. |
“Connection Settings” tab
| Property | Description |
|---|---|
| Host/IP/File | Name or IP address of the Microsoft SQL Server |
| Port | Selection of the port for the database connection. The port can be changed if necessary. |
| Data Source | Name of the database to be used. |
| Windows Authentication | If this option is enabled, Windows authentication is used instead of the User and Password fields. Local user accounts, DCOM users, and Windows domain users (NT authentication) are supported. To use Windows authentication, the user under which the OPC Router service is started (by default "System") have access rights to the database. To be able to configure the system, the user under which the configuration environment was started must have access rights to the database. “Test Connection” is not meaningful in this case, since the test is not performed with the “System” user, but with the user who started the configuration environment. Notes on configuring Windows user accounts for the OPC Router. |
| User | Username for logging in to the MS SQL Server |
| Password | Password for logging in to the MS SQL Server |
| Failover Settings | Enter the address and port of a failover server here. (Only the "connection string" is adjusted. The failover cluster configuration is performed directly on the Microsoft SQL Server) |
"Advanced Connection Parameters" tab
| Property | Description |
|---|---|
| Command Timeout | Time in seconds that the router waits for an SQL command to complete. For long-running queries, it may be necessary to increase this value. Please note that the connection timeout must also be increased for connections that trigger long queries. |
| Connection Timeout | Time in seconds that establishing a connection to the database is allowed to take. |
| Concurrent Connections | Number of connections the router is allowed to establish to the database simultaneously. The more concurrent connections allowed, the faster the router will be, but this can slow down the database. Ask your database administrator how many connections they allow the router to use. This naturally also depends on the size of the project. |
| Polling Interval | The interval at which the database is queried. If the broker is enabled in the database, polling occurs automatically. |
“Advanced” Tab
| Property | Description |
|---|---|
| Datetime Handling | see Using Timestamps |
| Bracket Behavior | Property that determines when square brackets should be used in your Microsoft SQL Server plugin. For more information, see here. |
| Parameter Conversion | If this option is enabled, types are converted by the OPC Router; otherwise, they are converted by the target system. |
| Encrypt | This property determines whether encryption is enabled. If the “Trust Server Certificate” option is disabled and the “Encrypt” option is enabled, the server name (or IP address) in an 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 enabled and the "Encrypt" option is disabled, 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 | If this property is enabled, the OPC Router queries the default values of stored procedure parameters and saves them. |
Bracket Behavior
- Enclose in quotes if a dot is present: This mode is intended for backward compatibility. If a dot (
.) is included in a 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 quotes: In this mode, square brackets are never added, regardless of the identifier. This can be useful if you are certain that your identifiers do not contain special characters or spaces.
- Always enclose in quotes: In this mode, square brackets are always added. This is useful if your identifiers may contain special characters, spaces, or reserved words.