- 3 minutes to read

Release Notes

SQL Server Configuration Example

On this page you will learn how to configure the Nodinite Pickup Logging Service to fetch Nodinite JSON Log Event from an SQL Server database.

graph LR subgraph "SQL Server" roS(fal:fa-database Database Table) end subgraph "Nodinite instance" roPS(fal:fa-truck-pickup Pickup Service) roS --> roPS roPS --> |Long term storage|roDB(fal:fa-database Log Database) end

SQL Servers

The SQL Servers section is where you manage SQL Server Queue related sources.

The configuration file (Settings.json), is in JSON format, the default path is:

C:\Program Files\Nodinite\Logging Agent - Pickup Service\Settings.json

{
  ...
  "SQLServers": [
    {
      "Enabled": true,
      "LogApi": "https://localhost/Nodinite/Dev/LogAPI/",
      "UseConfigurationDatabase": false,
      "ConfigurationDatabaseConnectionString": null,
     |**ConnectionString**|SQL Server Database Connection string |`Server=myServerAddress;Database=myDataBase;Integrated Security=SSPI;Connection Timeout=60;TrustServerCertificate=true`||
|**MaxRowsLimit**|The maximum number of rows to fetch on each loop|500 (default)|Using larger values will impact the memory used|

      "ReplaceRules": [
        {
          "Name": "Fix Endpoint real customer id to {customerId}",
          "ReplaceName": false,
          "ReplaceUri": true,
          "Pattern": "/([0-9]{4,})$",
          "Group1Value": "{customerId}"
        }
      ]
    }
  ]
  ...                      
}

SQL Servers is an array of a SQL Server configuration. Each configuration is valid for one SQL Server database. This configuration tells the Log Agent where to look for log messages with the Nodinite JSON Log Events.

Property Description Value Example Comment
ConnectionString SQL Server Database Connection string Server=myServerAddress;Database=myDataBase;Integrated Security=SSPI;Connection Timeout=60;TrustServerCertificate=true
MaxRowsLimit The maximum number of rows to fetch on each loop 500 (default) Using larger values will impact the memory used
Enabled Review the Shared Configuration section for additional information
LogAPI Review the Shared Configuration section for additional information
UseConfigurationDatabase Review the Shared Configuration section for additional information
ConfigurationDatabaseConnectionString Review the Shared Configuration section for additional information
ReplaceRules Review the shared Replace Rules section for additional information

Important

You must restart the Nodinite Pickup Logging Service for the changes to the configuration file to be operational.

SQL Server table

If you are using a Microsoft SQL Server database to store Log Events then your custom logging solution must have the following LogEvents table defined as:

Column Datatype Purpose
LogEvent json Your JSON encoded Log Event goes here
Id bigint Automatically created identity, the PickupService must know which row to flag as invalid if value in LogEvent column is not valid
ValidationFailed boolean Automatically set to false when inserted and might later be changed to true during processing if any errors are detected
ValidationFailedText text Column data is Updated if errors occur validating the LogEvent during processing
Created timestamp Automatically created during insert, might come in handy when troubleshooting logged but invalid log events

Important

Invalid Log Events will get the ValidationFailed column set to True and you must rid these entries manually.

-- Table: [LogEvents], used by :Nodinite: Logging

-- DROP TABLE [dbo].[LogEvents];

CREATE TABLE [dbo].[LogEvents](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[LogEvent] [nvarchar](max) NOT NULL,
	[ValidationFailed] [bit] NOT NULL,
	[ValidationFailedText] [nvarchar](max) NULL,
	[Created] [datetimeoffset](7) NOT NULL,
 CONSTRAINT [PK_LogEvents] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 7) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[LogEvents] ADD  CONSTRAINT [DF_LogEvents_ValidationFailed]  DEFAULT ((0)) FOR [ValidationFailed]
GO

ALTER TABLE [dbo].[LogEvents] ADD  CONSTRAINT [DF_LogEvents_Created]  DEFAULT (sysutcdatetime()) FOR [Created]
GO

-- Do not forget to assign user access rights

Important

Remember to assign the service account for the Pickup Service the following rights:

  • db_datareader
  • db_datawriter

Next Step

Configure