Automatic gathering of SQL Server configuration by using PowerShell

Total: 5 Average: 3.8

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’:

'hqdbt01'|

ForEach-Object {

Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName |

%{

#$_.ComputerNamePhysicalNetBIOS

#$_.DisplayName

If (($_.DisplayName -eq 'Default') -or ($_.DisplayName -eq 'MSSQLSERVER')) {

$SQL = $_.name

}

elseif ($_.InstanceName -ne $NULL)

{

#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"

$SQL = $_.name

}

New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQL |

SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}}, @{label='InstanceName';expression={$_.Name}},@{label='xp_cmdshell';expression= {$_.Configuration.XPCmdShellEnabled.configValue}} |SELECT ServerName,InstanceName,xp_cmdshell

}

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.

SELECT @{label='InstanceName';expression={$_.Name}},@{label='xp_cmdshell';expression= {$_.Configuration.XPCmdShellEnabled.configValue}}

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.

@{label='xp_cmdshellDesc';expression= { if($_.Configuration.XPCmdShellEnabled.configValue -eq 1) { 'enabled' } else {'disabled'}}}

Let’s see the PowerShell code to retrieve the xp_cmdshell description using “if” statement in the expression.

'hqdbt01'|

ForEach-Object {

Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName |

%{

#$_.ComputerNamePhysicalNetBIOS

#$_.DisplayName

If (($_.DisplayName -eq 'Default') -or ($_.DisplayName -eq 'MSSQLSERVER')) {

$SQL = $_.name

}

elseif ($_.InstanceName -ne $NULL)

{

#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"

$SQL = $_.name

}

New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQL |

SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}},

@{label='InstanceName';expression={$_.Name}},@{label='xp_cmdshell';expression= {$_.Configuration.XPCmdShellEnabled.configValue}},

@{label='xp_cmdshellDesc';expression= { if($_.Configuration.XPCmdShellEnabled.configValue -eq 1) { 'enabled' } else {'disabled'}}}`

|SELECT ServerName,InstanceName,xp_cmdshell,xp_cmdshellDesc

}

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.

'hqdbt01'|

ForEach-Object {

Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName |

%{

#$_.ComputerNamePhysicalNetBIOS

#$_.DisplayName

If (($_.DisplayName -eq 'Default') -or ($_.DisplayName -eq 'MSSQLSERVER')) {

$SQL = $_.name

}

elseif ($_.InstanceName -ne $NULL)

{

#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"

$SQL = $_.name

}

New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQL |

SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}},

@{label='InstanceName';expression={$_.Name}},

@{label='Max';expression={$_.Configuration.MaxServerMemory.configValue}},

@{label='MaxDesc';expression={ if ($_.Configuration.MaxServerMemory.configValue -eq 2147483647) {'Default'} else {'Custom'}}},

@{label='Min';expression={$_.Configuration.MinServerMemory.configValue}},

@{label='MinDesc';expression={ if ($_.Configuration.MinServerMemory.configValue -eq 0) {'Default'} else {'Custom'}}}`

|SELECT ServerName,InstanceName,Max,MaxDesc,Min,MinDesc

}

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.

'hqdbt01'|

ForEach-Object {

Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName |

%{

#$_.ComputerNamePhysicalNetBIOS

#$_.DisplayName

If (($_.DisplayName -eq 'Default') -or ($_.DisplayName -eq 'MSSQLSERVER')) {

$SQL = $_.name

}

elseif ($_.InstanceName -ne $NULL)

{

#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"

$SQL = $_.name

}

New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQL |

SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}},

@{label='InstanceName';expression={$_.Name}},

