Summary: Microsoft Scripting Guy, Ed Wilson, talks about using Windows PowerShell to examine the SQL Server object.

Microsoft Scripting Guy, Ed Wilson, is here. Today I want to continue exploring the SQL Server provider for Windows PowerShell. We will dive into the named instance node of SQL Server.

Note   Yesterday I introduced the SQL Server provider for Windows PowerShell. You should definitely read Introduction to the SQL Server 2012 PowerShell Provider prior to reading today’s blog. It provides essential background information, including the installation of the optional features that are required to use the provider.

Examining the SQL Server node

Note   I am using Windows PowerShell remoting to enter a remote Windows PowerShell session on my computer running SQL Server. I am doing this from my desktop. Therefore, my Windows PowerShell prompt reflects the name of the remote machine.

When I connect to the specific server that is using the SQL Server 2012 provider for Windows PowerShell, the prompt changes to reflect the server name. In my case, it is SQL1. This is shown here.

[sql1]: PS SQLSERVER:\sql\sql1>

Now, when I use the dir alias, I can see both instances appear as shown here.

[sql1]: PS SQLSERVER:\sql\sql1> dir

Instance Name

-------------

INSTANCE_A

INSTANCE_B

To connect to one of the specific instances, I use the Get-Item cmdlet. Whereas this name may seem a bit strange, keep in mind that the command behind the alias dir is in fact Get-ChildItem, so the naming convention actually makes sense. I retrieve Instance_A and store it in a variable.

Note   Whereas tab expansion works, it works REALLY slowly when working with SQL Server 2012 provider for Windows PowerShell. Therefore, it is probably best to avoid hitting the Tab key.

[sql1]: PS SQLSERVER:\sql\sql1> $instance = Get-Item Instance_A

[sql1]: PS SQLSERVER:\sql\sql1> $instance

Instance Name

-------------

INSTANCE_A

To see the type of object that is stored in the $Instance variable, I use the GetType method. I also pipe the results to the Format-Table cmdlet so I can look at the name and the BaseType of the object. This command is shown here.

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.GetType() | ft name, basetype -a

 

Name   BaseType

----   --------

Server Microsoft.SqlServer.Management.Smo.SqlSmoObject

The specific SqlSmoObject is a Server object, and it contains a number of very useful properties that describe Instance_A. These properties are shown here.

[sql1]: PS SQLSERVER:\sql\sql1\> $instance | Get-Member -membertype property | fw -a

 

ActiveDirectory              AffinityInfo                AuditLevel

Audits                       AvailabilityGroups          BackupDevices

BackupDirectory              BrowserServiceAccount       BrowserStartMode

BuildClrVersion              BuildClrVersionString       BuildNumber

ClusterName                  ClusterQuorumState          ClusterQuorumType

Collation                    CollationID                 ComparisonStyle

ComputerNamePhysicalNetBIOS  Configuration               ConnectionContext

Credentials                  CryptographicProviders      Databases

DefaultFile                  DefaultLog                  DefaultTextMode

DomainInstanceName           DomainName                  Edition

Endpoints                    EngineEdition               ErrorLogPath

Events                       FilestreamLevel             FilestreamShareName

FullTextService              HadrManagerStatus           Information

InstallDataDirectory         InstallSharedDirectory      InstanceName

IsCaseSensitive              IsClustered                 IsDesignMode

IsFullTextInstalled          IsHadrEnabled               IsSingleUser

JobServer                    Language                    Languages

LinkedServers                LoginMode                   Logins

Mail                         MailProfile                 MasterDBLogPath

MasterDBPath                 MaxPrecision                Name

NamedPipesEnabled            NetName                     NumberOfLogFiles

OleDbProviderSettings        OSVersion                   PerfMonMode

PhysicalMemory               PhysicalMemoryUsageInKB     Platform

Processors                   ProcessorUsage              Product

ProductLevel                 Properties                  ProxyAccount

ResourceGovernor             ResourceLastUpdateDateTime  ResourceVersion

ResourceVersionString        Roles                       RootDirectory

ServerAuditSpecifications    ServerType                  ServiceAccount

ServiceInstanceId            ServiceMasterKey            ServiceName

ServiceStartMode             Settings                    SqlCharSet

SqlCharSetName               SqlDomainGroup              SqlSortOrder

SqlSortOrderName             State                       Status

SystemDataTypes              SystemMessages              TapeLoadWaitTime

TcpEnabled                   Triggers                    Urn

UserData                     UserDefinedMessages         UserOptions

Version                      VersionMajor                VersionMinor

VersionString

There are actually four properties that are related to the version of SQL Server installed on the instance. The properties are Version, VersionMajor, VersionMinor, and VersionString. The best way to understand these properties is to see them in action. The following script shows the properties.

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.version

 

Major  Minor  Build  Revision

-----  -----  -----  --------

11     0      2100   -1

 

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.versionmajor

11

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.versionminor

0

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.versionstring

11.0.2100.60

Remember, this is Windows PowerShell, and therefore wildcard characters work. So, an easier way to examine the different version properties is to pipe the $instance variable and use Format-List to show the version properties. This is shown here.

[sql1]: PS SQLSERVER:\sql\sql1\> $instance | fl version*

VersionMajor  : 11

VersionMinor  : 0

VersionString : 11.0.2100.60

Version       : 11.0.2100

I can also easily find information about the edition of SQL Server 2012 that is running, in addition to the login accounts that are set up. This is shown here.

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.Edition

Enterprise Edition (64-bit)

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.EngineEdition

EnterpriseOrDeveloper

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.logins

 

Name                                          Login Type    Created

----                                          ----------    -------

##MS_PolicyEventProcessingLogin##             SqlLogin      2/10/2012 9:07 PM

##MS_PolicyTsqlExecutionLogin##               SqlLogin      2/10/2012 9:07 PM

NT AUTHORITY\SYSTEM                           WindowsUser   10/1/2012 9:47 AM

NT Service\MSSQL$INSTANCE_A                   WindowsUser   10/1/2012 9:47 AM

NT SERVICE\ReportServer$INSTANCE_A            WindowsUser   10/1/2012 9:47 AM

NT SERVICE\SQLAgent$INSTANCE_A                WindowsUser   10/1/2012 9:47 AM

NT SERVICE\SQLWriter                          WindowsUser   10/1/2012 9:47 AM

NT SERVICE\Winmgmt                            WindowsUser   10/1/2012 9:47 AM

NWTRADERS\administrator                       WindowsUser   10/1/2012 9:47 AM

sa                                            SqlLogin      4/8/2003 9:10 AM

I might also be interested in account information, such as the service account, the service start mode, and the name of the service. This information is shown here.

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.ServiceAccount

NT Service\MSSQL$INSTANCE_A

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.ServiceStartMode

Auto

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.Servicename

INSTANCE_A

I can use this same technique to see all the directory locations for my instance of SQL Server 2012 as shown here.

[sql1]: PS SQLSERVER:\sql\sql1> $instance | fl *dir*

 

BackupDirectory        : C:\Program Files\Microsoft SQL

                         Server\MSSQL11.INSTANCE_A\MSSQL\Backup

InstallDataDirectory   : C:\Program Files\Microsoft SQL

                         Server\MSSQL11.INSTANCE_A\MSSQL

InstallSharedDirectory : C:\Program Files\Microsoft SQL

                         Server\MSSQL11.INSTANCE_A\MSSQL

RootDirectory          : C:\Program Files\Microsoft SQL

                         Server\MSSQL11.INSTANCE_A\MSSQL

ActiveDirectory        :

Server object methods

There are also a number of methods that are exposed by the Server object.

[sql1]: PS SQLSERVER:\sql\sql1\> $instance | gm -mem method | fw -a

 

Alter                                      AttachDatabase

CompareUrn                                 DeleteBackupHistory

Deny                                       DesignModeInitialize

DetachDatabase                             DetachedDatabaseInfo

Discover                                   EnumActiveCurrentSessionTraceFlags

EnumActiveGlobalTraceFlags                 EnumAvailableMedia

EnumClusterMembersState                    EnumClusterSubnets

EnumCollations                             EnumDatabaseMirrorWitnessRoles

EnumDetachedDatabaseFiles                  EnumDetachedLogFiles

EnumDirectories                            EnumErrorLogs

EnumLocks                                  EnumMembers

EnumObjectPermissions                      EnumPerformanceCounters

EnumProcesses                              EnumServerAttributes

EnumServerPermissions                      EnumStartupProcedures

EnumWindowsDomainGroups                    EnumWindowsGroupInfo

EnumWindowsUserInfo                        Equals

GetActiveDBConnectionCount                 GetConnection

GetDefaultInitFields                       GetDomainRoot

GetHashCode                                GetLogicalVersion

GetParent                                  GetPropertyNames

GetPropertySet                             GetPropertyType

GetPropertyValue                           GetSmoObject

GetStringComparer                          GetType

GetUrn                                     Grant

Initialize                                 IsDetachedPrimaryFile

IsWindowsGroupMember                       JoinAvailabilityGroup

KillAllProcesses                           KillDatabase

KillProcess                                PingSqlServerVersion

ReadErrorLog                               Refresh

Resolve                                    Revoke

Script                                     SetConnection

SetDefaultInitFields                       SetObjectState

SetPropertyValue                           SetTraceFlag

SfcHelper_GetDataTable                     SfcHelper_GetSmoObject

SfcHelper_GetSmoObjectQuery                ToString

Validate

To call the methods, I need to know the requirements for the method. I can find some of the information via Get-Member, but other information will need to come from TechNet in the description of the methods of the Server object. To call the GetDirectories method, I need to supply the directory for the installation of SQL Server as shown here.

[sql1]: PS SQLSERVER:\sql\sql1\> $instance.EnumDirectories("C:\Program Files\Microsoft SQL Server")

Name

----

100

110

80

90

MSAS11.INSTANCE_A

MSAS11.INSTANCE_B

MSRS11.INSTANCE_A

MSRS11.INSTANCE_B

MSSQL11.INSTANCE_A

MSSQL11.INSTANCE_B

I do not have to type a path—I can use one of the properties from the Server object. In this example, I enumerate the directories beneath the root of the SQL Server instance.

[sql1]: PS SQLSERVER:\sql\sql1> $instance.EnumDirectories($instance.RootDirectory)

Name

----

Backup

Binn

DATA

FTData

Install

JOBS

Log

repldata

Join me tomorrow when I will talk about more cool Windows PowerShell and SQL Server stuff as I move to the database node.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy