October 28, 2011 at 12:36 pm
Hi Guys,
I want to know how i can get list of all SQL Server installed in different IT Enviornment ( Dev,Test,Prod)?
Any 3rd party tool or any t-sql script ?
October 28, 2011 at 1:17 pm
As I read what you are asking, you want to effectively search for SQL Servers. A sql script is not likely to get you there since you have to have a SQL server run it and its scope it limited. What you really need is something that will walk through a range of IP addresses and query the machines about SQL? This can be done in SSIS with a WMI reader or script task. Even that is kind of tricky but can certainly be done, I went the script task route due to performance. I have the basis for such code that I have developed. I hope to release that code on CodePlex by the end of November.
CEWII
October 28, 2011 at 2:59 pm
logicinside22 (10/28/2011)
Hi Guys,I want to know how i can get list of all SQL Server installed in different IT Enviornment ( Dev,Test,Prod)?
Any 3rd party tool or any t-sql script ?
In many envirnoments, you can't. Simply because each environment has its own network segment(s), and there is no routing between them. Additionally, both the network name and the IP address may be the same in for instance QA and prod.
October 31, 2011 at 3:05 am
there is a tool from Quest which is freeware and allows you to scan the network, but you need to know IP ranges, what you think default passwords would be, are they running under a domain account etc for it to query WMI properly and login to SQL to actually see what is running on the servers you can.
think is discovery wizard if they still have it on their portfolio.
October 31, 2011 at 3:26 am
1. First do the following query to allow to execute xp_cmdshell
-- 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
2. Exec xp_cmdshell 'SQLCMD /L'
October 31, 2011 at 9:13 am
I see two things wrong with this approach.
1. You use xp_cmdshell, which we have been having quite a raging discussion on in another thread.
2. It implies that you know that there is one and have access to the SQL Server on the box.
CEWII
November 1, 2011 at 8:17 am
Hi
Thank you for script i am able to get list of servers which are on same domain and network.
So is there anyway i can get like this format?
list of Servers Version SP
Anyway thanks for kind help really appreciated 🙂
November 1, 2011 at 12:08 pm
you could run the follwoing select on each sql instance or u could create 1 central box who has a linked server connection to each instance and run the below select as dynamic sql in an openquerry call. Thats how we do it. We have a monitor box who has a linked server connection for each instance. The open querry allows u to put the linked server name as the first param
SELECT CAST(@@version AS VARCHAR(300)) AS ServerVersion, CAST(SERVERPROPERTY('productversion') AS VARCHAR(255)) AS ServerProductVersion , CAST(SERVERPROPERTY ('productlevel') AS VARCHAR(255)) AS ServerProductLevel, CAST(SERVERPROPERTY ('Edition') AS VARCHAR(255)) AS ServerEdition
i'd have to do a few more steeps like seting up a monitor user on each instance to allow the select to go through etc. I'd be glad to elaborate in more detail if u like.
Theopen querry looks like this
select * FROM OPENQUERY([linked server name goes here], 'SELECT CAST(@@version AS VARCHAR(300)) AS ServerVersion, CAST(SERVERPROPERTY(''productversion'') AS VARCHAR(255)) AS ServerProductVersion , CAST(SERVERPROPERTY (''productlevel'') AS VARCHAR(255)) AS ServerProductLevel, CAST(SERVERPROPERTY (''Edition'') AS VARCHAR(255)) AS ServerEdition')
The good thing about this method is if it's designed properly then u can basically run any query you want accross any sql instance you have access to.
November 1, 2011 at 12:27 pm
Elliott Whitlow (10/31/2011)
I see two things wrong with this approach.1. You use xp_cmdshell, which we have been having quite a raging discussion on in another thread.
2. It implies that you know that there is one and have access to the SQL Server on the box.
CEWII
]
Not sure #1 is a problem, but that's a topic for that other thread 😉
The problem with sqlcmd /L as I see it is that it's looking for ports 1433 or 1434. Under a number of circumstatnces, like if SQL Server is configured to listen on a non-default port without the browser service running or maybe TCP/IP and named pipes protocols are not enabled, or maybe those ports are not accessible from your client, then you wont get a response.
The way to know if SQL Server installed with 100% certainty is to know if the service exists. The way I would approach this, requires a list of machines to check- so I'm not "discovering" machines on the network, I have a list of machines that I want to check on. I would then use powershell, to enumerate the services isntalled on each and check if the name is like "SQL Server". some servers might not be accessible via the network but since I have a list I would know that I need to track those down and check them.
That's my $0.02
November 1, 2011 at 3:17 pm
NJ-DBA (11/1/2011)
Elliott Whitlow (10/31/2011)
I see two things wrong with this approach.1. You use xp_cmdshell, which we have been having quite a raging discussion on in another thread.
2. It implies that you know that there is one and have access to the SQL Server on the box.
CEWII
Not sure #1 is a problem, but that's a topic for that other thread 😉
The problem with sqlcmd /L as I see it is that it's looking for ports 1433 or 1434. Under a number of circumstatnces, like if SQL Server is configured to listen on a non-default port without the browser service running or maybe TCP/IP and named pipes protocols are not enabled, or maybe those ports are not accessible from your client, then you wont get a response.
The way to know if SQL Server installed with 100% certainty is to know if the service exists. The way I would approach this, requires a list of machines to check- so I'm not "discovering" machines on the network, I have a list of machines that I want to check on. I would then use powershell, to enumerate the services isntalled on each and check if the name is like "SQL Server". some servers might not be accessible via the network but since I have a list I would know that I need to track those down and check them.
That's my $0.02
#1 is a matter of perspective and it has been beating literally to death in that thread..
#2 it a trickier one, because I want to work in SSIS for these kinds of thing I approached it with a Script task to handle the WMI. It uses a couple of different classes, but I only get the SQL services so I don't have to try and pick them out of the list. I have one version that works with a list of servers that I already know about. I am working on a tweaked version that takes an IP address range and goes machine to machine looking to see if it can access it and if so whether SQL is installed. It is limited to SQL 2005, 2008 (and R2) and SQL 2012. I am electing no to support SQL 2000 for it. I'm fairly sure your PowerShell would end up using a WMI reader to get the service list. And that is a completely valid method that does not require anything but access to the machine and the ability to query WMI.
Altogether I would prefer yours or my solution.
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply