DB - Microsoft SQL Server SSL

2min

Review the Microsoft SQL Server SSL Integration Guide﻿. 
Overview
Microsoft SQL Server SSL integration enables an Edge System to connect with an MSSQL database using SSL authentication. 
Supported Communication
Outbound (Edge to SQL Server) - Yes
Inbound (SQL Server to Edge) - No
Configurations
Variable
Detail
Required
Name
Enter a name for the connector 
Y
Hostname
IP Address or Hostname of MSSQL Server (Must be reachable from the Edge system)
Y
Port
MSSQL Server port
Y
SSL mode
If disable is selected, data sent between client and server is not encrypted. If true is selected, data sent between client and server is encrypted. If false is selected, data sent between client and server is not encrypted beyond the login packet. (default is disable)
Y
CA certificate
SSL CA Certificate (default certificate will be used if this field empty)
N
Username
User name (optional)
Y
Password
User password (optional)
Y
Database
Database name
Y
Table
Table name
Y
Show Mapping
Data from a predefined table could be mapped into a custom one. Example: {"from": "to"} where "from" is a column from predefined table, and "to" is a column from destination table. See Work with Tables in SQL Connectors (Create Table and Show Mapping)﻿ to learn more. 
﻿
Create table
If selected and table doesn't exist, the table is created with the necessary format. See Work with Tables in SQL Connectors (Create Table and Show Mapping)﻿  to learn more. 
N
Commit timeout
Transaction commit timeout (ms)
N
Max transaction size
Maximum number of messages before a transaction is committed regardless of timeout
N
Bulk insert count
To enable this option, enter the number of messages to group together and send as one bulk insert statement. Enabling this option can improve how quickly data is processed when dealing with high volumes of tags.
N
Throttling limit
Set a limit on the number of outbound messages per second. If sending data to a server that has restrictions on the rate of incoming messages, this ensures that no messages are lost.
N
Persistent storage
Persistent storage: If enabled, messages in the outbound queue are stored on disk. If there are interruptions in connection or power loss, messages will be retained and eventually sent when the connection is restored.
N
Queue mode
Select the sequence of data transfer: FIFO or LIFO. Selecting FIFO (First In First Out) will send the earliest message first. Selecting LIFO (Last In First Out) will send the latest message first.
N
﻿
Note: When the Create table checkbox is not selected, the table format (columns) that you will create must still adhere to the following format. 
SQL
IF NOT EXISTS(SELECT *
              FROM sysobjects
              WHERE NAME = '"TABLE_NAME"'
                AND xtype = 'U')
CREATE TABLE "TABLE_NAME"
(
    id          BIGINT IDENTITY(1,1) NOT NULL,
    record_uuid CHAR(36)       NOT NULL,
    arrived_at  DATETIME       NOT NULL,
    device_id   VARCHAR(64)    NULL,
    register_id VARCHAR(64)    NULL,
    tag_name    VARCHAR(64)    NULL,
    datatype    VARCHAR(32)    NULL,
    value       TEXT           NULL,
    success     BIT            NULL,
    PRIMARY KEY (id)
)
IF NOT EXISTS(SELECT *
              FROM sysobjects
              WHERE NAME = '"TABLE_NAME"'
                AND xtype = 'U')
CREATE TABLE "TABLE_NAME"
(
    id          BIGINT IDENTITY(1,1) NOT NULL,
    record_uuid CHAR(36)       NOT NULL,
    arrived_at  DATETIME       NOT NULL,
    device_id   VARCHAR(64)    NULL,
    register_id VARCHAR(64)    NULL,
    tag_name    VARCHAR(64)    NULL,
    datatype    VARCHAR(32)    NULL,
    value       TEXT           NULL,
    success     BIT            NULL,
    PRIMARY KEY (id)
)
﻿
﻿

Variable	Detail	Required
Name	Enter a name for the connector	Y
Hostname	IP Address or Hostname of MSSQL Server (Must be reachable from the Edge system)	Y
Port	MSSQL Server port	Y
SSL mode	If disable is selected, data sent between client and server is not encrypted. If true is selected, data sent between client and server is encrypted. If false is selected, data sent between client and server is not encrypted beyond the login packet. (default is disable)	Y
CA certificate	SSL CA Certificate (default certificate will be used if this field empty)	N
Username	User name (optional)	Y
Password	User password (optional)	Y
Database	Database name	Y
Table	Table name	Y
Show Mapping	Data from a predefined table could be mapped into a custom one. Example: {"from": "to"} where "from" is a column from predefined table, and "to" is a column from destination table. See Work with Tables in SQL Connectors (Create Table and Show Mapping) to learn more.
Create table	If selected and table doesn't exist, the table is created with the necessary format. See Work with Tables in SQL Connectors (Create Table and Show Mapping) to learn more.	N
Commit timeout	Transaction commit timeout (ms)	N
Max transaction size	Maximum number of messages before a transaction is committed regardless of timeout	N
Bulk insert count	To enable this option, enter the number of messages to group together and send as one bulk insert statement. Enabling this option can improve how quickly data is processed when dealing with high volumes of tags.	N
Throttling limit	Set a limit on the number of outbound messages per second. If sending data to a server that has restrictions on the rate of incoming messages, this ensures that no messages are lost.	N
Persistent storage	Persistent storage: If enabled, messages in the outbound queue are stored on disk. If there are interruptions in connection or power loss, messages will be retained and eventually sent when the connection is restored.	N
Queue mode	Select the sequence of data transfer: FIFO or LIFO. Selecting FIFO (First In First Out) will send the earliest message first. Selecting LIFO (Last In First Out) will send the latest message first.	N

Updated 23 Sep 2024

DB - Microsoft SQL Server

DB - MongoDB