About using Microsoft SQL Server with Nodinite
On this page, you will learn about some important prerequisites and options for installing and 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 up to the specified version.
- SQL Express is supported
- SQL Server Managed Instance (Azure) is supported
- Azure SQL Databases are currently NOT supported
| SQL Server Version | Always On support | Cluster support | Stand alone | |
|---|---|---|---|---|
| SQL Server Managed Instance (Azure) | >=6.1 | |||
| 2022 | ->6 | |||
| 2019 | ->6 | |||
| 2017 | ->6 | |||
| 2016 | ->6 | |||
| 2014 | ->6 | |||
| 2012 | ->6 | |||
| 2008 R2 | ->5.4 |
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. If this is how you intend to host Nodinite, please make sure to read it very carefully .
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 | Nodinite Version |
|---|---|---|---|---|
| 2022 | -> 6 | |||
| 2019 | -> 6 | |||
| 2017 | -> 6 | |||
| 2016 | -> 6 | |||
| 2014 | -> 6 | |||
| 2012 | -> 6 | |||
| 2008 R2 | ->5.4 |
Warning
The 10 GB limit for SQL Express makes it a bad candidate for logging.
Tip
SQL Express 2019 and later 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.
Collation
Nodinite should be installed on a SQL Server instance with a collation of type CI_AS (Case Insensitive, Accents Sensitive).
The sort order is determined by SQL Server, NOT in code. Depending on your preferred language, you may install Nodinite according to your preference, policy and business requirements.
You can read more about SQL Server Collations here
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 be fully operational, that in turn, is also 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:
- SPNs
- Trusted for delegation
- Firewalls
- Authentication and Authorization
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(s) 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:
Note
The type of DTC configuration, vary with the type of configuration for SQL Server and the policies of your organization.
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 (<2016) - 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
- DO NOT LOSE ANY DATA, AND/OR YOUR VALUABLE CONFIGURATION (!)
Important
Make sure to backup all your Nodinite databases, and make sure the documentation for the restore operation is available to the appropriate stakeholders