This article provides some configuration and optimization considerations when using an MS SQL or PostgreSQL database with SystemWeaver.
Ensure Stable Connection
To avoid unwanted consequences (e.g.,corrupt data) caused by the SystemWeaver server attempting to continue to run during network outages, SQL server patch, etc., the server will shut down and must be restarted again if the environment is unstable. To avoid unplanned restarts, it is of extreme importance that the connection between the SystemWeaver server and the MS SQL or PostgreSQL database server be kept stable. It is also important to plan database server outages accordingly so as to minimize disrupting SystemWeaver users. Any database outages, planned or unplanned will disrupt users.
| Tip: To guarantee a stable connection, consider using an SQLite database. |
Low Latency
When clients perform Write operations, the SystemWeaver server must connect to the MS SQL or PostgreSQL server. Because it is external, i.e., resides on a different server machine than the SystemWeaver server application, there will be latency. Keep this in mind when determining the location of the MS SQL or PostgreSQL server in relation to the SystemWeaver server.
Having the MS SQL or PostgreSQL server on the same machine or close by the SystemWeaver server application will improve the latency. The best write performance is with an SQLite database on local SSD disk.
For MS SQL Database Only:
Max Text Replication Size
The max text repl size option specifies the maximum size (in bytes) of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data that can be added to a replicated column or captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement. The default configuration for this setting in MS SQL is 65536. Consider increasing this size if users will be adding data in excess of that.
Configure the max text repl size Server Configuration Option
Database Authentication
The DatabaseUser entered in the server configuration must have sufficient authorization in the SQL database. You can use a service account in Active Directory for MS SQL server authentication.
ODBC Driver (R52 and earlier releases only)
To avoid server stops due to attempts to save too large of an amount of data to a text or image column in MS SQL, be sure to upgrade to a newer version of Microsoft ODBC Driver for SQL Server, e.g., Microsoft® ODBC Driver for SQL Server® - version 13 or later. There is a Check max blob size tool in the swDatabaseManager utility to test importing large data. To test, click the button. You do not need sole access to the database. The ODBC driver is installed on the machine where the SystemWeaver server application is installed.
Blob Size is OK
