Microsoft SQL Server Plug-in
The Microsoft SQL Server Plug-in in OPC Router enables communication with Microsoft SQL servers. With this plug-in, you can read data from existing Microsoft SQL databases, write new data, modify 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 Server. The plug-in supports various SQL commands and queries. These can be created graphically.
Required roles and rights for database transfer objects
| Action/Transfer Object | 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 specified time. In the case of the OPC Router, a timeout may occur when communicating with Microsoft SQL Server.
Types of Timeouts
- Connection Timeout: The connection timeout is the maximum time that the OPC Router waits for a connection to Microsoft SQL Server.
- Command Timeout: The command timeout is the maximum time that 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 cause errors.
Causes of timeouts
- High utilization of Microsoft SQL Server: If Microsoft SQL Server is heavily utilized, delays in the execution of SQL commands may occur.
- Network problems: If the network connection between the OPC Router and the Microsoft SQL Server is disrupted, delays or failures in communication may occur.
- Faulty SQL commands: If an SQL command is faulty, it cannot be executed by the SQL Server.
Performance in Microsoft SQL Server
The performance of Microsoft SQL Server is important for OPC Router because it uses the database to store and retrieve data. If the performance of the SQL Server is impaired, this can lead to delays in 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 records.
- Fragmentation: Fragmentation is a condition in which the data in a table is not stored in a continuous sequence. This can affect query performance because the SQL Server needs more time to read the data.
- Data organization: Data organization is another important factor that can affect the performance of the SQL Server. Efficient data organization can help SQL Server find and process data faster.
Maintaining Microsoft SQL Server is important for the performance of the OPC Router.