The Search for SQL Servers

Writing a custom application for a specific company is often easier to do since we have access to their computers, networks, servers, and available resources.  If we’re writing an app that can be used by anyone, we need a method to determine what servers are available and where they are located.

For example, Idera offers a variety of useful database management and monitoring tools.  Each tool requires a means or method of determining the current databases instances.  One of the many helpful free tools offered by Idera is a stand alone “SQL Instance Check” app.

SQL Server Instances

We can discover SQL Server Instances using Windows PowerShell (version 2.0) and the following command:

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

Unfortunately, this method has it’s downfalls. According to Microsoft’s article Enumerating Instances of SQL Server (ADO.NET), all servers may or may not be listed due to factors such as network traffic and timeouts.

A number of responses to the question “How can I determine installed SQL Server instances and their versions?” on stackoverflow include:

Get-Service | ?{ $_.Name -like "MSSQL*" }

From the command prompt or in Windows PowerShell we can enter the command: SQLCMD -L

One reply to this question suggests running Services.msc and looking for all the entries with SQL Server. This utility will return the name, description, and status of ALL of the local services on your machine. Although it’s not quite the neat utility I was looking for, it’s interesting to note all of the different services that are running my machine.

ManagedComputer.ServerInstances Property:  Represents a collection of ServerInstance objects.  Each ServerInstance object represents an instance of SQL Server defined on the WMI installation.

Namespace:  Microsoft.SqlServer.Management.Smo.Wmi

You must register the Microsoft.SqlServer.SqlWmiManagment.dll by using regasm.exe before you can use the Microsoft.SqlServer.Management.Smo.Wmi classes.

I have yet to find the ideal solution to finding all possible instances of a running server, however, I have learned that many have tried with varying degrees of success.

Until Next Time – Keep the “C” in Coding

versalytics-logo

Related Articles:

Reference Terms:

  • .NET Framework
  • ADO.NET 2.0 – System.Data.Sql, SqlDataSourceEnumerator, GetDataSources()
  • WMI – Windows Management Instrumentation
  • SMO – SQL Server Management Objects
  • ADC:  Active Directory Computer
  • DMO:  Distributed Management Objects (superseded and extended by SMO).

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.