Automated SQL Server configuration to standardize security, performance, reliability, and operational readiness across standalone and multi-node environments, including Always On Availability Group deployments.

This PowerShell framework provides a modular approach to applying SQL Server best practices across one or multiple servers. It automates post-installation configuration, Windows Server optimization, SQL Server security hardening, service account management, network connectivity, storage configuration, database integrity, and operational monitoring.

The script validates prerequisites, detects the target SQL Server topology, and executes a configurable sequence of functions against the defined server nodes. It supports standalone and multi-node deployments while incorporating high-availability safeguards, including detection of Failover Cluster Instances (FCIs), for which execution is stopped.

Each configuration function can be individually enabled or disabled, allowing DBAs and system administrators to customize the deployment baseline according to their infrastructure and organizational requirements.

The framework helps reduce manual configuration effort, avoid inconsistent server builds, enforce a standardized SQL Server configuration, and improve operational reliability through repeatable automation.

The automation covers

• Windows Server prerequisite validation, power plan optimization, and Receive Side Scaling (RSS).
• Standalone and multi-node environment detection, including SQL Server topology auditing and FCI safeguards.
• SQL Server service account configuration using virtual accounts or managed service accounts (MSA/gMSA).
• Windows service privileges, including Lock Pages in Memory (LPIM) and Instant File Initialization (IFI).
• Static SQL Server TCP ports, client aliases, Windows Firewall rules, and instance discovery restrictions.
• SQL Server security hardening, including disabling the built-in sa login, revoking guest access, disabling SQL Browser, and enforcing login password policies.
• Least-privilege SQL Server service identity configuration and topology-aware NT AUTHORITY\SYSTEM permissions.
• SQL Server authentication mode, advanced instance settings, backup compression, and backup checksum configuration.
• SQL Server memory allocation, NUMA-aware MAXDOP, and cost threshold for parallelism.
• Default database directories, NTFS folder permissions, file growth, and TempDB configuration.
• Database integrity settings, including PAGE_VERIFY CHECKSUM, AUTO_CLOSE OFF, and AUTO_SHRINK OFF.
• Query Store configuration for eligible user databases.
• SQL Server and SQL Server Agent service startup, dependencies, and automatic recovery after failures.
• Persistent trace flags, login auditing, SQL Server error log retention, and default trace configuration.
• Critical SQL Server Agent alerts for errors 823, 824, and 825 and severity levels 19–25.

Key Capabilities

• Environment Detection and Prerequisite Validation: Validates Windows Server and SQL Server compatibility, PowerShell remoting, administrative permissions, SQL Server services, and required PowerShell commands. Identifies standalone and multi-node target sets, audits SQL Server topology, and stops execution when an FCI is detected.
• Security Hardening and Least-Privilege Configuration: Applies SQL Server security settings, disables unnecessary services and features, enforces SQL login password policies, and manages SQL Server service SID permissions. Avoids blanket local administrator permissions for SQL Server service accounts and drive-level Full Control assignments.
• Service Account Standardization: Configures SQL Server Database Engine and SQL Server Agent service identities using virtual accounts or MSA/gMSA accounts. When no managed service account is specified, the framework uses the corresponding default SQL Server virtual account and verifies the configured service identity.
• Performance and Resource Optimization: Configures SQL Server memory allocation, MAXDOP, cost threshold for parallelism, and Windows Server performance settings. Includes safeguards for servers hosting multiple SQL Server instances and calculates MAXDOP using detected CPU and NUMA topology.
• Storage and Database Configuration: Standardizes SQL Server default directories, NTFS folder permissions, system database file growth, and TempDB configuration. Applies database integrity settings and enables Query Store for eligible user databases while excluding read-only databases and Availability Group secondary replicas from database-level changes.
• Network and Connectivity: Configures static SQL Server TCP ports, creates 32-bit and 64-bit SQL client aliases, and manages Windows Firewall rules. Disables SQL Server Browser and restricts instance discovery, with network exposure controlled through configurable policy variables.
• Operational Resilience and Monitoring: Configures Windows service startup and recovery actions, SQL Server error log retention, login auditing, persistent trace flags, and critical SQL Server Agent alerts. Supports optional DBA operator email notifications when an email address is configured.
• Modular Execution and Configuration Control: Provides a centralized execution menu where individual functions can be enabled or disabled. Configuration variables allow administrators to adjust service accounts, network ports, authentication settings, memory allocation, storage paths, TempDB sizing, and monitoring policies.

Functions

The framework includes the following PowerShell functions, organized by configuration area.

Environment Detection and Validation

• Get-NormalizedServerList: Validates, normalizes, and removes duplicate server names from the target list.
• Get-EnvironmentType: Identifies standalone and multi-node target configurations.
• Get-TargetServerList: Builds the server list used for remote execution.
• Get-SqlTopologyAudit: Detects SQL Server clustering, HADR configuration, version, and edition.
• Test-BaselinePrerequisites: Validates administrative permissions, PowerShell remoting, SQL Server services, instance registration, and SQL PowerShell availability.
• Assert-WindowsServerBaseline: Validates Windows Server 2016 or later.
• Assert-SqlServerBaseline: Validates SQL Server 2019 or later.
• Show-ExecutionContext: Displays the effective server list, instance name, TCP port, and SQL service accounts before configuration.

