This article provides some configuration support for implementation and maintenance of a MS SQL database for 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 database server be kept stable. It is also important to plan SQL server outages accordingly so as to minimize disrupting SystemWeaver users. Any database outages, planned or unplanned will disrupt users.

Low Latency

When clients perform Write operations, the SystemWeaver server must connect to the MS SQL server Because it is external, i.e., resides on a different server than the SystemWeaver server application, there will be latency. Keep this in mind when determining the location of the MS SQL server in relation to the SystemWeaver server.

Having the SQL 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.

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

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 13 for SQL Server® - 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

Blob size is Too Large to Handle