Last updated: 2019-07-02

Prerequisites for the Log Database

This page describes the prerequisites for successfully installing and running the Nodinite Log Database. The Log Database is a SQL Server database and is installed in a SQL Server instance.

Nodinite Log Database Checklist

The Log Database sits in the end of the "spider web" and on a single box machine you may have virtually no administration at all to get everything working. On the other hand, in a locked down distributed environment spanning multiple servers with network load balancing, firewalls, network zones (WLAN's), domains, DNS, group policies, anti virus/antimalware you may end up spending a lot of hours to get every piece of the puzzle in place.

Rest assure, Nodinite is built on Microsoft standard products and these form the very foundation for most enterprise business applications today. We are working hard on cloud enabling Nodinite as the required services mature one piece at a time to make sure you get a future proof solution for your business.

Verified Topic
SQL Server
MSDTC
Windows rights
Trusted for delegation
Register SPN
Database rights
Firewall
Linked Server(s)

Use the checklist above to verify that you have performed all steps required to get Nodinite flying (most probably already managed when you performed similar tasks for the Configuration Database)


SQL Server

Nodinite is designed to be implemented according to customer needs and all modern SQL Server versions are supported (Enterprise, Standard, Express):

  • SQL Server 2017
  • SQL Server 2016
  • SQL Server 2014
  • SQL Server 2012
  • SQL Server 2008 R2

Note: Always On is supported on SQL Server 2016 and later versions

Note: It's you (or your IT-organization/hosting partner/...) responsibility to make sure the Log Database is regularly being backuped, at least once per day

You can run SQL Server on Windows Servers implemented on physical machines, virtual machines and in the cloud. Actual Windows version used must match the prerequisites for SQL Server version being used.

To maximize your experience with Nodinite you should implement all suggestions below:

  • Nodinite should run in dedicated SQL instances in order to guarantee/dedicate HW resources and avoid competing demands for resources, aid in the troubleshooting and avoid blame game situations
    • At least assign dedicated disk volumes for the Log Database to make sure extended logging does not fault any other system/service
  • Keep the Logging Service close to the SQL Servers hosting the Log Database for Nodinite

Do repeat optimizations below on ALL SQL Server instances (Log Database, BizTalk Databases)

  • Use -T1118 Trace flag on SQL Instances - Optimizes TEMPDB
  • Disable PAC Verification if your policy allows for this option to be set on all Windows Servers running any of Nodinite Core Services - Less RPC calls, improves performance
  • Nodinite should be installed on machines with dedicated Windows swap volumes (>2,5*physical RAM) and SQL discs should have > 300 MB/S R/W.
  • There should be a secured backup volume (or network share) available with sufficient free space for Nodinite databases
  • Windows Servers hosting any of the Core Services should have at least 16GB RAM. Environments with large number of messages logged and processed may need additional RAM
  • For each core assigned with your SQL Instance (up to 8), create 1 tempdb file with 128 MB in size (no auto growth)

    If you have 14 cores that means you have 8 tempdb files. Putting the tempdb files on different volumes may increase overall performance of your system

  • Nodinite Log Databases when used together with BizTalk should be kept in Simple recovery mode (default)

Microsoft Distributed Transaction Coordinator (DTC)

The Log Database is involved in all SQL Server related operations and Nodinite uses the Windows Service Microsoft Distributed Transaction Coordinator (DTC) that is responsible for coordinating transactions that span multiple resource managers. We have written a dedicated tutorial for Nodinite with our best practices for how to install and configure the DTC Windows Service.

You must configure the DTC as documented otherwise Nodinite will not be able to function

What Windows rights does the Log Database require?

Nodinite keeps the identity of the user account for running Core Services related threads when traversing servers on your network. This means for example that when the Logging Service fetches data from Log Databases and/or BizTalk tracking database it will be the configured account that actually performs the remote operation. In order for the Windows Integrated Security to be able to pass and authenticate the user identity across servers all tasks outlined in the checklist in the beginning of thus tutorial must be properly configured.

The Log Database is a SQL Server database and is installed as part of the Core Services package. Since the Log Database is a SQL Server database You must use a Windows account that has been configured with the appropriate rights in SQL Server, see SQL Server database rights.

Trusted for delegation

In order for Kerberos to function, the included Windows Servers must be properly configured.

sequenceDiagram opt Kerberos TGT Logging Service ->> Domain Controller: Service account credentials Domain Controller ->> Logging Service: Kerberos TGT end opt Service ticket Logging Service ->> Domain Controller: Send TGT to request a service ticket Domain Controller ->> Logging Service: service ticket end Logging Service ->> SQL (Log Database): Client TGT and service ticket opt SQL service ticket SQL (Log Database) ->> Domain Controller: Clients TGT to request a service ticket Domain Controller ->> SQL (Log Database): service ticket end SQL (Log Database) ->> BizTalk SQL (BizTalkMgmtDb): Client TGT and SQL service ticket BizTalk SQL (BizTalkMgmtDb) ->> SQL (Log Database): Response SQL (Log Database) ->> Logging Service: Response

anonymous Common error for problems related to not having trusted for delegation

Note: A restart is required if this settings is changed in the Active Directory

Make sure to run the following command to make sure your server gets the new configuration (allow some time for your Domain Controllers to replicate new setting first)

gpupdate /force

Register SPN

Using Always on? make sure to read additional section for Kerberos When running Nodinite in a distributed environment, Kerberos requires the SQL Server Service Accounts have its SPNs for the SQL Services (cluster, nodes, ...) registered in Active Directory.

Watch this video to learn more about Kerberos.

Example1: If you have a single box server with BizTalk, SQL Server and Nodinite you don't have to register the SPN (not a distributed environment)

Example2: If you have Nodinite Configuration database and Log Databases on one Microsoft Fail Over SQL Server cluster and BizTalk Server on another SQL Server in the network.

graph LR subgraph Nodinite AppServer roLS(fal:fa-hdd Logging Service) end subgraph Nodinite SQL Server roNI(fal:fa-database Nodinite) roLS --> roNI end subgraph BizTalk SQL Server roBT1(fal:fa-cog BizTalkMGMTDb) roBT2(fal:fa-envelope BizTalkDTADb) roNI -->|Linked Server| roBT1 roNI -->|Linked Server| roBT2 end

Example of distributed SQL Server configuration with a jump from Nodinite databases to BizTalk Server databases used by the Logging Service

Databases are accessed from the Logging Service using credentials for the Windows Service first onto the Nodinite SQL Server instance and then "jumps" over the network to access the BizTalk Server databases (using the Linked Server configuration). This jump will fail if the SQL Account for the SQL Service is not allowed to delegate the credentials. The SQL Account for the SQL Server Service with Nodinite databases must be trusted for delegation within the Active Directory Computer object (Clustername and node names) for the following SPN's:

Name Description
Clustername The SQL cluster Role name from Windows fail over cluster setup
Clustername.FQDN The SQL cluster Role name from Windows fail over cluster setup with FQDN
...
NodeA Name of fail over cluster Node A
NodeA.FQDN Name of fail over cluster Node A using FQDN
...
NodeA Name of fail over cluster Node B
NodeA.FQDN Name of fail over cluster Node B using FQDN
...

Example SPN's required for Nodinite SQL Instance running on Microsoft fail over cluster

SQL Server Default Instance

The following example registers the SPN for the accountname running the default SQL Instance using an elevated command prompt (requires 'Domain Admin' rights):

setspn -A MSSQLSvc/myhost.redmond.microsoft.com accountname

SQL Server Named Instance

The following example registers the SPN for the accountname running the named SQL Instance using an elevated command prompt (requires 'Domain Admin' rights): repeat for each combination of named instance /accountname)

