Microsoft SQL Server - Security and Configuration – Askia Help Centre - Automating insight

1. SQL Server Configuration Standards

SQL Server versions and cumulative updates must be kept current in accordance with the organization’s patching and maintenance policies.
SQL Server instances must follow a standardized and documented configuration, including instance name, listening ports, service accounts, and data/log file locations.
Database and server collation must be selected intentionally at installation time and documented to avoid application inconsistencies and cross-database comparison issues.

Windows Authentication (Active Directory) must be used for interactive access whenever possible, with SQL authentication limited to approved service or application requirements only.
Where SQL authentication is required, strong passwords must be enforced, unused SQL logins disabled, and shared credentials avoided.
SQL Server service accounts must be dedicated, least-privilege identities (preferably gMSA where feasible) and must not be members of Domain Admin or Local Administrator groups.
Server- and database-level permissions must follow least-privilege principles, with sysadmin membership restricted and reviewed regularly.

Database AUTO_CLOSE must be set to OFF to prevent performance degradation and concurrency issues.
Database AUTO_SHRINK must be set to OFF to avoid recurring fragmentation and performance impact.
AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS should remain ON for most workloads to ensure accurate query optimization.
AUTO_UPDATE_STATISTICS_ASYNC may be enabled only after workload evaluation confirms it benefits performance without destabilizing execution plans.

Data and log files should be placed on appropriate, dedicated volumes where possible, with layouts documented for capacity planning and recovery.
Database files should be pre-sized based on expected workload and growth trends to reduce frequent autogrowth events during peak usage.
Database file autogrowth must be configured to 10% increments, which is acceptable for most Askia client environments, and growth behavior must be monitored regularly; consult Askia Support for further guidance if database size, workload, or performance characteristics require fixed-size growth or advanced tuning.
Disk free space, file growth events, and abnormal growth patterns must be monitored, with alerting thresholds defined.

TempDB must be appropriately sized and monitored, with autogrowth configured and reviewed regularly.
TempDB data file count should be configured based on observed contention and workload characteristics rather than fixed assumptions.
SQL Server memory configuration (minimum and maximum server memory) must be explicitly set to prevent OS resource starvation.

Backup strategies must include Full, Differential, and Transaction Log backups as appropriate, with retention aligned to defined recovery objectives.
Backup restoration testing must be performed regularly to validate data recoverability.
Database integrity checks (e.g., DBCC CHECKDB) must be scheduled and monitored, with failures investigated promptly.
Index and statistics maintenance must be implemented based on workload patterns and monitored for effectiveness.
Disk space maintenance activities must be coordinated with Askia Support to ensure application safety and data integrity.
Where disk usage growth is driven by unused or obsolete Askia-related tables, Askia Support may recommend using an internal tool to safely remove unused objects.
Following successful cleanup and validation, a controlled database shrink and file shrink may be performed to reclaim unused disk space, recognizing that shrink operations are exception-based maintenance actions and not part of routine operations.
Any cleanup or shrink activity must be documented, scheduled during maintenance windows, and monitored for post-operation performance impact.

Monitoring must be enabled for failed logins, privileged access, configuration changes, and job failures, with logs forwarded to centralized monitoring where feasible.
High availability and disaster recovery configurations (e.g., clustering, Availability Groups, log shipping) must be documented, including RPO/RTO objectives and recovery procedures.

Database compatibility levels must be documented and reviewed during upgrades to avoid unexpected behavior or query plan regressions.
Cross-database queries and integrations must be reviewed for collation conflicts and addressed using explicit collation handling when necessary.
Any deviations required by application behavior (including Askia-specific components) must be documented as approved exceptions with owner and justification.