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
Related Articles:
- How can I determine installed SQL Server instances and their versions? -stackoverflow.com
- Find SQL Server Instances Across Your Network Using Windows PowerShell – Edwin Sarmiento (mssqltips.com)
- Windows PowerShell has many SQL Server related capabilities and features. Many of them are presented on this SQL Server PowerShell Tips page at mssqltips.com
- Getting Started in SMO (SQL Server Management Objects)
- How to Create a Visual C# SMO Project in Visual Studio .NET
- Installing SMO
- Note that SQL Server Management Objects (SMO) programming samples – in both Visual C# and Visual Basic.Net – are available in C:\Program Files\Microsoft SQL Server\120\Samples\Engine\Programmability\SMO directory.
- Microsoft SQL Server 2016 Feature Pack
- SQL Server Network Instance Detector – (Code Project)
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).