setspn -A MSSQLSvc/myhost.redmond.microsoft.com:instancename accountname

What SQL Rights does the Log Database require?

For performance reasons the following Core Services accesses the Log Databases through linked server from the Configuration Database using as default the Windows Service Account configured.

graph LR subgraph SQL Server roConfigDatabase(fal:fa-database Configuration Database) --- |Linked Server| roLogDatabase(fal:fa-database fal:fa-database fal:fa-database Log databases) end

What Firewall settings are required for the Log Database?

The Log Database requires both inbound and outbound ports to be opened. Since Nodinite is highly configurable, the actual ports in use may differ from what's being exampled here.

The following image shows which Core Services are using the Log Database through linked servers from the Configuration Database:

graph LR subgraph SQL Server roConfigDatabase(fal:fa-database Log Database) --- |Linked Server| roLogDatabase(fal:fa-database fal:fa-database fal:fa-database Log databases) end subgraph Application Server roLogAPI(fal:fa-cloud-download Log API) --- |SQL, DTC, DNS, RPC, ...|roConfigDatabase roWebAPI(fal:fa-cloud Web API) roWebAPI --- |SQL, DTC, DNS, RPC, ...|roConfigDatabase roLoggingService(fal:fa-hdd Logging Service) --- |SQL, DTC, DNS, RPC, ...|roConfigDatabase roMonitoringService(fal:fa-watch-fitness Monitoring Service) --- |SQL, DTC, DNS, RPC, ...|roConfigDatabase end
  1. TCP Ports between Log Database and Core Services
  2. TCP Ports between Log Database and Log Databases / BizTalk SQL Server

The Log API, Web API, Logging Service and the Monitoring Service accesses the Log Database using the configured Windows Service Account.

1. TCP Ports between Log Database and Core Services

  • RPC Ports, kerberos 88 TCP
  • SQL Server ports, usually 1433, depends on your actual configuration
  • DTC - Facilitates transactional support
  • DNS - Windows needs to know where your servers are (can of course also be solved using hosts)
    • 53 both TCP/UDP

2. TCP Ports between Log Database and Log Databases / BizTalk SQL Server

The following Windows Components are accessing the Log Database and used/configured ports must be allowed, follow each link for details:

