Pages

Thursday, 9 February 2012

Listing SQL Server Instances

As a consultant I go into environments where the client simply does not know how many SQL Server instances they have. Perhaps as a DBA in a company you've been asked to do a license audit and need to know about all the SQL Server instances in your company. Or, as is common in New Zealand so probably elsewhere, you've just started at a company as their first DBA since they've grown to a size that requires one. The same company has probably allowed too many people to have too many rights and all kinds of SQL Server instances have sprung up all over the place.

Its a common situation. You can of course physically go to each machine in your company and check what is installed. But I'm a lazy DBA, or I try to be, and I want a solution that can let me have another coffee before lunch time.

If you do a Bingoogle search for something like "list sql server instances" you'll probably find these articles:
Both of which list this PowerShell command:

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

I did. And when I did all I could think was "Sweet! How easy is that!?"

Sadly not as easy as it looks.
I ran it and got this result:


I honestly cannot explain why that is - but in my "test" environment I also get the same and that's a pretty vanilla setup. But like anything in IT there is always another way. I basically thought "if I can get a list of services on each machine in the domain I'll be able to work out what instances are installed where".

The first half of that equation is easily achieved in PowerShell with the
Get-Service -ComputerName HostName
command. I just needed a list of computers in the domain to work over. Back to Bingoogle and once again "PowerShell Guy" to the rescue: http://blogs.technet.com/b/heyscriptingguy/archive/2006/11/09/how-can-i-use-windows-powershell-to-get-a-list-of-all-my-computers.aspx

I had a small tweak of the code to pump it into a list that can be saved to a file (computers.txt):
$strCategory = "computer"

$objDomain = New-Object System.DirectoryServices.DirectoryEntry

$objSearcher = New-Object System.DirectoryServices.DirectorySearcher
$objSearcher.SearchRoot = $objDomain
$objSearcher.Filter = ("(objectCategory=$strCategory)")

$colProplist = "name"
foreach ($i in $colPropList){
   $res = $objSearcher.PropertiesToLoad.Add($i)
}

$colResults = $objSearcher.FindAll()

$computers = foreach ($objResult in $colResults){
   $objComputer = $objResult.Properties; 
   $objComputer.name
}

$computers

$computers | Out-File "computers.txt"

Then take that file as the input to the Get-Service cmdlet (you could of course bypass the file...but having a computer list is handy for other tasks...so keep hold of it!):

Get-Content "computers.txt" | %{

    Get-Service -ComputerName $_ | ?{$_.Name -like "MSSQLServer" -or $_.Name -like "MSSQL$*"} | Add-Member -MemberType NoteProperty "HostName" -Value $_ -Passthru

} | select HostName, ServiceName, DisplayName, Status

Which spits out:

And there you have it. A complete list of SQL Server instances.

There are a couple of things to consider here though. Firstly you need the ability to query active directory. Secondly you need the ability to query the services on every computer. In larger environments a DBA typically will not be allowed to do that. You're going to have to go make friends with your domain administrators and either temporarily get some elevated privileges or have them run the code for you. And thirdly: if you've got servers in a DMZ type environment you may not be aware of those and/or be able to query the services list.

Also I haven't done too much formatting of the output, but it is possible to turn that into a HOSTNAME\INSTANCE_NAME format. And it would be handy to know the version of SQL Server being run. Which will make a terrific future blog post ;-)

For now - run the scripts and go have your coffee.

4 comments:

  1. Hello Kent, here is a VB.NET version of code to list SQL instances at http://www.kodyaz.com/articles/sql-server-instances-SqlDataSourceEnumerator.aspx

    ReplyDelete
    Replies
    1. Your VB.Net code calls the same function I detail at the top of this post and its the one I fail to have any success with. I still haven't had a chance to explore why this is.

      Delete
    2. Hi Kent, I also realized with the VB.NET code that the new enumerator class does not return always the same result. Due to network traffic load sometimes some of the servers are missed in the return set.

      Delete
    3. This is what I need to test. My suspicion is you need the Browser service running and firewall rules to allow access to the Browser service.

      You cant always guarantee that though.

      Delete