- 12 minutes to read

About using Microsoft SQL Server with Nodinite

Unlock the full potential of Nodinite by choosing the right Microsoft SQL Server configuration. This guide is packed with actionable tips, technical best practices, and expert advice for both first-time users and seasoned technicians:

✅ Full compatibility matrix for all supported SQL Server versions and editions
✅ SQL Server 2025 Express now supports up to 50 GB per Log Database, making it a strong low-cost option
✅ Step-by-step prerequisites for seamless installation and upgrades
✅ Advanced performance tuning, backup, and security strategies
✅ High availability, clustering, and cloud deployment options
✅ Troubleshooting guides for common SQL Server and Nodinite issues
✅ Real-world tips: tempdb optimization, trace flags, collation, and more
✅ How to leverage SQL Profiler, Always On, and SQL Agent for enterprise scenarios

Tip
Quick Tips:

Use -T1118 and -T834 trace flags for tempdb and memory optimizations

Configure multiple tempdb files (1 per core, up to 8) for best throughput

Always use CI_AS collation for Nodinite databases to avoid query issues

Prefer Enterprise/Standard editions for Logging Service and heavy workloads

Use SQL Agent or Windows Task Scheduler for automated maintenance and backups

Monitor SQL Server with built-in DMVs and integrate with Nodinite for alerting

Review the official SQL Server documentation for advanced features

SQL Server Versions and Editions

Nodinite supports a broad range of Microsoft SQL Server versions and editions, including Enterprise, Standard, Developer and Express. You should choose a version matching your organization's support policy and a edition that fits your business needs.

SQL Server Express is supported.
SQL Server Managed Instance (Azure) is supported.
- Azure SQL Databases are currently NOT supported with Nodinite Core Services

SQL Server Version	Always On support	Cluster support	Stand alone	Nodinite Version
SQL Server Managed Instance (Azure)				>=6.1
2025	✅	✅	✅	->7
2022	✅	✅	✅	->6
2019	✅	✅	✅	->6
2017	✅	✅	✅	->6
2016	✅	✅	✅	->6
2014	❌	✅	✅	->6
2012	❌	✅	✅	->6
2008 R2	❌	✅⚠️	✅⚠️	->5.4 -> <6.1

For production Logging workloads, SQL Server 2025 Express is now a viable budget-friendly option due to its 50 GB per-database limit. Older SQL Server Express versions keep the 10 GB limit and are effectively not viable for production logging in most environments.

Feature	Enterprise	Standard	Express
Maximum Database Size	524 PB (theoretical limit)	524 PB (same engine)	50 GB (SQL Server 2025 Express), 10 GB (older Express versions)
CPUs / Cores	OS Max (Windows Server supports up to 64 sockets / 2048 logical processors)	4 sockets or 24 cores	1 socket or 4 cores
Max Memory (per instance)	OS Max	128 GB (buffer pool), + other memory	1.41 GB (buffer pool only)
SQL Profiler (UI)	✅	✅	❌

Notes & Clarifications

Database Size

524 PB (petabytes) is the technical limit due to internal architecture (32 TB per file × 16,000 files).
In practice, your storage hardware and design will limit you before hitting that.

CPUs/Cores

Express: Limited to the lesser of 1 socket or 4 cores.
Standard: Limited to the lesser of 4 sockets or 24 cores.

Max Memory

Express: 1.41 GB for the buffer pool (some overhead not included).
Standard: 128 GB only for buffer pool — other components like columnstore indexes, in-memory OLTP, etc., may have their own limits.
Enterprise: Uses as much as the OS allows.

SQL Profiler

Not available in SQL Server Express Edition, since it doesn’t support SQL Server Agent or other features required by some Profiler components.

SQL Server Always On

For high availability, Nodinite supports SQL Server Always On. Review the About SQL Server Always On Availability Groups guide for detailed configuration steps and recommendations.

SQL Server Express

SQL Server Express is supported but has important edition differences. SQL Server 2025 Express is now a compelling entry-level choice for many Nodinite environments because each Log Database can grow up to 50 GB. SQL Server Express 2022 and earlier keep the 10 GB limit and are effectively not suitable for production Logging.

If you use Nodinite only for documentation and Monitoring, both current and older Express versions may still be sufficient depending on retention volume.

Tip

Start with SQL Server 2025 Express to reduce licensing cost while keeping meaningful log retention headroom (50 GB per Log Database).

You can still upgrade later to Standard edition if you need SQL Agent, more CPU/RAM, or higher-scale features.

SQL Server Express remains a strong choice for development, testing, and POC environments.

SQL Server Version	Always On (AOAG)	Fail Cluster	Stand alone	Nodinite Version
2025	❌	❌	✅	-> 7
2022	❌	❌	✅	-> 6
2019	❌	❌	✅	-> 6
2017	❌	❌	✅	-> 6
2016	❌	❌	✅	-> 6
2014	❌	❌	✅	-> 6
2012	❌	❌	✅	-> 6
2008 R2	❌	❌	✅	->5.4

Warning

SQL Server Express 2022 and earlier use a 10 GB per-database limit, which is effectively too small for production logging.

All SQL Server Express editions still lack SQL Server Agent. Use Windows Scheduled Tasks for maintenance jobs, or move to Standard/Enterprise when SQL Agent is required.

Installation Path

If Nodinite is not installed on the same Windows Server as SQL Server, install SQL Server in any folder. For multi-server environments, review System Parameters and the SqlPackageExecutable user guide for configuration examples.

Collation

Nodinite requires a SQL Server instance with a collation of type CI_AS (Case Insensitive, Accents Sensitive). Choose a collation that matches your language, policy, and business requirements.

Learn more about SQL Server Collations in the official documentation.

Security

Security is critical for your SQL Server environment. Follow these best practices:

Account for SQL Server Instance

Run the SQL Server instance with an AD service account (not a local account) to ensure proper SPN registration in Active Directory. This is required for Kerberos authentication and secure delegation; See Trusted for delegation.

Tip
Use a gMSA account for enhanced security and simplified management.

Kerberos

Windows enforces Kerberos, which is essential for secure, multi-server environments. Ensure the following are properly configured:

SPNs
Trusted for delegation
Firewalls
Authentication and Authorization

Trusted for delegation

See the Trusted for delegation user guide for details.

SPN

When Nodinite uses a Linked Server, the SQL Server instance(s) must have its Service Principal Name (SPN) properly registered. See Account for SQL Server Instance for more information.

Linked Server

Review the Linked Servers section for configuration guidance.

DTC

See the Transactions MSDTC user guide.

Note
The type of DTC configuration depends on your SQL Server setup and organizational policies.

Performance optimizations

Maximize your Nodinite experience by following these performance tips:

Run Nodinite in dedicated SQL instances to guarantee resources and simplify troubleshooting
- Assign dedicated disk volumes for the Log Databases using the LogLocations system parameter
Keep the Logging Service
Repeat these optimizations on ALL SQL Server instances (including BizTalk Server databases)
Use -T1118 Trace flag on SQL Instances (<2016) to optimize TEMPDB
Disable PAC Verification if your policy allows, to reduce RPC calls and improve performance
Install Nodinite on machines with dedicated swap volumes (>2.5x physical RAM) and SQL disks with >300 MB/s R/W
Ensure a secure backup volume or network share with enough free space for Nodinite databases
Windows Servers hosting Core Services should have at least 16GB RAM; high-volume environments may need more
Use Simple recovery mode for Log Databases with BizTalk Server to boost performance

SQL Server Memory Management

SQL Server is designed to aggressively cache data in the buffer pool (MEMORYCLERK_SQLBUFFERPOOL) and will consume all memory it has been allocated over time. This is expected behavior, not a Nodinite issue. By default, SQL Server has no upper memory limit and will grow until the OS is starved — this must be explicitly capped.

Is high memory usage expected?

Yes. SQL Server caches database pages in the buffer pool to avoid repeated disk reads, which is normal and desirable. A dedicated SQL Server instance will eventually consume most available RAM. This is the expected behavior for any SQL Server workload. Nodinite's Log Databases in particular grow over time and are read frequently across the Logging Service, Web API, and client queries — all of which contribute to buffer pool growth.

The key health indicator is Page Life Expectancy (PLE): the number of seconds a data page stays in the buffer pool before being evicted. A high PLE (>300 for every 4 GB of buffer pool) indicates the cache is healthy and not under memory pressure. A low or falling PLE indicates SQL Server needs more RAM or the memory cap is set too low.

Tip
Check the Memory Consumption report in SQL Server Management Studio (SSMS): Reports → Standard Reports → Memory Consumption. A PLE in the thousands means memory is working well. The official Microsoft guidance recommends a baseline PLE threshold of 300 × (buffer pool GB / 4).

Configure `max server memory`

You must configure max server memory to prevent SQL Server from starving the operating system and other services. Even on a dedicated SQL instance, the OS, Nodinite services, and Windows processes need memory headroom.

Recommended formula for a dedicated SQL Server host:

max server memory (MB) = Total RAM (MB) − OS headroom (2048–4096 MB) − other services

Examples for a server with 32 GB RAM running only SQL Server:

Total RAM	Recommended `max server memory`
16 GB	12 288 MB
32 GB	28 672 MB
64 GB	59 392 MB
128 GB	122 880 MB

Apply the setting in SSMS (Server Properties → Memory → Maximum server memory) or via T-SQL:

-- Cap SQL Server memory (example: 28 GB on a 32 GB server)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 28672;
RECONFIGURE;

Always test after changing memory settings and monitor PLE for at least 24 hours.

Note
On a dedicated SQL Server instance (as recommended for Nodinite), you only need to reserve ~2–4 GB for the OS. Nodinite Core Services run on a separate application server, so their RAM is not relevant here.

Warning
Setting max server memory too low will cause SQL Server to page excessively, dramatically reducing performance. Never set it below 4 GB for a production Nodinite environment.

See the official Microsoft documentation for further guidance:

TempDB optimization

For each core (up to 8), create 1 tempdb file of at least 128 MB (no auto growth)

For example, with 14 cores, use 8 tempdb files. Placing tempdb files on different volumes can further improve performance.

Backups

Nodinite databases are critical for your monitoring and logging operations. Follow these best practices to ensure data integrity and availability:

DO NOT LOSE ANY DATA OR CONFIGURATION!
Important
Back up all your Nodinite databases, and make sure the documentation for the restore operation is available to the appropriate stakeholders

Trust Server Certificate

Windows Server and SQL Server should be properly configured for TLS. Review the official documentation for details.

Nodinite components use Microsoft.Data.SqlClient (or older System.Data.SqlClient) for connection strings with modern TLS defaults (Encrypt=True, TrustServerCertificate=False). When using self-signed certificates or internal CAs, you may need to add TrustServerCertificate=True.

Tip
Need comprehensive connection string guidance? See SQL Server Connection Strings for:

Certificate validation decision guide (5 scenarios)

Encryption and authentication settings

Always On Availability Groups (AOAG) configuration

Troubleshooting common errors

Security best practices

With Nodinite (default settings) you are using the native SQL Client (typically using the newer Microsoft.Data.SqlClient or the older System.Data.SqlClient library). You can opt to use OLE DB Driver for SQL Server (MSOLEDBSQL). Both support the TrustServerCertificate parameter.

OLE DB Driver for SQL Server (MSOLEDBSQL)

You can download the latest OLE DB Driver for SQL Server from here: Download Microsoft OLE DB Driver for SQL Server.

Below is an example using version 19 of the OLE DB Driver for SQL Server. Adjust the version number as needed.

Provider=MSOLEDBSQL.19;Data Source=NODINITE01;Initial Catalog=Nodinite_MonitoringAgent_BizTalk;Integrated Security=True;TrustServerCertificate=True

There may be new registry keys to enforce the policy in use to connect to SQL Server. You can use the following PowerShell scripts to read and write the required registry keys.

Flag	Meaning	Values
Flag1	Protocol Encryption	0 = Optional 1 = Mandatory 2 = Strict
Flag2	Trust Server Certificate	0 = Validate Cert 1 = Trust without validation

Are there equivalent registry keys for the native SqlClient?
No, not directly. The .NET SQL client does not rely on registry-based configuration like the OLE DB driver does. Instead, it is entirely driven by the connection string and programmatic configuration.

Read Settings from the Registry

This script reads the current settings for the OLE DB Driver from the registry.

# Read-OLEDB-Encryption.ps1

$registryPaths = @(
    "HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client",
    "HKLM:\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client"
)

Write-Host "`n--- Reading OLE DB Driver Encryption and Certificate Validation Settings ---`n"

foreach ($basePath in $registryPaths) {
    $sniKeys = Get-ChildItem -Path $basePath -Name | Where-Object { $_ -like "SNI*.0" }

    foreach ($sniKey in $sniKeys) {
        $fullKeyPath = Join-Path $basePath $sniKey
        $flagPath = Join-Path $fullKeyPath "GeneralFlags"

        Write-Host "`n[$flagPath]"

        foreach ($flag in 1,2) {
            $valueName = "Flag$flag"

            if (Test-Path $flagPath) {
                try {
                    $value = Get-ItemPropertyValue -Path $flagPath -Name $valueName -ErrorAction Stop
                    Write-Host "$valueName = $value"
                } catch {
                    Write-Warning "$valueName is missing."
                }
            } else {
                Write-Warning "$flagPath does not exist."
                break
            }
        }
    }
}

Script to read the current settings from the registry and display them in the console.

Write Settings to the Registry

This script sets the desired values for the OLE DB Driver in the registry. You can specify the values for EncryptionMode and TrustServerCertificate as parameters.

Save the script below as Set-OLEDB-Encryption.ps1. Then, run it in an elevated PowerShell session to set the desired values.

.\Set-OLEDB-Encryption.ps1 -EncryptionMode 0 -TrustServerCertificate 1

Example command to set EncryptionMode to 0 (Optional) and TrustServerCertificate to 1 (Trust without validation).

# Set-OLEDB-Encryption.ps1

param (
    [ValidateSet(0,1,2)]
    [int]$EncryptionMode,

    [ValidateSet(0,1)]
    [int]$TrustServerCertificate
)

$registryPaths = @(
    "HKLM:\SOFTWARE\Microsoft\MSSQLServer\Client",
    "HKLM:\SOFTWARE\WOW6432Node\Microsoft\MSSQLServer\Client"
)

Write-Host "`n--- Setting OLE DB Driver Registry Values ---`n"

foreach ($basePath in $registryPaths) {
    $sniKeys = Get-ChildItem -Path $basePath -Name | Where-Object { $_ -like "SNI*.0" }

    foreach ($sniKey in $sniKeys) {
        $fullKeyPath = Join-Path $basePath $sniKey
        $flagPath = Join-Path $fullKeyPath "GeneralFlags"

        if (-not (Test-Path $flagPath)) {
            Write-Host "Creating missing path: $flagPath"
            New-Item -Path $flagPath -Force | Out-Null
        }

        if ($PSBoundParameters.ContainsKey("EncryptionMode")) {
            try {
                Set-ItemProperty -Path $flagPath -Name "Flag1" -Value $EncryptionMode -Force
                Write-Host "Set ${flagPath}\Flag1 to $EncryptionMode"
            } catch {
                Write-Error "Failed to set ${flagPath}\Flag1: $_"
            }
        }

        if ($PSBoundParameters.ContainsKey("TrustServerCertificate")) {
            try {
                Set-ItemProperty -Path $flagPath -Name "Flag2" -Value $TrustServerCertificate -Force
                Write-Host "Set ${flagPath}\Flag2 to $TrustServerCertificate"
            } catch {
                Write-Error "Failed to set ${flagPath}\Flag2: $_"
            }
        }
    }
}

Script to set the desired values in the registry for the OLE DB Driver.

Explore Nodinite for 30 days – completely free

See how Nodinite resolves your technical issues, in a

LIVE Demo!