• Server configuration prep
    • Separate data drive (with sufficient space)
    • (High Perf): Separate LDF drive
    • Separate backup drive (with sufficient space)
    • (High Perf): separate TempDB drive.
  • Collation checks if migration from previous environment

    • Check SQL collation from prior environment

      SELECT SERVERPROPERTY('Collation') AS ServerCollation;
go
SELECT name AS DatabaseName, collation_name AS DatabaseCollation FROM sys.databases ORDER BY name;

      it is usually either:
      a) SQL_Latin1_General_CP1_CI_AS (sql collation)

      b) Latin1_General_CI_AS (windows collation)

  • Install options
    • Location
    • Default backup location
    • Default data location
    • SQL Agent - Automatic
    • SQL Browser- IF NAMED: On, IF DEFAULT: Off.
    • Mixed mode. (Windows & SQL users allowed)
      • Generate secure pw, and store in Password Manager.
  • Post-installation configuration (SQL Configuration tool)
    • Network protocols
    • Firewall port opening (1433,and 1434 if named instance)
      • Check if IP and port is configured as static
    • SQL Agent
    • SQL Browser (if named instance)
  • Users
    • Create SECURE users for specific users that require access with least privilege
    • Create Windows Authentication SQL groups access: SQL - Create Evolution User in SQL
  • Configuration options:

    • Default backup compression on;

      EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;
--CHECK IF IT's CONFIGURED WELL:
EXEC sp_configure 'backup compression default';

    • Check default backup location

      --SET VALUE:
EXEC xp_instance_regwrite
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer',
    @value_name = 'BackupDirectory',
    @type = 'REG_SZ',
    @value = 'E:\SQLBackups'; -- Replace with your desired path




--CHECK VALUE:
EXEC xp_instance_regread
    @rootkey = 'HKEY_LOCAL_MACHINE',
    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer',
    @value_name = 'BackupDirectory';
    •  Review hardening template: SQL Database - Hardening template
  • Additional security possibilities:
    • Logon trigger: SQL - Logon trigger with blocks