(For more resources related to this topic, see here.)

Listing SQL Server instances

In this recipe, we will list all SQL Server instances in the local network.

Getting ready

Log in to the server that has your SQL Server development instance, as an administrator.

How to do it...

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Let's use the Start-Service cmdlet to start SQLBrowser:

   Import-Module SQLPS -DisableNameChecking
   #sql browser must be installed and running
   Start-Service "SQLBrowser"

3. Next, you need to create a ManagedComputer object to get access to instances. Type the following script and run it:

   $instanceName = "KERRIGAN"
   $managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.
   Wmi.ManagedComputer' $instanceName
   #list server instances
   $managedComputer.ServerInstances

   Your result should look similar to the one shown in the following screenshot:

   

   Note that $managedComputer.ServerInstances gives you not only instance names, but also additional properties such as ServerProtocols, Urn , State, and so on.

4. Confirm that these are the same instances you see in Management Studio . Open up Management Studio .

5. Go to Connect | Database Engine .

6. In the Server Name drop-down, click on Browse for More.

7. Select the Network Servers tab, and check the instances listed. Your screen should look similar to this:

   

    

How it works...

All services in a Windows operating system are exposed and accessible using Windows Management Instrumentation (WMI). WMI is Microsoft's framework for listing, setting, and configuring any Microsoft-related resource. This framework follows Web-based Enterprise Management (WBEM). Distributed Management Task Force, Inc. defines WBEM as follows (http://www.dmtf.org/standards/wbem):

a set of management and internet standard technologies developed to unify the management of distributed computing environments. WBEM provides the ability for the industry to deliver a well-integrated set of standard-based management tools, facilitating the exchange of data across otherwise disparate technologies and platforms.

In order to access SQL Server WMI-related objects, you can create a WMI ManagedComputer instance:

$managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.
ManagedComputer' $instanceName

The ManagedComputer object has access to a ServerInstance property, which in turn lists all available instances in the local network. These instances, however, are only identifiable if the SQL Server Browser service is running.

SQL Server Browser is a Windows service that can provide information on installed instances in a box. You need to start this service if you want to list the SQL Server-related services.

There's more...

An alternative to using the ManagedComputer object is using the System.Data.Sql. SQLSourceEnumerator class to list all the SQL Server instances in the local network, thus:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() |
Select ServerName, InstanceName, Version |
Format-Table  -AutoSize

When you execute this, your result should look similar to the following screenshot:

Yet another way to get a handle to the SQL Server WMI object is by using the Get-WmiObject cmdlet. This will not, however, expose exactly the same properties exposed by the Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer object.

To do this, you will need to discover first what namespace is available in your environment, thus:

$hostname = "KERRIGAN"
$namespace = Get-WMIObject -ComputerName $hostName -NameSpace root
MicrosoftSQLServer -Class "__NAMESPACE" |
Where Name -Like "ComputerManagement*"

If you are using PowerShell V2, you will have to change the Where cmdlet usage to use the curly braces ({}) and the $_ variable, thus: Where {$_.Name -Like "ComputerManagement*" }

For SQL Server 2012, this value is:

ROOTMicrosoftSQLServerComputerManagement11

Once you have the namespace, you can use this value with Get-WmiObject to retrieve the instances. One property we can use to filter is SqlServiceType.

According to MSDN (http://msdn.microsoft.com/en-us/library/ms179591.aspx)), the following are the values of SqlServiceType:

Thus, to retrieve the SQL Server instances, you need to filter for SQL Server service, or  SQLServiceType = 1.

Get-WmiObject -ComputerName $hostname `
-Namespace "$($namespace.__NAMESPACE)$($namespace.Name)" `
-Class SqlService |
Where SQLServiceType -eq 1 |
Select ServiceName, DisplayName, SQLServiceType |
Format-Table -AutoSize

If you are using PowerShell V2, you will have to change the Where cmdlet usage to use the curly braces ({}) and the $_ variable: Where {$_.SQLServiceType -Like –eq 1 }

Your result should look similar to the following screenshot:

Discovering SQL Server services

In this recipe, we enumerate all SQL Server services and list their status.

Getting ready

Check which SQL Server services are installed in your instance. Go to Start | Run and type services.msc. You should see a screen similar to this:

How to do it...

Let's assume you are running this script on the server box.

1. Open the PowerShell console by going to Start | Accessories | Windows PowerShell | Windows PowerShell ISE.

2. Add the following code and execute it:

   Import-Module SQLPS
   #replace KERRIGAN with your instance name
   $instanceName = "KERRIGAN"
   $managedComputer = New-Object 'Microsoft.SqlServer.Management.Smo.
   Wmi.ManagedComputer' $instanceName
   #list services
   $managedComputer.Services |
   Select Name, Type, Status, DisplayName |
   Format-Table -AutoSize

   Your result will look similar to the one shown in the following screenshot:

   

   Items listed on your screen will vary depending on the features installed and running in your instance.

3. Confirm that these are the services that exist in your server. Check your services window.

How it works...

Services that are installed on a system can be queried using WMI. Specific services for SQL Server are exposed through SMO's WMI ManagedComputer object. Some of the exposed properties include:

• ClientProtocols

• ConnectionSettings

• ServerAliases

• ServerInstances

• Services

There's more...

An alternative way to get SQL Server-related services is by using Get-WMIObject. We will need to pass in the hostname, as well as SQL Server WMI provider for the Computer Management namespace. For SQL Server 2012, this value is:

ROOTMicrosoftSQLServerComputerManagement11

The script to retrieve the services is provided in the following code. Note that we are dynamically composing the WMI namespace here.

$hostName = "KERRIGAN"
$namespace = Get-WMIObject -ComputerName $hostName -NameSpace root
MicrosoftSQLServer -Class "__NAMESPACE" |
Where Name -Like "ComputerManagement*"
Get-WmiObject -ComputerName $hostname -Namespace "$($namespace.__
NAMESPACE)$($namespace.Name)" -Class SqlService |
Select ServiceName

Yet another alternative but less accurate way of listing possible SQL Server-related services is the following snippet of code:

#alterative - but less accurate
Get-Service *SQL*

It uses the Get-Service cmdlet and filters based on the service name. It is less accurate because this cmdlet grabs all processes that have SQL in the name but may not necessarily be SQL Server-related. For example, if you have MySQL installed, that will get picked up as a process. Conversely, this cmdlet will not pick up SQL Server-related services that do not have SQL in the name, such as ReportServer.