Skip to main content

MSSQL Staging Environment

Introduction​

Efficient performance of the Microsoft SQL Server staging environment is crucial for the optimal functioning of the Enov8 Test Data Management (TDM) Tool. This document outlines the standard configurations required for as Microsoft SQL Server staging server and providess a comprehensive set of troubleshooting steps for Database Administrators (DBAs) to ensure that the database aligns with these standards and operates at peak efficiency.

Disclaimer

This documentation is written for SQL Server versions 2017, 2019, and 2022, specifically for use in staging environments. The configurations and settings provided are recommendations based on typical setups and may not be suitable for all environments. It is essential to evaluate these recommendations in the context of your specific staging environment and requirements. Consult with your organization's database administrators and Microsoft support for guidance tailored to your specific needs.

1. Standard Database Server Configuration​

1.1 Hardware Requirements​

The hardware requirements should scale based on the size of thes database and the volume of data being masked. Below is a comprehensive guideline for CPU, memory, disk, and network:

CPU​

  • Cores: Start with 8 cores, add 4 cores per additional 1 TB of data.
  • Example:
    • For a 1 TB database: 12 cores.
    • For a 2 TB database: 16 cores.

Memory (RAM)​

  • Base Requirement: Start with 32 GB, add 32 GB per additional 1 TB of data.
  • Example:
    • For a 1 TB database: 64 GB of RAM.
    • For a 2 TB database: 96 GB of RAM.

Disk​

  • Type: Use high-speed SSD for optimal performance.
  • Capacity: Ensure sufficient space for data, logs, and backups.
  • Performance: SSDs improve read/write speeds, essential for handling large datasets efficiently.

Network​

  • Bandwidth: High-bandwidth network interfaces (10 Gbps or higher) are recommended.
  • Purpose: Facilitates faster data transfer between the database server and other components of your infrastructure.

1.2 Operating System and Database Configuration​

Supported Operating Systems​

  • Windows Server:
    • Versions: Windows Server 2016, 2019, or 2022 (recommended).

SQL Server Versions​

  • Microsoft SQL Server:
    • Versions: SQL Server 2017, 2019, or 2022 (recommended).

Optimizing for Bulk Data Masking​

To improve performance during data masking operations with the Enov8 TDM Tool, consider the following configurations:

1. Disable Transaction Logging (Simple Recovery Model)​
  • Purpose: Reduces I/O overhead by minimizing transaction log writes during bulk operations.
  • Implementation: 
    ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE;
  • Note: This action prevents point-in-time recovery during the masking process. Ensure this aligns with your organization's data protection policies.
2. Enable Bulk Logged Recovery Model (Alternative Option)​
  • Purpose: Provides a balance between performance and recoverability by minimally logging bulk operations.
  • Implementation: 
    ALTER DATABASE [YourDatabaseName] SET RECOVERY BULK_LOGGED;
  • Note: Bulk Logged Recovery Model allows for point-in-time recovery except for the time when bulk operations are running.
3. Disable Non-essential Features​
  • Disable Triggers and Constraints (if applicable):
    • Purpose: Improves performance by preventing additional processing during data modifications.
    • Implementation: 
      EXEC sp_msforeach_table 'DISABLE TRIGGER ALL ON ?';
      EXEC sp_msforeach_table 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
    • Re-enable after masking: 
      EXEC sp_msforeach_table 'ENABLE TRIGGER ALL ON ?';
      EXEC sp_msforeach_table 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';
4. Adjust TempDB Configuration​
  • Purpose: Optimizing TempDB improves performance for operations that require sorting or temporary storage.
  • Implementation:
    • Increase the Number of TempDB Data Files: Create one TempDB data file per CPU core up to 8 files.
    • Set Equal Size for TempDB Data Files: Ensure all TempDB data files are of equal size to prevent uneven growth.
    • Example Script: 
      USE [master];
      GO
      ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'tempdev', SIZE = 1024MB);

1.3 SQL Server Configuration​

Memory Configuration​

  • Set Maximum Server Memory:
    • Purpose: Ensures that SQL Server does not consume all available memory, leaving some for the OS and other applications.
    • Implementation:
      • Calculate Max Server Memory: 
        EXEC sp_configure 'max server memory', 58000;
        RECONFIGURE;

CPU Configuration​

  • Enable Hyper-Threading (if supported): Ensure that hyper-threading is enabled to improve performance.

