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
- TSQL, which is very old way and doesn’t meets the requirement
- SQLCMD command line utility
- OSQL command line utility
- 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.
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.
- Get Started with the MAP Toolkit
- Read the Overview of MAP
- Review the Scenarios using MAP: SQL Server 2008 R2
- Download the Free MAP Toolkit
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
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
——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:’
Very Useful SQLCMD for DBa. Thanks for sharing.