Saturday, April 25, 2015

Counting the SQL Server Configuration properties using Windows PowerShell


You have created a SQL Server Instance default or a named instance and you want to find the configurtion properties. You can use Windows PowerShell to count the properties.
For the named instance Hodentek8\RegencyPark the following code will count the number of configuration properties:
$srv = new-object Microsoft.SqlServer.Management.Smo.Server 'Hodentek8\RegencyPark'
#Replace 'Hodentek8\RegencyPark' by ("(local)") for the default instance
$srv.name
$srv
$srv.Configuration.Properties.Count

Launch the Windows PowerShell ISE.
When you run this code in the Windows PowerShell ISE
You will get this response:
PS C:\Users\Jayaram> $srv = new-object Microsoft.SqlServer.Management.Smo.Server 'Hodentek8\RegencyPark' #("(local)")
$srv.name
$srv
$srv.Configuration.Properties.Count

----
Hodentek8\RegencyPark

AuditLevel                  : Failure
BackupDirectory             : C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\Backup
BrowserServiceAccount       : NT AUTHORITY\LOCALSERVICE
BrowserStartMode            : Auto
BuildClrVersionString       : v4.0.30319
BuildNumber                 : 2218
ClusterName                 :
ClusterQuorumState          : NotApplicable
ClusterQuorumType           : NotApplicable
Collation                   : SQL_Latin1_General_CP1_CI_AS
CollationID                 : 872468488
ComparisonStyle             : 196609
ComputerNamePhysicalNetBIOS : HODENTEK8
DefaultFile                 : C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA\
DefaultLog                  : C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA\
Edition                     : Developer Edition (64-bit)
ErrorLogPath                : C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\Log
FilestreamLevel             : TSqlFullFileSystemAccess
FilestreamShareName         : REGENCYPARK
HadrManagerStatus           : Failed
InstallDataDirectory        : C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL
InstallSharedDirectory      : c:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL
InstanceName                : REGENCYPARK
IsCaseSensitive             : False
IsClustered                 : False
IsFullTextInstalled         : True
IsHadrEnabled               : False
IsSingleUser                : False
IsXTPSupported              : False
Language                    : English (United States)
LoginMode                   : Integrated
MailProfile                 :
MasterDBLogPath             : C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA
MasterDBPath                : C:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL\DATA
MaxPrecision                : 38
NamedPipesEnabled           : False
NetName                     : HODENTEK8
NumberOfLogFiles            : -1
OSVersion                   : 6.2 (9200)
PerfMonMode                 : None
PhysicalMemory              : 8116
PhysicalMemoryUsageInKB     : 251848
Platform                    : NT x64
Processors                  : 8
ProcessorUsage              : 12
Product                     : Microsoft SQL Server
ProductLevel                : RTM
ResourceLastUpdateDateTime  : 6/12/2012 3:06:43 PM
ResourceVersionString       : 11.00.2218
RootDirectory               : c:\Program Files\Microsoft SQL Server\MSSQL11.REGENCYPARK\MSSQL
ServerType                  : Standalone
ServiceAccount              : .\Jayaram
ServiceInstanceId           : MSSQL11.REGENCYPARK
ServiceName                 : REGENCYPARK
ServiceStartMode            : Auto
SqlCharSet                  : 1
SqlCharSetName              : iso_1
SqlDomainGroup              : NT SERVICE\MSSQL$REGENCYPARK
SqlSortOrder                : 52
SqlSortOrderName            : nocase_iso
Status                      : Online
TapeLoadWaitTime            : -1
TcpEnabled                  : False
VersionMajor                : 11
VersionMinor                : 0
VersionString               : 11.0.2218.0
Name                        : Hodentek8\RegencyPark
Version                     : 11.0.2218
EngineEdition               : EnterpriseOrDeveloper
ResourceVersion             : 11.0.2218
BuildClrVersion             : 4.0.30319
DefaultTextMode             : True
Configuration               : Microsoft.SqlServer.Management.Smo.Configuration
AffinityInfo                : Microsoft.SqlServer.Management.Smo.AffinityInfo
ProxyAccount                : [Hodentek8\RegencyPark]
Mail                        : [Hodentek8\RegencyPark]
Databases                   : {AdventureWorks2012, master, model, msdb...}
Endpoints                   : {Dedicated Admin Connection, TSQL Default TCP, TSQL Default VIA, TSQL Local Machine...}
Languages                   : {Arabic, British, čeština, Dansk...}
SystemMessages              : {21, 21, 21, 21...}
UserDefinedMessages         : {}
Credentials                 : {}
CryptographicProviders      : {}
Logins                      : {##MS_PolicyEventProcessingLogin##, ##MS_PolicyTsqlExecutionLogin##, HODENTEK8\Jayaram, Hodentek8\TestUser...}
Roles                       : {bulkadmin, dbcreator, diskadmin, processadmin...}
LinkedServers               : {}
SystemDataTypes             : {bigint, binary, bit, char...}
JobServer                   : [Hodentek8\RegencyPark]
ResourceGovernor            : Microsoft.SqlServer.Management.Smo.ResourceGovernor
ServiceMasterKey            : Microsoft.SqlServer.Management.Smo.ServiceMasterKey
SmartAdmin                  : Microsoft.SqlServer.Management.Smo.SmartAdmin
Settings                    : Microsoft.SqlServer.Management.Smo.Settings
Information                 : Microsoft.SqlServer.Management.Smo.Information
UserOptions                 : Microsoft.SqlServer.Management.Smo.UserOptions
BackupDevices               : {}
FullTextService             : [Hodentek8\RegencyPark]
ActiveDirectory             :
Triggers                    : {}
Audits                      : {}
ServerAuditSpecifications   : {}
AvailabilityGroups          : {}
ConnectionContext           : Data Source=Hodentek8\RegencyPark;Integrated Security=True;MultipleActiveResultSets=False;Application Name="SQL Management"
Events                      : Microsoft.SqlServer.Management.Smo.ServerEvents
OleDbProviderSettings       :
Urn                         : Server[@Name='HODENTEK8\REGENCYPARK']
Properties                  : {Name=AuditLevel/Type=Microsoft.SqlServer.Management.Smo.AuditLevel/Writable=True/Value=Failure,
                              Name=BackupDirectory/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL
                              Server\MSSQL11.REGENCYPARK\MSSQL\Backup, Name=BuildNumber/Type=System.Int32/Writable=False/Value=2218,
                              Name=DefaultFile/Type=System.String/Writable=True/Value=C:\Program Files\Microsoft SQL
                              Server\MSSQL11.REGENCYPARK\MSSQL\DATA\...}
UserData                    :
State                       : Existing
IsDesignMode                : False
DomainName                  : SMO
DomainInstanceName          : HODENTEK8\REGENCYPARK
69