- 4 minutes to read

About Microsoft SQL Server

On this page, you will learn about some important, and different options for using Nodinite with Microsoft SQL Server

SQL Server Versions and Editions

Any of the following Enterprise and Standard SQL Server versions can be used with Nodinite

SQL Server Version Always On support Cluster support Stand alone
2019
2017
2016
2014
2012
2008 R2

Developers can use the Developer edition

If you have the need for Logging then you will most probably always need the Enterprise or Standard edition.

Enterprise Standard Express
Maximum Database Size 524 PB 524 PB 10 GB
CPUs/Cores OS Max 4 sockets or 24 cores 1 socket or 4 cores
Max Memory OS Max 128 GB 1.4 GB
SQL Profiler

SQL Server Always On

There is a specific user guide for Microsoft SQL Server Always on and Nodinite, please make sure to read it very carefully if this is how you intend to host Nodinite

SQL Express

SQL Express has some limitations and is generally not recommended if you have the need for Logging. If you intend to use Nodinite only for documentation and Monitoring then you can most probably get away with the SQL Server Express edition.

SQL Server Version Always On support Cluster support Stand alone
2019
2017
2016
2014
2012
2008 R2
The 10 GB limit for SQL Express makes it a bad candidate for logging
SQL Express 2019 can be "upgraded" to a standard edition. This means you can often start cheap and expand with your business needs

Installation Path

If Nodinite is not installed on the same Windows Server as SQL Server, then you can install SQL Server in any folder. However, if you have multiple Nodinite application servers you need to be aware that Nodinite uses some System Parameters. Please review the SqlPackageExecutable user guide for an example.

Security

Next you will find some details regarding security options for your SQL Server environment.

Account for SQL Server Instance

Generally, it is a good idea to run the SQL Server instance with an AD service account (and not a local account). By doing so the SPN should be properly registered in the Active Directory which is also required in order for Kerberos to fully function, which in turn is a requirement Trusted for delegation

Kerberos

Kerberos is enforced by Windows and often becomes a problem when services traverse multiple servers across the network. The following features must all be properly configured:

Trusted for delegation

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

SPN

When Nodinite uses a Linked Server the SQL Server instance (Windows Service) must have its Service Principal Name (SPN) properly registered. Review the Account for SQL Server Instance paragraph for additional information.

Linked Server

Review the Linked Servers section

DTC

Review the MSDTC user guide

The type of DTC configuration varies with the type of configuration for SQL Server

Performance optimizations

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

  • Nodinite should run in dedicated SQL instances to guarantee/dedicate HW resources and avoid competing demands for resources, aid in the troubleshooting and avoid blame game situations
    • 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 Server instances hosting the Nodinite Log Database

Repeat the optimizations below on ALL SQL Server instances (Log Database, BizTalk Server 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 a large number of messages logged and processed may need additional RAM
  • The Nodinite Log Databases, when used together with BizTalk Server, the Simple recovery mode option can be used to increase the general performance

TempDB optimization

  • For each core assigned with your SQL Instance (up to 8), create 1 tempdb file with at least 128 MB in size (no auto growth)

    For example, if you have 14 cores, this means that you should have 8 tempdb files. Putting the tempdb files on different volumes may increase the overall performance of your system

Backups

Make sure to backup all your Nodinite databases and make the documentation for the restore operation available to stakeholders