@{label='SQLVersion';expression={ switch -wildcard( $_.VersionString )`

{`

"8*" { 'SQL Server 2000' }

"9*" { 'SQL Server 2005' }

"10*" { 'SQL Server 2008/R2' }

"10.5*" { 'SQL Server 2008 R2' }

"11*" { 'SQL Server 2012' }

"12*" { 'SQL Server 2014' }

"13*" { 'SQL Server 2016' }

"14*" { 'SQL Server 2017' }

`

}}},

@{label='OSVersion';expression={

switch -wildcard( $_.OSVersion )

{

"5.0*" { 'Windows Server 2000' }

"5.1*" { 'Windows XP' }

"5.2*" { 'Windows Server 2003' }

"6.0*" { 'Windows Server 2008' }

"6.1*" { 'Windows Server 2008 R2' }

"6.2*" { 'Windows Server 2012' }

"6.3*" { 'Windows Server 2012 R2' }

"10.0*" { 'Windows 10/2019/2016' }

}

}

}

}

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
#An array to hold the output values

$Results=@()

#Input server names for the configuration listing

'hqmesrp01','hqmesrp02','hqdbt01'|

ForEach-Object {

Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName |

%{

#$_.ComputerNamePhysicalNetBIOS

#$_.DisplayName

If (($_.DisplayName -eq 'Default') -or ($_.DisplayName -eq 'MSSQLSERVER')) {

$SQL = $_.name

}

elseif ($_.InstanceName -ne $NULL)

{

#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"

$SQL = $_.name

}

$a=''

$inv=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQL |

SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}}, @{label='InstanceName';expression={$_.Name}} ,@{label='Max';expression={$_.Configuration.MaxServerMemory.configValue}},

@{label='Min';expression={$_.Configuration.MinServerMemory.configValue}},

@{label='xp_cmdshell';expression= {$_.Configuration.XPCmdShellEnabled.configValue}},

@{label='xp_cmdshell1';expression= { if($_.Configuration.XPCmdShellEnabled.configValue -eq 1) { 'enabled' } else {'disabled'}}},

@{label='SQLVersion';expression={ switch -wildcard( $_.VersionString )`

{`

"8*" { 'SQL Server 2000' }`

"9*" { 'SQL Server 2005' }`

"10*" { 'SQL Server 2008/R2' }`

"10.5*" { 'SQL Server 2008 R2' }`

"11*" { 'SQL Server 2012' }`

"12*" { 'SQL Server 2014' }`

"13*" { 'SQL Server 2016' }`

"14*" { 'SQL Server 2017' }`

`

}}},

@{label='OSVersion';expression={

switch -wildcard( $_.OSVersion )

{

"5.0*" { 'Windows Server 2000' }

"5.1*" { 'Windows XP' }

"5.2*" { 'Windows Server 2003' }

"6.0*" { 'Windows Server 2008' }

"6.1*" { 'Windows Server 2008 R2' }

"6.2*" { 'Windows Server 2012' }

"6.3*" { 'Windows Server 2012 R2/8.1' }

"10.0*" { 'Windows 10/2019/2016' }

}

}

},

@{label='Traceflag';expression={ $_.EnumActiveGlobalTraceFlags()| % {

[string]$b=$_.status

[string]$c=$_.TraceFlag

$a+=$c.ToString() +'->'+ $b.ToString()+ ' '

}

$a

}

}

ForEach($i in $inv)

{

$Properties = @{Name=$i.ServerName

InstanceName =$i.InstanceName

MaxMemory=$i.Max

MinMemory=$i.Min

xp_cmdshell=$i.xp_cmdshell

xp_cmdshellStatus=$i.xp_cmdshell1

SQLVersion=$i.SQLVersion

OSVersion=$i.OSVersion

Traceflag=$i.Traceflag

}

$Results += New-Object psobject -Property $properties

}

}

$Results |Select Name,InstanceName,OSVersion,SQLVersion,xp_cmdshell,xp_cmdshellStatus,Traceflag,MinMemory,MaxMemory| format-table -AutoSize

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
#Output file to hold the list

$Outputfile='c:\output.csv'

#An array to hold the output values

$Results=@()

'hqmesrp01','hqmesrp02','hqdbt01'|

ForEach-Object {

Get-ChildItem -Path "SQLSERVER:\SQL\$_"} | Select-Object -Property ComputerNamePhysicalNetBIOS, Name, DisplayName, InstanceName |

%{

#$_.ComputerNamePhysicalNetBIOS

#$_.DisplayName

If (($_.DisplayName -eq 'Default') -or ($_.DisplayName -eq 'MSSQLSERVER')) {

$SQL = $_.name

}

elseif ($_.InstanceName -ne $NULL)

{

#$SQLInstance = "$($_.ComputerNamePhysicalNetBIOS)\$($_.InstanceName)"

$SQL = $_.name

}

$inv=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $SQL |

SELECT @{label='ServerName';expression={$_.ComputerNamePhysicalNetBIOS}}, @{label='InstanceName';expression={$_.Name}} ,@{label='Max';expression={$_.Configuration.MaxServerMemory.configValue}},

@{label='Min';expression={$_.Configuration.MinServerMemory.configValue}},

@{label='AdHocDistributedQueriesEnabled';expression={$_.Configuration.AdHocDistributedQueriesEnabled.configValue}},

@{label='Affinity64IOMask';expression={$_.Configuration.Affinity64IOMask.configValue}},

@{label='Affinity64Mask';expression={$_.Configuration.Affinity64Mask.configValue}},

@{label='xp_cmdshell';expression= {$_.Configuration.XPCmdShellEnabled.configValue}},

@{label='xp_cmdshell1';expression= { if($_.Configuration.XPCmdShellEnabled.configValue -eq 1) { 'enabled' } else {'disabled'}}},

@{label='AgentXPsEnabled';expression={$_.Configuration.AgentXPsEnabled.configValue}},

@{label='AllowUpdates';expression={$_.Configuration.AllowUpdates.configValue}},

@{label='AweEnabled';expression={$_.Configuration.AweEnabled.configValue}},

@{label='BlockedProcessThreshold';expression={$_.Configuration.BlockedProcessThreshold.configValue}},

@{label='C2AuditMode';expression={$_.Configuration.C2AuditMode.configValue}},

@{label='CommonCriteriaComplianceEnabled';expression={$_.Configuration.CommonCriteriaComplianceEnabled.configValue}},

@{label='ContainmentEnabled';expression={$_.Configuration.ContainmentEnabled.configValue}},

@{label='CostThresholdForParallelism';expression={$_.Configuration.CostThresholdForParallelism.configValue}},

@{label='CrossDBOwnershipChaining';expression={$_.Configuration.CrossDBOwnershipChaining.configValue}},

@{label='CursorThreshold';expression={$_.Configuration.CursorThreshold.configValue}},

@{label='DatabaseMailEnabled';expression={$_.Configuration.DatabaseMailEnabled.configValue}},

@{label='DefaultBackupCompression';expression={$_.Configuration.DefaultBackupCompression.configValue}},

@{label='DefaultFullTextLanguage';expression={$_.Configuration.DefaultFullTextLanguage.configValue}},

@{label='DefaultLanguage';expression={$_.Configuration.DefaultLanguage.configValue}},

@{label='DefaultTraceEnabled';expression={$_.Configuration.DefaultTraceEnabled.configValue}},

@{label='DisallowResultsFromTriggers';expression={$_.Configuration.DisallowResultsFromTriggers.configValue}},

@{label='ExtensibleKeyManagementEnabled';expression={$_.Configuration.ExtensibleKeyManagementEnabled.configValue}},

@{label='FilestreamAccessLevel';expression={$_.Configuration.FilestreamAccessLevel.configValue}},

@{label='FillFactor';expression={$_.Configuration.FillFactor.configValue}},

@{label='FullTextCrawlBandwidthMax';expression={$_.Configuration.FullTextCrawlBandwidthMax.configValue}},

@{label='FullTextCrawlBandwidthMin';expression={$_.Configuration.FullTextCrawlBandwidthMin.configValue}},

@{label='FullTextCrawlRangeMax';expression={$_.Configuration.FullTextCrawlRangeMax.configValue}},

@{label='FullTextNotifyBandwidthMax';expression={$_.Configuration.FullTextNotifyBandwidthMax.configValue}},

@{label='FullTextNotifyBandwidthMin';expression={$_.Configuration.FullTextNotifyBandwidthMin.configValue}},

@{label='IndexCreateMemory';expression={$_.Configuration.IndexCreateMemory.configValue}},

@{label='InDoubtTransactionResolution';expression={$_.Configuration.InDoubtTransactionResolution.configValue}},

@{label='IsSqlClrEnabled';expression={$_.Configuration.IsSqlClrEnabled.configValue}},

@{label='LightweightPooling';expression={$_.Configuration.LightweightPooling.configValue}},

@{label='Locks';expression={$_.Configuration.Locks.configValue}},

@{label='MaxDegreeOfParallelism';expression={$_.Configuration.MaxDegreeOfParallelism.configValue}},

@{label='MaxServerMemory';expression={$_.Configuration.MaxServerMemory.configValue}},

@{label='MaxWorkerThreads';expression={$_.Configuration.MaxWorkerThreads.configValue}},

@{label='MediaRetention';expression={$_.Configuration.MediaRetention.configValue}},

@{label='MinMemoryPerQuery';expression={$_.Configuration.MinMemoryPerQuery.configValue}},

@{label='MinServerMemory';expression={$_.Configuration.MinServerMemory.configValue}},

@{label='NestedTriggers';expression={$_.Configuration.NestedTriggers.configValue}},

@{label='NetworkPacketSize';expression={$_.Configuration.NetworkPacketSize.configValue}},

@{label='OleAutomationProceduresEnabled';expression={$_.Configuration.OleAutomationProceduresEnabled.configValue}},

@{label='OpenObjects';expression={$_.Configuration.OpenObjects.configValue}},

@{label='OptimizeAdhocWorkloads';expression={$_.Configuration.OptimizeAdhocWorkloads.configValue}},

@{label='Parent';expression={$_.Configuration.Parent.configValue}},

@{label='PrecomputeRank';expression={$_.Configuration.PrecomputeRank.configValue}},

@{label='PriorityBoost';expression={$_.Configuration.PriorityBoost.configValue}},

@{label='Properties';expression={$_.Configuration.Properties.configValue}},

@{label='ProtocolHandlerTimeout';expression={$_.Configuration.ProtocolHandlerTimeout.configValue}},

@{label='QueryGovernorCostLimit';expression={$_.Configuration.QueryGovernorCostLimit.configValue}},

@{label='QueryWait';expression={$_.Configuration.QueryWait.configValue}},

@{label='RecoveryInterval';expression={$_.Configuration.RecoveryInterval.configValue}},

@{label='RemoteAccess';expression={$_.Configuration.RemoteAccess.configValue}},

@{label='RemoteDacConnectionsEnabled';expression={$_.Configuration.RemoteDacConnectionsEnabled.configValue}},

@{label='RemoteDataArchiveEnabled';expression={$_.Configuration.RemoteDataArchiveEnabled.configValue}},

@{label='RemoteLoginTimeout';expression={$_.Configuration.RemoteLoginTimeout.configValue}},

@{label='RemoteProcTrans';expression={$_.Configuration.RemoteProcTrans.configValue}},

@{label='RemoteQueryTimeout';expression={$_.Configuration.RemoteQueryTimeout.configValue}},

@{label='ReplicationMaxTextSize';expression={$_.Configuration.ReplicationMaxTextSize.configValue}},

@{label='ReplicationXPsEnabled';expression={$_.Configuration.ReplicationXPsEnabled.configValue}},

@{label='ScanForStartupProcedures';expression={$_.Configuration.ScanForStartupProcedures.configValue}},

@{label='ServerTriggerRecursionEnabled';expression={$_.Configuration.ServerTriggerRecursionEnabled.configValue}},

@{label='SetWorkingSetSize';expression={$_.Configuration.SetWorkingSetSize.configValue}},

@{label='ShowAdvancedOptions';expression={$_.Configuration.ShowAdvancedOptions.configValue}},

@{label='SmoAndDmoXPsEnabled';expression={$_.Configuration.SmoAndDmoXPsEnabled.configValue}},

@{label='SqlMailXPsEnabled';expression={$_.Configuration.SqlMailXPsEnabled.configValue}},

@{label='TransformNoiseWords';expression={$_.Configuration.TransformNoiseWords.configValue}},

@{label='TwoDigitYearCutoff';expression={$_.Configuration.TwoDigitYearCutoff.configValue}},

@{label='UserConnections';expression={$_.Configuration.UserConnections.configValue}},

@{label='UserInstancesEnabled';expression={$_.Configuration.UserInstancesEnabled.configValue}},

@{label='UserInstanceTimeout';expression={$_.Configuration.UserInstanceTimeout.configValue}},

@{label='UserOptions';expression={$_.Configuration.UserOptions.configValue}},

@{label='WebXPsEnabled';expression={$_.Configuration.WebXPsEnabled.configValue}},

@{label='XPCmdShellEnabled';expression={$_.Configuration.XPCmdShellEnabled.configValue}},

@{label='SQLVersion';expression={ switch -wildcard( $_.VersionString )`

{`

"8*" { 'SQL Server 2000' }`

"9*" { 'SQL Server 2005' }`

"10*" { 'SQL Server 2008/R2' }`

"10.5*" { 'SQL Server 2008 R2' }`

"11*" { 'SQL Server 2012' }`

"12*" { 'SQL Server 2014' }`

"13*" { 'SQL Server 2016' }`

"14*" { 'SQL Server 2017' }`

`

}}},

@{label='OSVersion';expression={

switch -wildcard( $_.OSVersion )

{

"5.0*" { 'Windows Server 2000' }

"5.1*" { 'Windows XP' }

"5.2*" { 'Windows Server 2003' }

"6.0*" { 'Windows Server 2008' }

"6.1*" { 'Windows Server 2008 R2' }

"6.2*" { 'Windows Server 2012' }

"6.3*" { 'Windows Server 2012 R2/8.1' }

"10.0*" { 'Windows 10/2019/2016' }

}

}

},

@{label='Traceflag';expression={ $_.EnumActiveGlobalTraceFlags()| % {

[string]$b=$_.status

[string]$c=$_.TraceFlag

$a+=$c.ToString() +'->'+ $b.ToString()+ ' '

}

$a

}

}

ForEach($i in $inv)

{

$Properties = @{Name=$i.ServerName

InstanceName =$i.InstanceName

MaxMemory=$i.Max

MinMemory=$i.Min

xp_cmdshell=$i.xp_cmdshell

xp_cmdshellStatus=$i.xp_cmdshell1

SQLVersion=$i.SQLVersion

OSVersion=$i.OSVersion

Traceflag=$i.Traceflag

AdHocDistributedQueriesEnabled=$i.AdHocDistributedQueriesEnabled

Affinity64IOMask=$i.Affinity64IOMask

Affinity64Mask=$i.Affinity64Mask

AffinityIOMask=$i.AffinityIOMask

AffinityMask=$i.AffinityMask

AgentXPsEnabled=$i.AgentXPsEnabled

AllowUpdates=$i.AllowUpdates

AweEnabled=$i.AweEnabled

BlockedProcessThreshold=$i.BlockedProcessThreshold

C2AuditMode=$i.C2AuditMode

CommonCriteriaComplianceEnabled=$i.CommonCriteriaComplianceEnabled

ContainmentEnabled=$i.ContainmentEnabled

CostThresholdForParallelism=$i.CostThresholdForParallelism

CrossDBOwnershipChaining=$i.CrossDBOwnershipChaining

CursorThreshold=$i.CursorThreshold

DatabaseMailEnabled=$i.DatabaseMailEnabled

DefaultBackupCompression=$i.DefaultBackupCompression

DefaultFullTextLanguage=$i.DefaultFullTextLanguage

DefaultLanguage=$i.DefaultLanguage

DefaultTraceEnabled=$i.DefaultTraceEnabled

DisallowResultsFromTriggers=$i.DisallowResultsFromTriggers

ExtensibleKeyManagementEnabled=$i.ExtensibleKeyManagementEnabled

FilestreamAccessLevel=$i.FilestreamAccessLevel

FillFactor=$i.FillFactor

FullTextCrawlBandwidthMax=$i.FullTextCrawlBandwidthMax

FullTextCrawlBandwidthMin=$i.FullTextCrawlBandwidthMin

FullTextCrawlRangeMax=$i.FullTextCrawlRangeMax

FullTextNotifyBandwidthMax=$i.FullTextNotifyBandwidthMax

FullTextNotifyBandwidthMin=$i.FullTextNotifyBandwidthMin

IndexCreateMemory=$i.IndexCreateMemory

InDoubtTransactionResolution=$i.InDoubtTransactionResolution

IsSqlClrEnabled=$i.IsSqlClrEnabled

LightweightPooling=$i.LightweightPooling

Locks=$i.Locks

MaxDegreeOfParallelism=$i.MaxDegreeOfParallelism

MaxServerMemory=$i.MaxServerMemory

MaxWorkerThreads=$i.MaxWorkerThreads

MediaRetention=$i.MediaRetention

MinMemoryPerQuery=$i.MinMemoryPerQuery

MinServerMemory=$i.MinServerMemory

NestedTriggers=$i.NestedTriggers

NetworkPacketSize=$i.NetworkPacketSize

OleAutomationProceduresEnabled=$i.OleAutomationProceduresEnabled

OpenObjects=$i.OpenObjects

OptimizeAdhocWorkloads=$i.OptimizeAdhocWorkloads

Parent=$i.Parent

PrecomputeRank=$i.PrecomputeRank

PriorityBoost=$i.PriorityBoost

ProtocolHandlerTimeout=$i.ProtocolHandlerTimeout

QueryGovernorCostLimit=$i.QueryGovernorCostLimit

QueryWait=$i.QueryWait

RecoveryInterval=$i.RecoveryInterval

RemoteAccess=$i.RemoteAccess

RemoteDacConnectionsEnabled=$i.RemoteDacConnectionsEnabled

RemoteDataArchiveEnabled=$i.RemoteDataArchiveEnabled

RemoteLoginTimeout=$i.RemoteLoginTimeout

RemoteProcTrans=$i.RemoteProcTrans

RemoteQueryTimeout=$i.RemoteQueryTimeout

ReplicationMaxTextSize=$i.ReplicationMaxTextSize

ReplicationXPsEnabled=$i.ReplicationXPsEnabled

ScanForStartupProcedures=$i.ScanForStartupProcedures

ServerTriggerRecursionEnabled=$i.ServerTriggerRecursionEnabled

SetWorkingSetSize=$i.SetWorkingSetSize

ShowAdvancedOptions=$i.ShowAdvancedOptions

SmoAndDmoXPsEnabled=$i.SmoAndDmoXPsEnabled

SqlMailXPsEnabled=$i.SqlMailXPsEnabled

TransformNoiseWords=$i.TransformNoiseWords

TwoDigitYearCutoff=$i.TwoDigitYearCutoff

UserConnections=$i.UserConnections

UserInstancesEnabled=$i.UserInstancesEnabled

UserInstanceTimeout=$i.UserInstanceTimeout

UserOptions=$i.UserOptions

WebXPsEnabled=$i.WebXPsEnabled

XPCmdShellEnabled=$i.XPCmdShellEnabled

}

$Results += New-Object psobject -Property $properties

}

}

$Results |Select Name,InstanceName,OSVersion,SQLVersion,xp_cmdshell,xp_cmdshellStatus,Traceflag,MinMemory,MaxMemory,AdHocDistributedQueriesEnabled,Affinity64IOMask,Affinity64Mask,AffinityIOMask,AffinityMask,AgentXPsEnabled,AllowUpdates,AweEnabled,BlockedProcessThreshold,C2AuditMode,CommonCriteriaComplianceEnabled,ContainmentEnabled,CostThresholdForParallelism,CrossDBOwnershipChaining,CursorThreshold,DatabaseMailEnabled,DefaultBackupCompression,DefaultFullTextLanguage,DefaultLanguage,DefaultTraceEnabled,DisallowResultsFromTriggers,ExtensibleKeyManagementEnabled,FilestreamAccessLevel,FillFactor,FullTextCrawlBandwidthMax,FullTextCrawlBandwidthMin,FullTextCrawlRangeMax,FullTextNotifyBandwidthMax,FullTextNotifyBandwidthMin,IndexCreateMemory,InDoubtTransactionResolution,IsSqlClrEnabled,LightweightPooling,Locks,MaxDegreeOfParallelism,MaxServerMemory,MaxWorkerThreads,MediaRetention,MinMemoryPerQuery,MinServerMemory,NestedTriggers,NetworkPacketSize,OleAutomationProceduresEnabled,OpenObjects,OptimizeAdhocWorkloads,Parent,PrecomputeRank,PriorityBoost,Properties,ProtocolHandlerTimeout,QueryGovernorCostLimit,QueryWait,RecoveryInterval,RemoteAccess,RemoteDacConnectionsEnabled,RemoteDataArchiveEnabled,RemoteLoginTimeout,RemoteProcTrans,RemoteQueryTimeout,ReplicationMaxTextSize,ReplicationXPsEnabled,ScanForStartupProcedures,ServerTriggerRecursionEnabled,SetWorkingSetSize,ShowAdvancedOptions,SmoAndDmoXPsEnabled,SqlMailXPsEnabled,TransformNoiseWords,TwoDigitYearCutoff,UserConnections,UserInstancesEnabled,UserInstanceTimeout,UserOptions,WebXPsEnabled,XPCmdShellEnabled| Export-Csv $outputfile
Prashanth Jayaram

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.