Copy-DbaSpConfigure View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Copies SQL Server configuration settings (sp_configure values) from source to destination instances.
Description This function retrieves all sp_configure settings from the source SQL Server and applies them to one or more destination instances, ensuring consistent configuration across your environment. Only settings that differ between source and destination are updated, making it safe for standardizing existing servers. The function automatically handles settings that require a restart and provides detailed reporting of which configurations were changed, skipped, or failed.
Export-DbaSpConfigure View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Generates SQL script containing all sp_configure settings for SQL Server instance configuration replication and documentation.
Description Creates a complete SQL script file with EXEC sp_configure statements for all server configuration options, including advanced settings. This script can be executed on another SQL Server instance to replicate the exact same configuration settings, making it invaluable for environment standardization, disaster recovery preparation, or compliance documentation.
Get-DbaInstanceProperty View Source Klaas Vandenberghe (@powerdbaklaas) Windows, Linux, macOS Synopsis Retrieves comprehensive SQL Server instance configuration properties for auditing and comparison
Description Retrieves all instance-level configuration properties from SQL Server’s Information, UserOptions, and Settings collections via SMO. This gives you a complete inventory of server settings like default file paths, memory configuration, security options, and user defaults in a standardized format. Essential for configuration audits, compliance reporting, environment comparisons, and troubleshooting configuration-related issues across multiple instances.
Get-DbaInstanceUserOption View Source Klaas Vandenberghe (@powerdbaklaas) Windows, Linux, macOS Synopsis Retrieves instance-level user option defaults that affect new database connections
Description Returns the default user options configured at the SQL Server instance level that are automatically applied to new database connections. These settings include ANSI compliance options like ANSI_NULLS, QUOTED_IDENTIFIER, date format preferences, and other connection-level defaults. This is useful when standardizing connection behavior across environments or troubleshooting why applications behave differently on different instances.
Get-DbaSpConfigure View Source Nic Cain, sirsql.net Windows, Linux, macOS Synopsis Retrieves SQL Server sp_configure settings with default value comparisons for configuration auditing
Description Retrieves all SQL Server instance-level configuration settings accessible through sp_configure, using SMO to gather comprehensive details about each setting. This function compares current configured and running values against SQL Server defaults to quickly identify which settings have been customized from their out-of-box values.
Essential for configuration auditing, compliance checks, and ensuring consistency across multiple SQL Server environments.
Import-DbaSpConfigure View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Copies sp_configure settings between SQL Server instances or applies settings from a SQL file.
Description Copies all sp_configure settings from a source SQL Server instance to a destination instance, or applies sp_configure settings from a SQL file to an instance. This function handles advanced options visibility, validates server versions for compatibility, and executes the necessary RECONFIGURE statements. Essential for maintaining consistent configuration across environments during migrations, standardization projects, or when applying saved configuration templates.
Set-DbaPowerPlan View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Configures Windows power plan on SQL Server host computers to optimize database performance.
Description Changes the Windows power plan on SQL Server host machines using WMI and PowerShell remoting. Defaults to High Performance, which prevents CPU throttling that can severely impact database query performance and response times.
Windows power plans control CPU frequency scaling, and the default “Balanced” plan can cause significant performance degradation under SQL Server workloads.
Set-DbaStartupParameter View Source Stuart Moore (@napalmgram), stuart-moore.com Windows, Linux, macOS Synopsis Modifies SQL Server startup parameters stored in the Windows registry
Description Changes the startup parameters that SQL Server uses when the service starts, including paths to master database files, error log location, and various startup flags. These parameters are stored in the Windows registry and require elevated permissions to modify.
This function is commonly used to enable single-user mode for emergency repairs, set trace flags for troubleshooting, relocate system database files during migrations, or adjust memory settings.
Set-DbaTcpPort View Source @H0s0n77 Windows, Linux, macOS Synopsis Configures SQL Server TCP port settings for specified instances and IP addresses.
Description Configures TCP port settings for SQL Server instances by modifying the network configuration through SQL Server Configuration Manager functionality. This replaces the manual process of opening SQL Server Configuration Manager to change port settings for security hardening or network compliance.
The function can target all IP addresses (IPAll setting) or specific IP addresses, disables dynamic port allocation, and sets static port numbers.
Test-DbaOptimizeForAdHoc View Source Brandon Abshire, netnerds.net Windows, Linux, macOS Synopsis Tests whether the SQL Server “optimize for ad-hoc workloads” configuration setting is enabled.
Description Checks the current value of the “optimize for ad-hoc workloads” server configuration option and compares it against the recommended setting of 1 (enabled). This setting helps prevent plan cache bloat by storing only compiled plan stubs for single-use ad hoc queries instead of full execution plans.
dbatools