Windows Server and SQL Services

• Configure-WindowsPrerequisites: Provides Windows Time service registration and synchronization functionality.
• Configure-WindowsPowerPlan: Configures the Windows High Performance power plan.
• Configure-ReceiveSideScaling: Enables and verifies Receive Side Scaling on supported network adapters.
• Configure-WindowsPrivileges: Configures SQL Server service privileges, including LPIM and IFI for the Database Engine.
• Configure-ServiceLogonAs: Configures and validates SQL Server Database Engine and Agent service accounts using virtual accounts or MSA/gMSA.
• Configure-ServiceDependencies: Configures SQL Server service dependencies on IP Helper, Server Service, and Network List Service.
• Configure-ServiceStartupType: Sets SQL Server Database Engine and SQL Server Agent services to Automatic startup.
• Configure-ServiceAutoRestart: Configures Windows Service Control Manager recovery actions for SQL Server and SQL Server Agent.

Network and Connectivity

• Configure-StaticPort: Configures a fixed TCP port for the SQL Server instance and restarts the Database Engine when required.
• Configure-Aliases: Creates 32-bit and 64-bit SQL Server client aliases on the target servers.
• Configure-FirewallRules: Creates Windows Firewall rules according to the configured SQL Server network policy.
• Configure-SqlBrowserService: Stops and disables the SQL Server Browser service.
• Configure-HideInstance: Hides the SQL Server instance from SQL Browser discovery.

SQL Server Security and Instance Configuration

• Configure-SaDisable: Renames the built-in sa login to adm-SQL-sa and disables it.
• Configure-GuestDisable: Revokes guest CONNECT permissions from eligible writable databases.
• Configure-AdvancedOptions: Applies advanced SQL Server configuration, authentication policy, security restrictions, and backup defaults.
• Configure-SqlServiceSidLogins: Creates or verifies SQL Server per-service SID logins and their required sysadmin permissions.
• Configure-NtAuthoritySystemPermissions: Removes sysadmin membership from NT AUTHORITY\SYSTEM and applies topology-specific SQL Server permissions.
• Configure-SqlLoginPolicy: Enforces Windows password policy for eligible SQL-authenticated logins.

Performance and Resource Management

• Configure-MemoryLimits: Configures minimum and maximum SQL Server memory according to physical memory and multi-instance policies.
• Configure-MaxDopParallelism: Calculates MAXDOP using CPU and NUMA topology and configures the cost threshold for parallelism and remote query timeout.

Storage and Database Configuration

• Configure-DefaultDirectories: Configures or preserves SQL Server default data, log, and backup directories.
• Configure-SqlDirectoryPermissions: Applies Database Engine service SID permissions to the actual local SQL Server directories.
• Configure-AutoGrowth: Standardizes system database file growth settings.
• Configure-DatabaseBaseline: Configures PAGE_VERIFY CHECKSUM, AUTO_CLOSE OFF, and AUTO_SHRINK OFF for eligible databases.
• Configure-QueryStoreBaseline: Enables Query Store in READ_WRITE mode for eligible user databases.
• Configure-TempDb: Configures TempDB file count, initial file sizes, file growth, and optional storage locations, with disk capacity validation.

Monitoring and Diagnostics

• Configure-TraceFlags: Configures and verifies persistent SQL Server trace flags 3226 and 3625.
• Configure-TraceAudit: Configures SQL Server error log retention, login auditing, and default trace settings.
• Configure-CriticalSqlAgentAlerts: Creates or verifies SQL Server Agent alerts for errors 823, 824, and 825 and severity levels 19–25, with optional DBA operator email notifications.

System Requirements

The automation framework is designed for environments meeting the following requirements:

• Windows Server 2016 or later.
• SQL Server 2019 or later.
• PowerShell remoting enabled on all target servers.
• Administrative permissions on the target Windows servers.
• Sufficient SQL Server permissions to execute the selected configuration functions.
• SQL Server Database Engine and SQL Server Agent installed for the selected instance.
• The Invoke-Sqlcmd command available on all target servers.

The script supports default and named SQL Server instances, standalone servers, and multi-node deployments. A domain is optional when SQL Server virtual service accounts are used.

The framework does not require the dbatools PowerShell module.

Configuration and Execution

Before running the script, administrators must review and configure the environment-specific variables, including the target server list, SQL Server instance name, TCP port, and service account identities.

Additional policy variables allow customization of authentication mode, firewall configuration, SQL Server memory allocation, parallelism settings, default database directories, TempDB configuration, and SQL Agent notifications.

The script performs prerequisite validation and SQL Server topology detection before executing the selected configuration functions.

A centralized execution menu allows administrators to control which functions are executed by commenting or uncommenting individual function calls.

The current v2.13 execution menu includes service dependency and TempDB configuration, while Windows Time maintenance is disabled by default.

The framework reports configuration results through console messages, including successful operations, applied changes, existing configurations, warnings, failures, and pending actions.

Important: Certain configuration functions may restart SQL Server services or modify settings that affect database connectivity and availability. Production execution should be performed under an appropriate change management and maintenance procedure.

The framework does not create Availability Groups, perform automatic failovers, or configure Failover Cluster Instances. TLS certificate deployment, antivirus exclusions, SQL Server patching, maintenance frameworks, and backup-job deployment remain outside the scope of this automated baseline.