By: Edwin Sarmiento | Updated: 2010-05-20 | Comments (14) | Related: > PowerShell
Problem
As more and more SQL Server instances get installed in my organization by different departments in both servers and desktops, I need to find out where they are as I'll be responsible for managing and administering these instances. How do I list out all the SQL Server instances across my entire organization without even knowing where they are?
Solution
You may have probably heard about the term SQL Server sprawl - the uncontrolled and uncoordinated deployment of different SQL Server instances of different editions. It's easy to track Enterprise and Standard Edition instances since we regularly do audits for licensing purposes. But, then, there are third-party and line-of-business applications that use SQL Server Express as their backend database that get installed by people from different departments of your organization. Who knows whether these SQL Server instances sit on their desktops or on servers allocated by the IT department? And the worst part is, we DBAs get charged for administering them.
You may have used tools like SQL Ping and a bunch of other stuff in the past, some of which are highlighted in this SQL Server Central article, to discover SQL Server instances in your network. The good news is, your nightmare is about to end. Since Windows PowerShell runs on top of the .NET Framework, it can leverage any .NET Framework-based assemblies available on the machine that it is running on top of (this includes having SQL Server 2005 Client Tools with Server Management Object (SMO) to administer SQL Server instances). For this particular case, you don't even need to have SMO installed on your client machine to enumerate SQL Server instances across your organization as the .NET Framework already includes ADO.NET 2.0. ADO.NET contains the System.Data.Sql namespace which contains classes that support SQL Server-specific functionality. One of these classes is the SqlDataSourceEnumerator which can be used for enumerating all available instances of SQL Server within your local network, even if the SQL Server service is stopped. The SqlDataSourceEnumerator class has the Instance property that gets an instance of the SqlDataSourceEnumerator, which can then be used to retrieve information about available SQL Server instances. A method called GetDataSources retrieves a DataTable containing information about all visible SQL Server instances in the network from SQL Server 2000 onwards.
And this really highlights the power and simplicity of Windows PowerShell. This task can be done in a single line of code, using the information mentioned above.
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
See, that wasn't so hard, was it? The default output of the script will display the four major properties - ServerName, Instancename, IsClustered and Version. You can have an instant documentation by piping the results in a text file for later review. To know more about the different properties and methods available to you from the object returned by the script, you can use the Get-Member cmdlet.
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Get-Member
You'll see that in my test environment, I have a Windows 7 machine running two SQL Server 2005 instances - one is a default instance while the other one is a SQL Server Express instance - and a SQL Server 2008 named instance. You'll also see a Windows Server 2003 with two SQL Server 2005 instances and, looking at the Version property, one is not patched at all. While the Windows 7 machine is a member of an Active Directory domain, it is interesting to note that the Windows Server 2003 machine is not. This means that even machines in a Workgroup configuration can be discovered for SQL Server instances even if the account that you are using to run the script does not have any permissions at all on the remote machine.
Caveats?
I may have overstated the fact that this is really very easy to do. However, the reason that it appears to be very easy is because of two things that I have done. First, the SQL Browser service is started. The SQL Browser service is the one making it possible for the discovery of SQL Server instances on a computer. The challenge here is that the SQL Browser service is not started by default. So, how do we start it? And how do we even know that it exists on a specific machine? There are a couple of ways to do that. One of which is by using the Invoke-Command cmdlet available in Windows PowerShell v2.0. This cmdlet can be used to run commands on local or remote computer. The assumption here is that the target computer also has Windows PowerShell v2.0 installed and that Windows PowerShell Remoting is enabled on all the target computers. As this is beyond the scope of work for the DBA, I will not cover PowerShell Remoting in this tip.
Another option is by using the Get-Service cmdlet which can assist us in this task. You can run the Get-Service cmdlet to query for the SQL Browser service on a computer and, if it is not started, force it to start using the Start-Service cmdlet. Both of these cmdlets do not require Windows PowerShell on the remote computer
Get-Service | Where-Object {$_.Name -eq "SQLBrowser"} | Start-Service
Since you won't be dealing with just a handful of computers, it's best to work with your domain administrators to write a script to enumerate servers and workstations in your Active Directory domain for this purpose and pipe the results to the script (it's one of those reasons why you should be in good terms with your systems administrators). If you are running Windows Server 2008 R2 in your environment, the Active Directory PowerShell cmdlets can make this task even simpler.
The second reason why this task seem so easy is that the Windows Firewall has been configured to allow the SQL Browser service to accept inbound connections. Starting with Windows XP Service Pack 3 and Windows Server 2003 Service Pack 1, Windows administrators have had to deal with the Windows Firewall to allow services to function as expected in the local network. If you are simply running this script once every month, you can ask your domain administrators to disable the Windows Firewall via Active Directory Group Policy (and yet another reason why you should be in good terms with your domain administrators). A screenshot of this Group Policy setting in Active Directory is shown below, making it easier to disable the Windows Firewall on all the computers in your organization.
Next Steps
- Regularly audit your network environment for SQL Server sprawl using this script
- Check out the other Windows PowerShell with SQL Server tips
About the author
Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-05-20
