Skip to main content

Microsoft SQL Server

This guide covers SQL Server setup, configuration, and management for Microsoft Dynamics NAV and related systems.

Overview​

SQL Server is the database engine used by Microsoft Dynamics NAV and LS Central. Proper SQL configuration is essential for system performance, reliability, and data integrity.

Key Areas:

  • SQL Server installation and setup
  • Database configuration
  • Performance optimization
  • Backup and maintenance
  • Security configuration

Prerequisites​

Before setting up SQL Server, ensure you have:

  • ✅ Windows Server with appropriate permissions
  • ✅ SQL Server installation media
  • ✅ Required licenses
  • ✅ Administrative access
  • ✅ Network connectivity
  • ✅ Understanding of system requirements

Installation​

Step 1: Prepare Server​

  1. System Requirements

    • Windows Server 2016 or later
    • Minimum 8GB RAM (16GB+ recommended)
    • Sufficient disk space for databases
    • Network connectivity

  2. Pre-Installation Checks

    • Verify system requirements
    • Check available disk space
    • Review security policies
    • Plan installation location

Step 2: Install SQL Server​

  1. Run Installation

    • Launch SQL Server installation wizard
    • Select installation type
    • Choose features to install

  2. Configuration

    • Set instance name
    • Configure service accounts
    • Set up authentication mode
    • Configure ports

  3. Complete Installation

    • Review configuration
    • Install SQL Server
    • Verify installation
Installation Best Practices
  • Use dedicated service accounts for SQL Server services
  • Enable Windows Authentication mode
  • Configure appropriate memory limits
  • Set up proper file locations for data and logs

Database Configuration​

Creating Databases​

  1. Create NAV Database

    CREATE DATABASE [NAV_Database]
    ON (NAME = 'NAV_Data', FILENAME = 'C:\SQLData\NAV_Data.mdf')
    LOG ON (NAME = 'NAV_Log', FILENAME = 'C:\SQLLog\NAV_Log.ldf');

  2. Configure Database Settings

    • Set recovery model
    • Configure file growth
    • Set initial sizes
    • Configure auto-shrink (if needed)

Database Settings​

SettingRecommended ValueDescription
Recovery ModelFullEnables point-in-time recovery
Auto GrowthEnabledAllows database to grow automatically
Auto ShrinkDisabledPrevents performance issues
Compatibility LevelLatestUse latest SQL Server features

Performance Optimization​

Index Management​

  1. Create Indexes

    CREATE INDEX idx_Customer_Email 
    ON Customers(Email);

  2. Maintain Indexes

    • Regular index maintenance
    • Rebuild fragmented indexes
    • Update statistics
    • Monitor index usage

Query Optimization​

  • Use appropriate indexes
  • Avoid SELECT * queries
  • Use parameterized queries
  • Monitor slow queries
  • Optimize joins and WHERE clauses
Performance Monitoring

Regularly monitor query performance, index usage, and database statistics to maintain optimal performance.

Backup & Recovery​

Backup Strategy​

  1. Full Backups

    • Schedule daily full backups
    • Store backups securely
    • Test restore procedures
    • Document backup locations

  2. Transaction Log Backups

    • Schedule frequent log backups
    • Maintain log chain
    • Monitor log file size
    • Configure retention policy

Backup Configuration​

-- Full backup
BACKUP DATABASE [NAV_Database]
TO DISK = 'C:\Backups\NAV_Full.bak'
WITH COMPRESSION, INIT;

-- Transaction log backup
BACKUP LOG [NAV_Database]
TO DISK = 'C:\Backups\NAV_Log.trn'
WITH COMPRESSION;
Backup Testing

Regularly test backup restoration procedures to ensure backups are valid and can be restored when needed.

Security Configuration​

Authentication​

  1. Windows Authentication

    • Use Windows authentication when possible
    • Configure service accounts
    • Set up domain integration

  2. SQL Authentication

    • Use strong passwords
    • Enable password policies
    • Limit SQL logins
    • Regular password rotation

User Permissions​

  1. Principle of Least Privilege

    • Grant minimum required permissions
    • Use role-based access
    • Regular permission audits
    • Document access changes

  2. Database Roles

    • Assign appropriate database roles
    • Use custom roles for specific needs
    • Review role memberships regularly

Maintenance​

Regular Maintenance Tasks​

  1. Index Maintenance

    • Rebuild fragmented indexes
    • Update statistics
    • Monitor index usage
    • Remove unused indexes

  2. Database Maintenance

    • Check database integrity
    • Shrink log files (if needed)
    • Update statistics
    • Clean up old data

Maintenance Plans​

Create automated maintenance plans for:

  • Backups - Automated backup schedules
  • Index Maintenance - Regular index optimization
  • Statistics Updates - Keep statistics current
  • Integrity Checks - Verify database integrity

Monitoring​

Key Metrics​

Monitor the following SQL Server metrics:

  • CPU Usage - Server processor utilization
  • Memory Usage - SQL Server memory consumption
  • Disk I/O - Database file I/O performance
  • Query Performance - Slow query identification
  • Connection Count - Active database connections

Tools​

  • SQL Server Management Studio (SSMS) - Primary management tool
  • Activity Monitor - Real-time performance monitoring
  • SQL Profiler - Query tracing and analysis
  • Performance Dashboard - Performance metrics

Troubleshooting​

Common Issues​

IssueSolution
Connection ErrorsCheck SQL Server service, verify network, check firewall
Performance IssuesReview indexes, check statistics, optimize queries
Disk SpaceMonitor database growth, implement data archiving
Backup FailuresVerify disk space, check permissions, review backup location

Diagnostic Queries​

-- Check database size
EXEC sp_spaceused;

-- Check active connections
SELECT * FROM sys.dm_exec_sessions;

-- Check index fragmentation
SELECT * FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'DETAILED');

Best Practices​

Configuration​

  • ✅ Use appropriate recovery model
  • ✅ Configure proper file growth settings
  • ✅ Set up automated maintenance
  • ✅ Monitor database size and growth
  • ✅ Regular performance tuning

Security​

  • ✅ Use Windows authentication when possible
  • ✅ Implement strong password policies
  • ✅ Regular security audits
  • ✅ Limit administrative access
  • ✅ Encrypt sensitive data

Backup & Recovery​

  • ✅ Regular automated backups
  • ✅ Test restore procedures
  • ✅ Store backups off-site
  • ✅ Document recovery procedures
  • ✅ Monitor backup success

Questions?​

For questions about SQL Server setup and configuration:

  • Contact your Technical Team Lead
  • Reach out to the Head of Software Department
  • Check SQL Server documentation
  • Review system administration guides

Need help? Don't hesitate to reach out! 🚀

Last updated on by shellatomic