Updated Function: Get-SQLInstance

综合技术 2016-06-06

One function of mine that I use quite a bit to get information about SQL servers isGet-SQLInstance not only because it provides most of the information that I am looking for but it also is a great way to scan for all of the SQL servers on my network when combined with an Active Directory query to grab all of the servers on the domain.

This was a completely registry-centric tool that used different parts of the registry to gather a decent amount of information without having to actually connect to the SQL server itself and running some TSQL commands to learn more about the system. Now by looking at WMI, I can grab even more information while still not worrying about making a connection and running TSQL.

WMI, as you may or may not know, is an excellent repository of data that exists in Windows and lets you query for just about anything as long as you know where to look. Of course, using PowerShell means that exploring WMI is as simple as running some Get-WMIObject commands using either –List or (if you already know the class) connecting directly to the class using –Class and seeing what information is sitting there waiting to be discovered!

Some of the information that I need requires me to do a little digging around. I have to look at the RootMicrosoftSQLServer namespace and then taking that information to then look at and then after I find what I need, I can then dig a little deeper into the namespace to unearth more information about the SQL server.

    Computername = $Computer
    Query="SELECT name FROM __NAMESPACE WHERE name LIKE 'ComputerManagement%'"
    Authentication = 'PacketPrivacy'
    ErrorAction = 'Stop'

I already know what properties I am looking for, so I am going to build a hash table with empty values that I can then use later on for adding items to it. This way, if there are no values added, it will still have the property names.

$PropertyHash = [ordered]@{
    Computername = $Computer
    Instance = $Null
    SqlServer = $Null
    WmiNamespace = $Null
    SQLSTATES = $Null
    VERSION = $Null
    SPLEVEL = $Null
    CLUSTERED = $Null
    DATAPATH = $Null
    LANGUAGE = $Null
    VSNAME = $Null
    REGROOT = $Null
    SKU = $Null
    SKUNAME = $Null
    INSTANCEID = $Null
    DUMPDIR = $Null
    ISWOW64 = $Null
    BackupDirectory = $Null
    AlwaysOnName = $Null

Now we can start out with our query to first find out the namespace that I want to connect to and then to start looking at the SQL properties using a different filter. In this case SqlServiceType = 1 means that I am only looking for the SQL Server service (more info on that here ).

$Namespace = (Get-WMIObject @WMIParams).Name
$Filter = "SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType=1" 
$PropertyHash['WMINamespace'] = $Namespace
$WMIResults = Get-WMIObject @WMIParams

The results of this query provides us with a nice amount of information in both a string and number format.

From here it is a matter of adding each of these to their respected name in the hash table and then displaying the results at the end.

$WMIResults | ForEach {
    $Name = "{0}{1}" -f ($_.PropertyName.SubString(0,1),$_.PropertyName.SubString(1).ToLower())    
    $Data = If ($_.PropertyStrValue) {
    Else {
        If ($Name -match 'Clustered|ErrorReporting|SqmReporting|IsWow64') {
        Else {
    $PropertyHash[$Name] = $Data

The end result is some nice additions to my function that you can see below.

Get-SQLInstance –Computername vSCOM

Be sure to download the updated function below and if you want to help make this better, be sure to fork my repo and submit a pull request!

Download the updated Get-SQLInstance


Help contribute to Get-SQLInstance


责编内容by:Learn Powershell (源链)。感谢您的支持!


SQL Server Installation Best Practices By: Daniel Farina | | Related Tips:More > Install and Uninstall Problem You are a SQL Server Database Administrator (DBA) and you...
Is there a way to find the foreign key, ... When I'm trying to drop a constraint of primary key on a table, I'm getting the error ORA-02273: this unique/primary key is referenced by some forei...
Data warehouse integration to embrace data analyti... Posted byStanislav Andrenko | On June 27th, 2017 | In Business Intelligence , Customer Story ,Data Management | Tags:Data Warehouse, ETL , MS SQL ...
Hadoop Materialized Query Tables (MQTs) Boost Big ... Big SQL offers an array of performance features to help improve performance of queries in the Hadoop ecosystem. One of these features is the ability t...
使用 SQLSRV 連結 MS SQL Server 教學 本文將說明如何於 PHP 5.6(x64 TS) 上使用 SQLSRV 設定連接 MS SQL Server。 步驟一 請依據 PHP 版本 下載對應的 sqlsrv 版本 。 SQLSRV40 對應 PHP 7.0+ (Windows、Linux) SQLSRV32 ...