Best practices to configure SQL Server for Business Central on-prem installation.

20-07-2025

Configuring SQL Server for a Business Central on-premises installation requires careful planning to ensure optimal performance, reliability, and scalability. Here are the best practices you should follow:


⚙️ SQL Server Configuration Best Practices

1. Edition & Version

  • Use SQL Server Enterprise or Standard Edition (latest supported version).
  • Ensure compatibility with the Business Central version you're installing.

2. Instance Configuration

  • Use a dedicated SQL Server instance for Business Central.
  • Disable unnecessary services (e.g., SQL Server Agent if not used).

3. Memory Settings

  • Set Max Server Memory to leave room for OS and Business Central services.
    • Example: If the server has 32 GB RAM, allocate ~24 GB to SQL Server.
  • Avoid dynamic memory allocation in production environments.

4. Processor Settings

  • Enable Lock Pages in Memory for SQL Server service account.
  • Use NUMA-aware configuration if applicable.
  • Ensure parallelism settings are optimized:
    • Max Degree of Parallelism = 4 (or tuned based on workload).
    • Cost Threshold for Parallelism = 50 or higher.

5. Database Configuration

  • Use Simple Recovery Model unless point-in-time recovery is needed.
  • Enable Instant File Initialization for faster database creation.
  • Separate data, log, and tempdb files on different disks.

6. TempDB Optimization

  • Use multiple tempdb files (1 per logical CPU core up to 8).
  • Pre-size tempdb files to avoid auto-growth.
  • Place tempdb on fast storage (SSD/NVMe).

7. Maintenance Plans

  • Regular backups (full, differential, transaction log).
  • Index maintenance (rebuild/reorganize).
  • Update statistics regularly.

8. Security

  • Use Windows Authentication where possible.
  • Assign least privilege to SQL Server service accounts.
  • Encrypt connections using SSL/TLS.

9. Monitoring & Alerts

  • Set up alerts for disk space, failed jobs, and performance thresholds.
  • Use tools like SQL Server Management Studio (SSMS).

10. Business Central-Specific Settings

  • Ensure collation matches Business Central requirements (usually SQL_Latin1_General_CP1_CI_AS).
  • Use dbo schema for all Business Central tables.
  • Avoid triggers or custom constraints that interfere with Business Central logic.