Prerequisites for the Log Database
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.
|Trusted for delegation|
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)
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: 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)
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
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.
In order for Kerberos to function, the included Windows Servers must be properly configured.
- All node names and cluster names for the Servers (BizTalk, SQL, Nodinite) must have the Trusted for delegation option set in the Active Directory. For more information see https://docs.microsoft.com/en-us/microsoft-desktop-optimization-pack/appv-v4/how-to-configure-the-server-to-be-trusted-for-delegation
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)
When running Nodinite in a distributed environment, Kerberos requires all SQL Instances (both physical node names and cluster names) have its SPNs registered in Active Directory.
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 Log Database installed on one Windows Server with a SQL default instance and BizTalk in another two node fail over cluster running 2 SQL instances (1 default and 1 named instance), one for other BizTalk databases and one dedicated for the messagebox (BizTalkMSGBoxDb) you will have to register 7 SPNs in total (1 for Nodinite, 3 for the BizTalk instance, 3 for the BizTalk Messagebox instance)
- SQL Server name for default instance with Nodinite Log Database
- SQL Server first node name for default instance with BizTalk databases
- SQL Server second node name for default instance with BizTalk databases
- SQL Server cluster name for default instance with BizTalk databases
- SQL Server first node name for named instance with messagebox database
- SQL Server second node name for named instance with messagebox database
- SQL Server cluster name for named instance with messagebox database
The following example registers accountname for the default SQL Instance using an elevated command prompt (requires 'Domain Admin' rights):
setspn -A MSSQLSvc/myhost.redmond.microsoft.com accountname
Using Always on? Please review the steps outlined in the Always On | Kerberos section
The following example registers accountname for 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
- Logging Service - SQL rights
- Monitoring Service - SQL rights
- Web API - SQL rights
- Log API - SQL rights
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.
These ports should always be allowed:
|53||DNS||The Agent needs to know where your other servers/services are (can sometimes optionally be solved using entries in the local hosts file), review the following 'Microsoft' user guide|
|88||Kerberos||Review 'Microsoft Kerberos' user guide|
- TCP Ports between Core Services and Log Databases
- TCP Ports between Log Database and Log Databases / BizTalk SQL Server
|135||DTC/RPC||This port is shared between many Windows Services|
|1433/...||SQL Server instance ports (multiple)||Depends on policies and settings on target environment. Please review the How to configure RPC dynamic port allocation to work with firewalls user guide|
The following Windows Components are accessing the Log Database and used/configured ports must be allowed, follow each link for details:
- All servers with Core Services and SQL Server with Log Database must be Trusted for delegation
- Windows rights
- SQL rights
- SPN registered
Prior to installing Nodinite a SQL DBA must add the following linked servers (match the conditions that apply for your environment):
- remote SQL Server (with historical databases)
- remote SQL Server\instancename (with historical databases)
- remote BizTalk SQL Server with:
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;
Read more about the RPC and RPC Out option in the 'About Linked Server RPC and RPC OUT option' user guide.
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.
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.
If your SQL Server environment is configured for Always on availability groups then additional configuration is required:
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
Remember SQL accounts are not replicated between the nodes
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.
Use the following command
setspn -l [Domain\User] (replace [Domain\User] with the account for the SQL Instance). The list should contain at least:
- NodeB:50123 ...
- NodeB:InstanceName ...
For a 2 Node AOAG example the command would yield:
MSSQLSvc/Clustername:1433 MSSQLSvc/Clustername MSSQLSvc/NodeA:50123 MSSQLSvc/NodeB:50123 MSSQLSvc/NodeA:InstanceName MSSQLSvc/NodeB:InstanceName
If your are using DNS suffix the list is doubled with each entry with it's DNS suffix added.