Find All SQL Servers in your Local Network

Leave a comment (2) Go to comments

Today, somebody asked me, how can I find what all are SQL Server Instances are running in my local network. I was shocked, I thought every DBA must know, what SQL servers are running in your network. This question was being asked by a sys admin guy, who wanted make an inventory list by finding all SQL Server in local network.

There are ways to accomplish this objective

  1. TSQL, which is very old way and doesn’t meets the requirement
  2. SQLCMD command line utility
  3. OSQL  command line utility
  4. Microsoft Assessment and Planning (MAP) Toolkit, best tool

The easiest way to accomplish this is to use sqlcmd or osql  built in utility with -L which will list down all the SQL Servers in your local network.

Find All SQL Servers in your Local Network What SQL instances are installed on my network SQL Server Network Utility sql server list sql server inventory sheet Script to Find SQL Server on Network Scanning the network for SQL Server Scan network for SQL Server instances Microsoft Assessment and Planning How do I find all the available SQL Servers on my network Finding SQL Servers running on a network Finding SQL Servers on the Network Find SQL Server Instances Across Your Network download Microsoft Assessment and Planning download map build sql server inventory list

Note : osql -L and SQLCMD -L will only return for those instance, where SQL Browser services are running. For example, if you have stopped "SQL Server Browser" services, where SQL services are running that you will not get a sure results, as this only captures the instance names, which are publish via SQL Server Browser.


In this scenario, the best option is to use is Microsoft Assessment and Planning Toolkit (MAP)

Microsoft Assessment and Planning (MAP) Toolkit
The Microsoft Assessment and Planning (MAP) Toolkit is a powerful inventory, assessment and reporting tool that can securely assess IT environments for various platform migrations and virtualization without the use of any software agents.

Specifically to our objective, this tool will help us to get a report in excel, which has following important columns

  • Computer Name
  • Instance Name
  • SQL Product Name
  • SQL Version Number
  • SQL Service Pack level (from the version number)
  • SQL Server Edition (Developer, Standard, Express, etc)
  • Clustered yes/no flag
  • Physical or Virtual Machine indicator
  • What sub-directory that instance is in (MSSQL.1/MSSQL.2/etc)
  • SQL Server State (Running/Stopped)
  • SQL Server Service start mode
  • O/S and Service Pack info
  • 64/32 Bit flag
  • How many processors/cores/logical processors
  • CPU brand/model number/speed/etc
  • Memory
  • Drive letters with total and current free space
  • And other useful information

To know more about this tool, you can search google using serach string "Microsoft Assessment and Planning (MAP) Toolkit". Here are some direct links to some important information.

Note : MAP has a prerequisites of

  • Microsoft Word 2007 or higher version
  • Microsoft Excel 2007 or higher version
  • .Net Framework
  • Administrative right on the server, where you installing
  • you can not run this tool on DC

Find All SQL Servers in your Local Network What SQL instances are installed on my network SQL Server Network Utility sql server list sql server inventory sheet Script to Find SQL Server on Network Scanning the network for SQL Server Scan network for SQL Server instances Microsoft Assessment and Planning How do I find all the available SQL Servers on my network Finding SQL Servers running on a network Finding SQL Servers on the Network Find SQL Server Instances Across Your Network download Microsoft Assessment and Planning download map build sql server inventory list

Keywords : Scanning the network for SQL Server,Script to Find SQL Server on Network,Finding SQL Servers running on a network,Finding SQL Servers on the Network,How do I find all the available SQL Servers on my network,SQL Server Network Utility,Find SQL Server Instances Across Your Network,What SQL instances are installed on my network,Scan network for SQL Server instances,What SQL Instances Are Installed On My Network,Microsoft Assessment and Planning, download Microsoft Assessment and Planning,download map, build sql server inventory list, sql server list, sql server inventory sheet

EOF - Find All SQL Servers in your Local Network, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

2 Comments.

  1. ——Getting List of SQl server instances from Network\LAN using SQL query

    – To allow advanced options to be changed.
    EXEC sp_configure ‘show advanced options’, 1
    GO
    – To update the currently configured value for advanced options.
    RECONFIGURE
    GO
    – To enable the feature.
    EXEC sp_configure ‘xp_cmdshell’, 1
    GO
    – To update the currently configured value for this feature.
    RECONFIGURE
    GO

    Declare @t table
    (
    ServerName Varchar(50)
    )
    insert into @t
    exec master..xp_cmdshelL ‘sqlcmd -L’

    select ltrim(rtrim(ServerName)),ServerName From @t where ServerName is not null and ServerName ”
    and ServerName ‘Servers:’

  2. Very Useful SQLCMD for DBa. Thanks for sharing.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.