Spelunking for SQL Server Using PowerShell

In a new environment I find I waste a lot of time trying to figure out what everybody already knows. For example, what SQL Server instances are running on a given machine? Is Reporting Services running on this server?

Here’s a handy little script you can paste into a PowerShell window and see what’s on any computer. With a little tinkering you can get it to work on a list of servers — but that’s for another day. Here’s the script:

get-service -computername DWHSQL1`

-displayname “*sql*” `

sort-object -property DisplayName `

format-table -auto

And the results:

Status Name DisplayName
Stopped TSSQLPublisher$Default DM TS SQL Publisher [Default]
Stopped TSSQLListener$Default DM TS SQL TCP/IP Listener Service [Default]
Stopped MSSQL$DWHSQL2 SQL Server (DWHSQL2)
Running MSSQLSERVER SQL Server (MSSQLSERVER)
Stopped MSSQLServerADHelper SQL Server Active Directory Helper
Stopped SQLAgent$DWHSQL2 SQL Server Agent (DWHSQL2)
Running SQLSERVERAGENT SQL Server Agent (MSSQLSERVER)
Running SQLBrowser SQL Server Browser
Stopped msftesql$DWHSQL2 SQL Server FullText Search (DWHSQL2)

As you can see, there are two instances on the server but only one is running, the default instance, DWHSQL1.

If you need to look at another server simply change the computername. You can modify the filtered column (displayname) to suit your needs as well.

You could get the same information by remoting in to the box and looking at services, but this is quicker and easier, especially when you are dealing with several servers.

Advertisements

2 thoughts on “Spelunking for SQL Server Using PowerShell

  1. Check out this tip to search your network for SQL Server instances. It’s a one-liner in PowerShell
    http://mssqltips.com/tip.asp?tip=2013

    This, however, requires that the SQL Browser service be enabled

    Since I don’t rely much on the SQL Browser service, I use this PowerShell script

    Get-Content serverList.txt | % {Get-WmiObject Win32_Service -Filter “Name=’MSSQLSERVER'” -computer $_ | Select __SERVER,DisplayName, State}

    This will read the serverList.txt file that lists the names of all the servers you have on your network, use the Get-WmiObject cmdlet to query the Win32_Service on all remote computers and display the server host name, service display name and status of the service. With Windows PowerShell, you can even use wildcards especially if you have named instances

    Have fun!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s