July 21, 2016 at 7:05 am
Is it possible to get list of all sql server instances from all the servers located remotely but on same network ?
only thing i know is the server names but each server has different named instances.
One way to do is remote RDP/login into the server and go to services.msc and check sql server instance name or open ssms.
but to do this on 100 servers will be tedious and wondering if there is command or script i can run from local machine which checks all the servers on network and collect the name of all sql server instances.
July 21, 2016 at 7:17 am
We're just coming to the end of a project at our place to move all the SQL Server instances onto a new dedicated infrastructure. For the initial investigation of what instances we actually had, we had our server team run lansweeper (or auditing tool of your choice - in my experience most places have one for licensing compliance) over the network looking for boxes running SQL Server.
You could use POSH, but first you'd need the list of servers you want to connect to in the first place. I do believe I may have a script kicking about that is close, looks for instances that are online - but should be tweakable - if the first option's not available to you for some reason
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 21, 2016 at 7:21 am
I just have list of all the server names
July 21, 2016 at 7:38 am
You could try [font="Courier New"]sqlcmd -L[/font]. It works better on some networks than others, though. Read the sqlcmd page in Books Online so that you understand the limitations.
John
July 21, 2016 at 7:49 am
John Mitchell-245523 (7/21/2016)
You could try [font="Courier New"]sqlcmd -L[/font]. It works better on some networks than others, though. Read the sqlcmd page in Books Online so that you understand the limitations.
I've used this and got good results in the past - the process was something like:
* create central list of servers
* from this central server, run a process that connects to each server in the list
* on each server, run SQLCMD -Lc and trap the output
* feed that output back into the central list, adding only the new items
* repeat.
On one site, I found a significant number of servers that the in-house DBA team didn't already know about.
The down side to this is it doesn't work if the instances are hidden. Or on a different subnet. Or you don't have access to them once you've found them. Or hidden behind some firewall or other.
Thomas Rushton
blog: https://thelonedba.wordpress.com
July 21, 2016 at 7:53 am
Quick suggestion
😎
/* -- ENABLE XP_CMDSHELL
EXEC SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE;
EXEC SP_CONFIGURE 'xp_cmdshell',1;
RECONFIGURE WITH OVERRIDE;
*/
DECLARE @services TABLE
(
SRV_TXT NVARCHAR(150) NULL
)
INSERT INTO @services
(
SRV_TXT
)
EXEC xp_cmdshell 'NET START';
SELECT
SRV.SRV_TXT
,REPLACE(REPLACE(SRV.SRV_TXT,N' SQL Server (',N''),N')',N'') AS INSTANCE_NAME
FROM @services SRV
WHERE SRV.SRV_TXT LIKE N' SQL Server (%';
July 21, 2016 at 7:54 am
before this post, i tried that (sqlcmd -L)
it gave me nothing.
attach is the result page from that command
July 26, 2016 at 4:08 am
If you've not got an auditing tool that can scan for you, try using this guy's powershell scripts. Have a look at his blog here, they may be what you're looking for.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
July 26, 2016 at 12:46 pm
SQLCMD -L needs the browser service, which isn't running most of the time.
The most success I've had in finding instances is when we have SCOM or OpsMgr or something that can query all hosts and check services. There is a pattern to services for named and default instances that you can check for.
July 26, 2016 at 1:04 pm
I've done in before in using the MAP tool, worked pretty well. This MS blog has some info on using it:
Sue
July 26, 2016 at 1:17 pm
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply