Summary<\/b>: Microsoft Scripting Guy, Ed Wilson, talks about using Windows PowerShell to examine the SQL Server object.\nMicrosoft 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.<\/p>\n
Note <\/b>Yesterday I introduced the SQL Server provider for Windows PowerShell. You should definitely read Introduction to the SQL Server 2012 PowerShell Provider<\/a> <\/i>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.<\/p>\n Note <\/b>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.\nWhen 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.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1><\/p>\n Now, when I use the dir<\/b> <\/i>alias, I can see both instances appear as shown here.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> dir<\/p>\n Instance Name<\/p>\n ————-<\/p>\n INSTANCE_A<\/p>\n INSTANCE_B\nTo connect to one of the specific instances, I use the Get-Item<\/b> cmdlet. Whereas this name may seem a bit strange, keep in mind that the command behind the alias dir<\/b> <\/i>is in fact Get-ChildItem<\/b>, so the naming convention actually makes sense. I retrieve Instance_A and store it in a variable.\nNote <\/b>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.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance = Get-Item Instance_A<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance<\/p>\n Instance Name<\/p>\n ————-<\/p>\n INSTANCE_A\nTo see the type of object that is stored in the $Instance<\/b> variable, I use the GetType <\/b>method. I also pipe the results to the Format-Table<\/b> cmdlet so I can look at the name and the BaseType<\/b> of the object. This command is shown here.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.GetType() | ft name, basetype -a<\/p>\n <\/p>\n Name BaseType<\/p>\n —- ——–<\/p>\n Server Microsoft.SqlServer.Management.Smo.SqlSmoObject\nThe specific SqlSmoObject is a Server object<\/a>, and it contains a number of very useful properties that describe Instance_A. These properties are shown here.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance | Get-Member -membertype property | fw -a<\/p>\n <\/p>\n ActiveDirectory AffinityInfo AuditLevel<\/p>\n Audits AvailabilityGroups BackupDevices<\/p>\n BackupDirectory BrowserServiceAccount BrowserStartMode<\/p>\n BuildClrVersion BuildClrVersionString BuildNumber<\/p>\n ClusterName ClusterQuorumState ClusterQuorumType<\/p>\n Collation CollationID ComparisonStyle<\/p>\n ComputerNamePhysicalNetBIOS Configuration ConnectionContext<\/p>\n Credentials CryptographicProviders Databases<\/p>\n DefaultFile DefaultLog DefaultTextMode<\/p>\n DomainInstanceName DomainName Edition<\/p>\n Endpoints EngineEdition ErrorLogPath<\/p>\n Events FilestreamLevel FilestreamShareName<\/p>\n FullTextService HadrManagerStatus Information<\/p>\n InstallDataDirectory InstallSharedDirectory InstanceName<\/p>\n IsCaseSensitive IsClustered IsDesignMode<\/p>\n IsFullTextInstalled IsHadrEnabled IsSingleUser<\/p>\n JobServer Language Languages<\/p>\n LinkedServers LoginMode Logins<\/p>\n Mail MailProfile MasterDBLogPath<\/p>\n MasterDBPath MaxPrecision Name<\/p>\n NamedPipesEnabled NetName NumberOfLogFiles<\/p>\n OleDbProviderSettings OSVersion PerfMonMode<\/p>\n PhysicalMemory PhysicalMemoryUsageInKB Platform<\/p>\n Processors ProcessorUsage Product<\/p>\n ProductLevel Properties ProxyAccount<\/p>\n ResourceGovernor ResourceLastUpdateDateTime ResourceVersion<\/p>\n ResourceVersionString Roles RootDirectory<\/p>\n ServerAuditSpecifications ServerType ServiceAccount<\/p>\n ServiceInstanceId ServiceMasterKey ServiceName<\/p>\n ServiceStartMode Settings SqlCharSet<\/p>\n SqlCharSetName SqlDomainGroup SqlSortOrder<\/p>\n SqlSortOrderName State Status<\/p>\n SystemDataTypes SystemMessages TapeLoadWaitTime<\/p>\n TcpEnabled Triggers Urn<\/p>\n UserData UserDefinedMessages UserOptions<\/p>\n Version VersionMajor VersionMinor<\/p>\n VersionString\nThere are actually four properties that are related to the version of SQL Server installed on the instance. The properties are Version<\/b>, VersionMajor<\/b>, VersionMinor<\/b>, and VersionString<\/b>. The best way to understand these properties is to see them in action. The following script shows the properties.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.version<\/p>\n <\/p>\n Major Minor Build Revision<\/p>\n —– —– —– ——–<\/p>\n 11 0 2100 -1<\/p>\n <\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.versionmajor<\/p>\n 11<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.versionminor<\/p>\n 0<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.versionstring<\/p>\n 11.0.2100.60\nRemember, this is Windows PowerShell, and therefore wildcard characters work. So, an easier way to examine the different version properties is to pipe the $instance<\/b> variable and use Format-List<\/b> to show the version properties. This is shown here.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance | fl version*<\/p>\n VersionMajor : 11<\/p>\n VersionMinor : 0<\/p>\n VersionString : 11.0.2100.60<\/p>\n Version : 11.0.2100\nI 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.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.Edition<\/p>\n Enterprise Edition (64-bit)<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.EngineEdition<\/p>\n EnterpriseOrDeveloper<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.logins<\/p>\n <\/p>\n Name Login Type Created<\/p>\n —- ———- ——-<\/p>\n ##MS_PolicyEventProcessingLogin## SqlLogin 2\/10\/2012 9:07 PM<\/p>\n ##MS_PolicyTsqlExecutionLogin## SqlLogin 2\/10\/2012 9:07 PM<\/p>\n NT AUTHORITYSYSTEM WindowsUser 10\/1\/2012 9:47 AM<\/p>\n NT ServiceMSSQL$INSTANCE_A WindowsUser 10\/1\/2012 9:47 AM<\/p>\n NT SERVICEReportServer$INSTANCE_A WindowsUser 10\/1\/2012 9:47 AM<\/p>\n NT SERVICESQLAgent$INSTANCE_A WindowsUser 10\/1\/2012 9:47 AM<\/p>\n NT SERVICESQLWriter WindowsUser 10\/1\/2012 9:47 AM<\/p>\n NT SERVICEWinmgmt WindowsUser 10\/1\/2012 9:47 AM<\/p>\n NWTRADERSadministrator WindowsUser 10\/1\/2012 9:47 AM<\/p>\n sa SqlLogin 4\/8\/2003 9:10 AM\nI 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.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.ServiceAccount<\/p>\n NT ServiceMSSQL$INSTANCE_A<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.ServiceStartMode<\/p>\n Auto<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.Servicename<\/p>\n INSTANCE_A\nI can use this same technique to see all the directory locations for my instance of SQL Server 2012 as shown here.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance | fl *dir*<\/p>\n <\/p>\n BackupDirectory : C:Program FilesMicrosoft SQL<\/p>\n ServerMSSQL11.INSTANCE_AMSSQLBackup<\/p>\n InstallDataDirectory : C:Program FilesMicrosoft SQL<\/p>\n ServerMSSQL11.INSTANCE_AMSSQL<\/p>\n InstallSharedDirectory : C:Program FilesMicrosoft SQL<\/p>\n ServerMSSQL11.INSTANCE_AMSSQL<\/p>\n RootDirectory : C:Program FilesMicrosoft SQL<\/p>\n ServerMSSQL11.INSTANCE_AMSSQL<\/p>\n ActiveDirectory :<\/p>\n There are also a number of methods that are exposed by the Server object.<\/a><\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance | gm -mem method | fw -a<\/p>\n <\/p>\n Alter AttachDatabase<\/p>\n CompareUrn DeleteBackupHistory<\/p>\n Deny DesignModeInitialize<\/p>\n DetachDatabase DetachedDatabaseInfo<\/p>\n Discover EnumActiveCurrentSessionTraceFlags<\/p>\n EnumActiveGlobalTraceFlags EnumAvailableMedia<\/p>\n EnumClusterMembersState EnumClusterSubnets<\/p>\n EnumCollations EnumDatabaseMirrorWitnessRoles<\/p>\n EnumDetachedDatabaseFiles EnumDetachedLogFiles<\/p>\n EnumDirectories EnumErrorLogs<\/p>\n EnumLocks EnumMembers<\/p>\n EnumObjectPermissions EnumPerformanceCounters<\/p>\n EnumProcesses EnumServerAttributes<\/p>\n EnumServerPermissions EnumStartupProcedures<\/p>\n EnumWindowsDomainGroups EnumWindowsGroupInfo<\/p>\n EnumWindowsUserInfo Equals<\/p>\n GetActiveDBConnectionCount GetConnection<\/p>\n GetDefaultInitFields GetDomainRoot<\/p>\n GetHashCode GetLogicalVersion<\/p>\n GetParent GetPropertyNames<\/p>\n GetPropertySet GetPropertyType<\/p>\n GetPropertyValue GetSmoObject<\/p>\n GetStringComparer GetType<\/p>\n GetUrn Grant<\/p>\n Initialize IsDetachedPrimaryFile<\/p>\n IsWindowsGroupMember JoinAvailabilityGroup<\/p>\n KillAllProcesses KillDatabase<\/p>\n KillProcess PingSqlServerVersion<\/p>\n ReadErrorLog Refresh<\/p>\n Resolve Revoke<\/p>\n Script SetConnection<\/p>\n SetDefaultInitFields SetObjectState<\/p>\n SetPropertyValue SetTraceFlag<\/p>\n SfcHelper_GetDataTable SfcHelper_GetSmoObject<\/p>\n SfcHelper_GetSmoObjectQuery ToString<\/p>\n Validate\nTo call the methods, I need to know the requirements for the method. I can find some of the information via Get-Member<\/b>, but other information will need to come from TechNet in the description of the methods of the Server object. To call the GetDirectories<\/b> method, I need to supply the directory for the installation of SQL Server as shown here.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.EnumDirectories(“C:Program FilesMicrosoft SQL Server”)<\/p>\n Name<\/p>\n —-<\/p>\n 100<\/p>\n 110<\/p>\n 80<\/p>\n 90<\/p>\n MSAS11.INSTANCE_A<\/p>\n MSAS11.INSTANCE_B<\/p>\n MSRS11.INSTANCE_A<\/p>\n MSRS11.INSTANCE_B<\/p>\n MSSQL11.INSTANCE_A<\/p>\n MSSQL11.INSTANCE_B\nI 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.<\/p>\n [sql1]: PS SQLSERVER:sqlsql1> $instance.EnumDirectories($instance.RootDirectory)<\/p>\n Name<\/p>\n —-<\/p>\n Backup<\/p>\n Binn<\/p>\n DATA<\/p>\n FTData<\/p>\n Install<\/p>\n JOBS<\/p>\n Log<\/p>\n repldata\nJoin me tomorrow when I will talk about more cool Windows PowerShell and SQL Server stuff as I move to the database node.\nI invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.\nEd Wilson, Microsoft Scripting Guy<\/b> <\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,3,176,375,45],"class_list":["post-4802","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-scripting-guy","tag-sql-server","tag-sql-server-2012","tag-windows-powershell"],"acf":[],"blog_post_summary":" 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/4802","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=4802"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/4802\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=4802"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=4802"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=4802"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Examining the SQL Server node<\/h2>\n
Server object methods<\/h2>\n