Automatic gathering of SQL Server configuration by using PowerShell

Rate this post

At present, the gathering of SQL Server configuration information is no longer a problem— the opportunities to add value through an update and to build a stable database environment exist permanently. Most of the third party tools provide the appropriate functionality in order to capture the software market. Through this guide, you’ll figure out the ways to capture few SQL Server configurations and manipulate the data by using PowerShell.

Moreover, you will get the answers to the following questions:

  • How to gather SQL Server configuration
  • How to automatically discover SQL instances
  • How to manually input the SQL Server instances to speed up the process
  • How to transform data by using PowerShell
  • And more…

Getting Started

In this article, we’ll go over an existing example of using PowerShell and SQL Server. Proper tuning up the configuration parameters will always produce a better performance. In the appendix section, you will be able to see a full-length script to query the configuration settings. The output can be used as a baseline for your instance and your databases for performance troubleshooting.

Introduction

In a moment, we’ll see how to audit the SQL Server configuration details of the database environment. In our case, we will talk about how to list XP_CMDSHELL, SQL Server Memory Settings—Max and Min memory configurations, and Traceflags property values across the SQL instances.

XP_CMDSHELL

The xp_cmdshell is a configuration property that enables the required option to execute the extended stored procedure inside the system.

Note: The xp_cmdshell is disabled by default. There are several instances where the legacy application may require this feature to be enabled. It is critical to consider this option enabled because it has several security implications and risks.

In the following PowerShell code, the configuration class of SQL Server management objects is instantiated in order to get the configvalue parameter of each SQL Server instance.

Note: the XPCmdShellEnabled property that is used to get the ConfigProperty or configvalue object, is used to configure XP_CMDSHELL configuration in our case.

The following PowerShell code lists the configuration value for the XP_CMDSHELL property of the listed server ‘hqdbt01’:

In this section, you’ll learn how to display a custom header named xp_cmdshell along with the customized value for that property. xp_cmdshell configuration is explained in detail.

powershell_1

Note: To get property of configuration class, you need to use $_ as it represents the current scope of SMO class ‘Microsoft.SqlServer.Management.Smo.Server’ in the pipeline extended with the configuration class that uses a dot membership operator to get the property of xp_cmdshell.

Next, you’ll learn how to use the condition statement in the expression operation. In the following code, you can see the usage of IF statement in the expression. The IF statement runs the conditional block, in this case, $_.Configuration.XPCmdShellEnabled.configValue. It is evaluated for ‘1’ to return Enabled. For other values, it returns Disabled.
Let’s see the PowerShell code to retrieve the xp_cmdshell description using “if” statement in the expression.
powershell_2

SQL Server Memory Settings

SQL Server memory settings have two options: “max_memory” setting and “min_memory” setting. Now, learn how to list the configured values of memory parameters of all the listed servers.

Note: By default, min server memory setting is configured with a 0 value, and the max server memory is configured with 2,147,483,647 MB. On another note, by default, the SQL Server engine will manage memory requirements more dynamically.

The following PowerShell code gathers the memory setting of all the SQL instances that are listed in the input servers.

In the following screenshot, you can see that the memory-related properties MaxServerMemory.configValue and MinServerMemory.configValue are gathered in order to use the same method as described in the XP_CMDSHELL section.

powershell_3

OS and SQL Wildcard expression

In this section, we’ll see how to use PowerShell regular expression with a “switch” statement in the expression statement.
Wildcard characters are forms of regular expression. In this case, the wildcard character is represented by an asterisk and is used to represent any character that can be repeated any number of times. In this case, you’ll see asterisk wildcard that is used with the “switch” statement.

The “switch” statement is an extension of IF-ELSE conditional statement. In this demo, it will retrieve a versionString and OSVersion properties of the SQL Server object and its compared values to print the respective OS and SQL Server version. The usage of the ‘*’ wildcard in the statement includes the same logic just as the LIKE operator to match each field.

Note: The Switch statement is actually a nested IF-ELSE statement.

powershell_4

Next, let’s put all together to retrieve a set of configuration values by using PowerShell. In the following PowerShell code, replace the input array of servers.

Code

The output is a formatted list of configuration values generated via using SQL Server Managed objects along with the data transformation using PowerShell techniques.

powershell_5

Summary

This guide is an effort to outline some of the SQL Server information and configuration, such as xp_cmdshell, memory settings, trace flag, os version, and SQL version. It is recommended to use the above snippets to gather other properties of your choice using SMO (SQL Server Management Object) configuration class. You’ll also learn the tips to use a conditional statement, switch statement and looping statement in the expression statement using PowerShell.

I hope you liked this article. Please, leave your feedback in the comment section.

Appendix

Code

Prashanth Jayaram

Prashanth Jayaram is working as a DB Manager for a leading MNC, Database Technologist, Author, Blogger, Automation Expert, Technet WIKI Ninja, and Powershell Geek.
Prashanth Jayaram