Prerequisites for the Log Database

This page describes the prerequisites to successfully install and run 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 at 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, and antivirus/antimalware you may end up spending a lot of hours to get every piece of the puzzle in place.

Rest assured, 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 required services mature, one piece at a time. All, to make sure you get a future-proof solution for your business.

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

Please review the Microsoft SQL Server user guide for general information.

It's you (or your IT-organization/hosting partner/...) responsibility to make sure a backup of the Log Database is regularly taken, at least once per day

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

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 operational

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 performs the remote operation. For the Windows Integrated Security to be able to pass and authenticate the user identity across servers all tasks outlined in the checklist at the beginning of this 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

This topic is detailed in the Trusted for delegation user guide.

Register SPN

When running Nodinite in a distributed environment, Kerberos requires all SQL Instances (both physical node names and cluster names) to 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)

1. SQL Server name for default instance with Nodinite Log Database
2. SQL Server first node name for default instance with BizTalk databases
3. SQL Server second node name for default instance with BizTalk databases
4. SQL Server cluster name for default instance with BizTalk databases
5. SQL Server first node name for named instance with messagebox database
6. SQL Server second node name for named instance with messagebox database
7. SQL Server cluster name for named instance with messagebox database

SQL Server Default Instance

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

setspn -A MSSQLSvc/myhost.redmond.microsoft.com account name

Using Always on? Please review the steps outlined in the Always On | Kerberos section

SQL Server Named Instance

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

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 the linked server from the Configuration Database using as default the Windows Service Account configured.

• Logging Service - SQL rights
• Monitoring Service - SQL rights
• Web API - SQL rights
• Log API - SQL rights

What Firewall settings are required for the Log Database?

The Log Database require 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:

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

1. TCP Ports between Core Services and Log Databases
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 Nodinite Core Services and Log Databases

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:

• DTC
• Trusted for delegation
• Windows rights
• SQL rights
• SPN registered

Linked Servers

Before installing Nodinite, a SQL DBA must add the following Linked Servers (match the conditions that apply for your environment):

• Default Instance
• Named Instance

If you have remote Databases (BizTalk BizTalkDTADb, BizTalk BizTalkMGMTDb, and Nodinite Log Databases), you will need to add a Linked Server to every remote SQL instance on each node where applicable

All linked server configurations must have the Security option 'Be made using the login's current security context' and the two server options RPC enabled

To add a linked server, use the following template commands (replace %LOCALHOST\instance% as necessary):

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 (SSMS) you should now be able to retrieve the list of databases (including BizTalk management database and BizTalk tracking database) available on the BizTalk Server SQL instances.

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 login used with the 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 bypassing Kerberos related problems. We urge you to only use this solution as a temporary solution until you have resolved your Kerberos related problems.

Default instance

Named instance

Always-on

If your SQL Server environment is configured with Always on availability groups, then additional configuration is required.

Fail-over Cluster

If your SQL Server environment is configured with fail-over clustering, then you need to review the articulated details for MSDTC and Linked Servers

SQL Accounts

Remember that the SQL accounts are not replicated between the nodes

SPN

To make us of the Kerberos security protocol, you must properly register the SPNs.

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 an 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:

• Clustername
• Clustername:1433
• NodeA:50123
• NodeB:50123 ...
• NodeA:InstanceName
• NodeB:InstanceName ...

Example for a two Node AOAG environment:

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

Frequently asked questions

Common problems and the FAQ for the Log Database can be found on the Troubleshooting page.

Next Step

Install Nodinite
System Parameters

Related

• Core Services
• Log Database