August 3, 2010 at 7:03 am
Hi There,
Does anyone know how to list SQL Servers in multiple domains?
I have a query which lists all servers on a domain, though I would like to list the servers on more than 1 domain.
My code is as follows:
BEGIN
SET NOCOUNT ON
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master.dbo.xp_cmdshell 'OSQL -L'
-- play with ISQL -L too, results differ slightly
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
END
Any ideas?
August 3, 2010 at 7:20 am
That query isn't reliable for all servers on one domain. Per Books Online:
"-L Lists the locally configured servers and the names of the servers broadcasting on the network."
If a server isn't broadcasting or locally configured, it won't find it. You can easily disable broadcasting via SQL Browser. And broadcasting also is not in reference to a domain.
August 3, 2010 at 7:24 am
jeff.mason (8/3/2010)
That query isn't reliable for all servers on one domain. Per Books Online:"-L Lists the locally configured servers and the names of the servers broadcasting on the network."
If a server isn't broadcasting or locally configured, it won't find it. You can easily disable broadcasting via SQL Browser. And broadcasting also is not in reference to a domain.
oh ok, is there a better way to list all the SQL servers on a domain? I'd hate to run this query only to find 50% of the servers out there 😀
August 3, 2010 at 7:40 am
I'd try SQLPing
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 3, 2010 at 7:46 am
The only foolproof way I know of (and you probably need a domain admin account for each domain to do this) is to do an LDAP query against AD to get all Windows servers, then search each server's registry for SQL Server. SQL Browser can be disabled, and an install of SQL doesn't have to inform AD of its existence. So if you can't even in a foolproof fashion find all SQL Servers on one domain, how much trickier is multiple domains.
A tool like SQLPing is probably as good as it will get though. It's doing things like this under the hood.
August 3, 2010 at 7:52 am
jeff.mason (8/3/2010)
The only foolproof way I know of (and you probably need a domain admin account for each domain to do this) is to do an LDAP query against AD to get all Windows servers, then search each server's registry for SQL Server. SQL Browser can be disabled, and an install of SQL doesn't have to inform AD of its existence. So if you can't even in a foolproof fashion find all SQL Servers on one domain, how much trickier is multiple domains.A tool like SQLPing is probably as good as it will get though. It's doing things like this under the hood.
ahh ok, I think I'll give it a try.
SQLPing won't cause any changes or damage to a server, right? Reason I am asking is I possibly will be looking at production servers as well and would hate something to break on them:crying:
August 3, 2010 at 7:55 am
SQLPing seems to be the most popular.
In the past, I've used Jeff's method, actually through SMS to query the registry of all hosts on the domain for a SQL Server key. That's the only "good way" I've known, and then you had to account for 16 possible keys (instances).
August 3, 2010 at 7:56 am
jeff.mason (8/3/2010)
The only foolproof way I know of (and you probably need a domain admin account for each domain to do this) is to do an LDAP query against AD to get all Windows servers, then search each server's registry for SQL Server. SQL Browser can be disabled, and an install of SQL doesn't have to inform AD of its existence. So if you can't even in a foolproof fashion find all SQL Servers on one domain, how much trickier is multiple domains.A tool like SQLPing is probably as good as it will get though. It's doing things like this under the hood.
It shouldn't. I've used it in our systems without any problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 11, 2010 at 4:52 pm
This would require that you have security access on the domain to peruse whats on a server but the again I imagine any method of doing thsi would have something similiar else anyone with some sql smarts could too easily find out about SQL Server instances on a network.
While this might nott be the simplest method I would think it would be as close to %100 guaranteed as you can get so long as the SQL server is connected to a domain and running. Couldn't you check for the service MSSQLSERVER on any server and if it returns that the sevrice is running then you have a SQL Server. You might need to do some RegEx searching so as to find instances as well as any root installs.
Example: MSSQLSERVER vs MSSQLSERVER(MySQLInstance
I believe you can even do this checking of services with PowerShell among several other scripitng variants. The benefit here is that the opertaional statsu of SQL Browser woudl be irrelevant. Just a thought to ad to the mix.
I don't suppose anyone with the LDAP/AD method would be willing to share that code???
Kindest Regards,
Just say No to Facebook!Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply