- 9 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
✅ 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

Technical Goodies for Technicians:
• 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 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

If you enable Logging and the retention period yields large databases (>10GB), the SQL Server Express edition is not recommended.

Feature	Enterprise	Standard	Express
Maximum Database Size	524 PB (theoretical limit)	524 PB (same engine)	10 GB
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 Express

SQL Express is supported but has limitations. It is generally not recommended for environments with Logging. If you use Nodinite only for documentation and Monitoring, SQL Server Express may suffice.

Tip

You can upgrade SQL Express 2019 and later to Standard edition, allowing you to start small and scale as your business grows.

You can use SQL Express for development, testing, or small-scale deployments like a POC.

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

The 10 GB per database limit for SQL Express makes it a poor choice for logging scenarios.

SQL Express does not include SQL Agent. Use scheduled tasks in Windows to execute maintenance tasks and backups.

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 [].

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

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!