2. Troubleshooting Steps for DBAs​

2.1 Verify Hardware and OS Configuration​

Check CPU and Memory Usage​

  • Purpose: Ensure that the server hardware meets the required specifications and is not experiencing resource bottlenecks.
  • Commands: 
    top
  • Expected Results: CPU usage should not be consistently above 80%. There should be enough free memory (e.g., at least 10% of total RAM).

Check Disk I/O Performance​

  • Purpose: Identify any disk bottlenecks impacting database performance.
  • Commands: 
    iostat -x 1 5
  • Expected Results: %util should ideally be below 80%. Average wait time should be low.

2.2 Validate SQL Server Configuration Settings​

Check Memory Configuration​

  • Commands: 
    EXEC sp_configure 'max server memory';
  • Expected Results: Ensure the memory settings align with the calculated requirements.

Check TempDB Configuration​

  • Commands: 
    SELECT name, size / 128 AS [Size in MB], max_size, growth FROM sys.database_files;
  • Expected Results: Confirm TempDB files are configured correctly.

2.3 Assess Database Performance​

Generate Execution Plans​

  • Purpose: Identify performance bottlenecks in query execution.
  • Commands: 
    SET SHOWPLAN_ALL ON;
  • Expected Results: Review execution plans for inefficiencies.

Monitor Session and Query Performance​

  • Commands: 
    SELECT session_id, status, blocking_session_id FROM sys.dm_exec_sessions WHERE status = 'running';
  • Expected Results: Identify and resolve blocking sessions.

2.4 Evaluate Storage and Space Usage​

Check Database File Sizes and Growth Settings​

  • Commands: 
    SELECT name, size / 128 AS [Size in MB], max_size, growth FROM sys.database_files;
  • Expected Results: Ensure data and log files have appropriate size and autogrowth settings.

Monitor TempDB Usage​

  • Commands: 
    SELECT SUM(user_object_reserved_page_count) * 8 AS [User Object Space (KB)] FROM sys.dm_db_file_space_usage;
  • Expected Results: Confirm TempDB usage is within expected limits.

2.5 Review Statistics and Indexes​

Ensure Statistics are Up-to-Date​

  • Commands: 
    EXEC sp_updatestats;
  • Expected Results: Statistics should be current to optimize query performance.

Analyze Index Fragmentation​

  • Commands: 
    SELECT indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') AS indexstats;
  • Expected Results: Reorganize or rebuild indexes with fragmentation over 10%.

2.6 Network Diagnostics​

Check SQL Server Network Configuration​

  • Verify TCP/IP is Enabled: Use SQL Server Configuration Manager ➔ SQL Server Network Configuration ➔ Protocols for [InstanceName].

Test Connectivity​

  • Commands: 
    sqlcmd -S ServerName\InstanceName -E
  • Expected Results: Successful connection to the server.

2.7 Verify Security and Patch Levels​

Check SQL Server Version and Patch Level​

  • Commands: 
    SELECT @@VERSION;
  • Expected Results: Verify that the SQL Server version includes the latest cumulative updates and service packs.

Review Security Settings​

  • Ensure Only Necessary Permissions are Granted: Audit user roles and permissions.

2.8 Application Interaction​

Confirm Connection Management​

  • Purpose: Ensure applications use connection pooling to optimize resource usage.
  • Verification: Review application configuration settings for connection pooling parameters.

Monitor Application Sessions​

  • Commands: 
    SELECT login_name, COUNT(session_id) AS SessionCount FROM sys.dm_exec_sessions WHERE is_user_process = 1 GROUP BY login_name;
  • Expected Results: A reasonable number of sessions corresponding to the application's expected behavior.

2.9 System Resource Monitoring​

Monitor System Performance​

  • Use Windows Performance Monitor:

    • Key Counters:
      • Processor\% Processor Time
      • Memory\Available MBytes
      • Disk\Avg. Disk sec/Read
      • Disk\Avg. Disk sec/Write

  • Expected Results: CPU, memory, and disk usage should be within normal operating thresholds.

Check for Memory Pressure​

  • Commands: 
    SELECT object_name, counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name IN ('Target Server Memory (KB)', 'Total Server Memory (KB)');
  • Expected Results: Compare Total Server Memory and Target Server Memory to assess if SQL Server needs more memory.