graph LR subgraph SQL Server Hotel roLogDatabase2(fal:fa-database fal:fa-database fal:fa-database Log databases) end subgraph SQL Server roConfigDatabase(fal:fa-database Log Database) --- |Linked Server| roLogDatabase(fal:fa-database fal:fa-database fal:fa-database Log databases) end subgraph BizTalk SQL Server roBizTalkMGMTDB(fal:fa-database BizTalkMGMTDb) end subgraph BizTalk SQL Server 2 roBizTalkDTADb(fal:fa-database BizTalkDTADb) end roConfigDatabase -.Linked Server.-roBizTalkMGMTDB roConfigDatabase -.Linked Server.-roBizTalkDTADb roConfigDatabase -.Linked Server.-roLogDatabase2

Prior to installing Nodinite a SQL DBA must add the following linked servers (match the conditions that apply for your environment):

  • localhost
  • localhost\instancename
  • remote SQL Server (with historical databases)
  • remote SQL Server\instancename (with historical databases)
  • remote BizTalk SQL Server with:
    • BizTalkDTADb
    • BizTalkMGMTDb
If you are using SQL Server Always on availability groups make sure to follow the additional instructions on this page

The BizTalk database(s) named above must be added as linked servers (even for localhost). Make sure to set the 'be made using the login's current security context' option on the security setting of the linked server connection.

To add a linked server in SQL use the following commands:

EXEC sp_addlinkedserver @server = 'LOCALHOST\instance';
EXEC sp_serveroption 'LOCALHOST\instance', 'rpc', true;
EXEC sp_serveroption 'LOCALHOST\instance', 'rpc out', true;

Running the SQL MMC you should now be able to retrieve the list of databases (including BizTalk management database and BizTalk tracking database) available on the BizTalk SQL instances.
linkedserver

Note: For single box solutions the Kerberos security protocol is not activated, however for distributed solutions, for example when Nodinite is installed on one (or more) server(s) and the databases are located elsewhere, the logins used for linked server must use the Kerberos protocol. Using SQL Server in mixed mode allows you to impersonate a Windows account to use a SQL account totally bypassing Kerberos related problems. We urge you to only use this solution as a temporary solution until you have resolved your Kerberos related problems.

Always on

If your SQL Server environment is configured for Always on availability groups then additional configuration is required:

Linked Serves

the linked server configuration must be setup as follows:

  • On each and every SQL node
  • Use the cluster name and if applicable the instance name

SQL Accounts

Remember SQL accounts are not replicated between the nodes

Kerberos

These settings apply on all SQL Instances where Nodinite databases are hosted In order for Kerberos to work with Always On additional steps must be taken described here. Even though the SQL instance is configured to use a specific port, for example 50123 the listener still uses 1433 (default) and hence there must be a SPN registered for this configuration.

According to best practices the DNS suffix (FQDN) should be included doubling the number of SPN's.

The following names must be registered, replace 50123, and FQDN as appropriate for your environment

Name Description
Clustername The AOAG name (should not include the instance name!)
Clustername:1433 Default listener address using port 1433
Clustername.FQDN Default listener address using FQDN
Clustername.FQDN:1433 Default listener address using FQDN and port 1433
...
NodeA:50123 Name of Node A using example port 50123
NodeA.FQDN:50123 Name of Node A using FQDN and example port 50123
NodeA:InstanceName Name of Node A with SQL Instance name
NodeA.FQDN:InstanceName Name of Node A with FQDN and SQL Instance name
...
NodeB:50123 Name of Node B using example port 50123
NodeB.FQDN:50123 Name of Node B using FQDN and example port 50123
NodeB:InstanceName Name of Node B with SQL Instance name
NodeB.FQDN:InstanceName Name of Node B with FQDN and SQL Instance name
...

Use the following command setspn -l [Domain\User] (replace [Domain\User] with the account for the SQL Instance). The list should contain at least: For a 2 Node AOAG setup, the command setspn -l ... would yield (at least 12 entries):

MSSQLSvc/Clustername
MSSQLSvc/Clustername:1433
MSSQLSvc/Clustername.FQDN
MSSQLSvc/Clustername.FQDN:1433
MSSQLSvc/NodeA:50123
MSSQLSvc/NodeA.FQDN:50123
MSSQLSvc/NodeB:50123
MSSQLSvc/NodeB.FQDN:50123
MSSQLSvc/NodeA:InstanceName
MSSQLSvc/NodeA.FQDN:InstanceName
MSSQLSvc/NodeB:InstanceName
MSSQLSvc/NodeB.FQDN:InstanceName

Watch this video to learn more about Kerberos.

Frequently asked questions

Common problems and FAQ for the Log Database can be found in the troubleshooting page.

Where do I add my custom built Search Field Plugins?

You simply copy the DLL to the 'Plugins' folder of the Log Database. If the DLL is being replaced then you must restart the Log Database.

Note: Make sure the DLL after the copy paste operation is not blocked by Windows. Right click on the DLL and select properties. Click on the Unblock button if it exists


Next Step

Install Nodinite
System Parameters