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 that is compared with this timestamp must also be used 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 OPC Router must perform data type conversions when transferring data. If no time zone information is available during conversion, 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 it 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 project. Choose a name that is unique to you during connection projecting. |
"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 properties. Local user accounts, DCOM users, and Windowsdomain users (NT authentication) are supported. 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 configure, the user under which the configuration environment was started must have have access rights to the database. "Test connection" is not meaningful in this case, as the test is not performed with the "System" user, but with the user who started the configuration environment |
| User | User name for logging on to 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 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 in connections that trigger long queries. |
| Connection Timeout | Time in seconds that a connection to the database may take. |
| Simultaneous Connections | Number of connections that the router is allowed to establish to the database in parallel. The more simultaneous connections are allowed, the faster the router is, but this can slow down the database. Ask your database administrator how many connections he allows the router to make. This also depends on the size of the project, of course. |
| Polling interval | The interval at which the database is queried. If the broker is activated in the database, polling takes place automatically. |
"Advanced" tab
| Property | Description |
|---|---|
| Datetime handling | See [#using-timestamps](konfiguration.md#using-timestamps "mention") |
| 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, the types are converted by OPC Router; if not, 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 match exactly 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 certification 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 | When this property is enabled, OPC Router queries and stores the default values of stored procedure parameters. |
Bracket behavior
- Enclose in quotation marks if a period is present: This mode is intended for backward compatibility. If a period (
.) is included in an identifier, square brackets are added. This is useful if you have database objects with periods in their names, such asdatabase.schema.[table.name]. - Never enclose in quotes: 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 any 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.