Copy-DbaSpConfigure View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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.
Export-DbaSpConfigure View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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 On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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.
Get-DbaInstanceUserOption View Source Klaas Vandenberghe (@powerdbaklaas) Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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.
Get-DbaSpConfigure View Source Nic Cain, sirsql.net Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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.
Import-DbaSpConfigure View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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.
Set-DbaPowerPlan View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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.
Set-DbaStartupParameter View Source Stuart Moore (@napalmgram), stuart-moore.com Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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.
Set-DbaTcpPort View Source @H0s0n77 Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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.
Test-DbaOptimizeForAdHoc View Source Brandon Abshire, netnerds.net Windows, Linux, macOS On this page: Synopsis · Description · Syntax · Examples · Parameters · Outputs 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