Listing SQL Server configuration settings

This recipe walks through how to list SQL Server configurable and non-configurable instance settings using PowerShell.

How to do it...

1. Open the PowerShell ISE. Go to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Import the SQLPS module, and create a new SMO Server object:

   #import SQL Server module
   Import-Module SQLPS -DisableNameChecking
   
   #replace this with your instance name
   $instanceName = "KERRIGAN"
   $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

   To explore what members and methods are included in the SMO server, use the following code snippet in PowerShell V3:

   #Explore: get all properties available for a server object
   #http://msdn.microsoft.com/en-us/library/ms212724.aspx
   $server | Get-Member | Where MemberType -eq "Property"

   In PowerShell V2, you will need to slightly modify your syntax:

   $server | Get-Member | Where {$_.MemberType -eq "Property"}
   #The Information class lists...