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, and Express. Choose the edition that matches your business needs and expected data volume.
- 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 |
Use the Developer edition for non-production environments.
If you require Logging, you almost always need the Enterprise or Standard edition for sufficient capacity and features.
| 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