David
asked on
Find all SQL Servers in network/domain
Is there a way I can find out the names of the servers that are running any version of MS SQL server? I'd like to run a command which scans the domain for SQL servers, either using powershelll, VBS, AD support tools, whatever.
All servers are 2005 to 2008 R2. Forest functional level 2008 R2.
I have basic powershell experience and very limited VBS knowledge.
All servers are 2005 to 2008 R2. Forest functional level 2008 R2.
I have basic powershell experience and very limited VBS knowledge.
http://blog.sqlauthority.com/2009/03/18/sql-server-find-all-servers-from-local-network-using-sqlcmd/
Pinal Dave's script will find SQL Servers that are broadcasting/advertising their existence.
You can also query the directory for SQL instances which have been registered in AD with the SETSPN command.
"SETSPN -Q MSSQLSvc/*" checks the local domain. "SETSPN -F -Q MSSQLSvc/*" checks the whole forest.
Unfortunately you can't always count on all instances of SQL being registered. (Kerberos won't work without it, but plenty of folks don't worry about that.)
A third option would be to use Powershell:
1. Enumerate the computers you are concerned with, I assume Get-ADComputer will work for this purpose for you.
2. Use Get-Service to enumerate the services, and pull out entries for SQL.
You can also query the directory for SQL instances which have been registered in AD with the SETSPN command.
"SETSPN -Q MSSQLSvc/*" checks the local domain. "SETSPN -F -Q MSSQLSvc/*" checks the whole forest.
Unfortunately you can't always count on all instances of SQL being registered. (Kerberos won't work without it, but plenty of folks don't worry about that.)
A third option would be to use Powershell:
1. Enumerate the computers you are concerned with, I assume Get-ADComputer will work for this purpose for you.
2. Use Get-Service to enumerate the services, and pull out entries for SQL.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow that was great!!! Thanks!! Super easy!!