December 21, 2011 at 7:38 am
Is there a query I can run to identify SQL Server instances in our network envrironment? Would a network admin be required to run?
Beyond that - once SQL instances have been identified, is there a query I can run for each SQL Server instance to identify user databases?
Mind you I do have SSMS installed - but running queries and capturing content for subsequent documentation is what I have in mind. If anyone has an alternative to my thinking I'll listen...
I'm just starting out so any information / URL links provided would be appreciated - thank you.
December 21, 2011 at 8:08 am
If you don't have documentation stating what the servers are, then you will probably need a net admin to help you identify all of them. There are tools (sqlping2, MBSA) that can do it, but if your netadmin is monitoring - he may come to you asking questions.
Once you have those, then you can run the following to get a list of user databases
SELECT *
FROM sys.databases
WHERE database_id > 4
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2011 at 8:37 am
bobparisi (12/21/2011)
Is there a query I can run to identify SQL Server instances in our network envrironment? Would a network admin be required to run?Beyond that - once SQL instances have been identified, is there a query I can run for each SQL Server instance to identify user databases?
Mind you I do have SSMS installed - but running queries and capturing content for subsequent documentation is what I have in mind. If anyone has an alternative to my thinking I'll listen...
I'm just starting out so any information / URL links provided would be appreciated - thank you.
There really is no query and by and large the machines have to be interogated individually to see what services are installed. However once the SQL instance is connected to finding out what databases are there is really easy and a query presented above is a start.
Now I have been working on a SQL inventory and config monitoring for a while now and recently released it to CodePlex at:
http://nclsqlinv.codeplex.com/ - SQL Server Inventory, Configuration Monitoring, and Logging Infrastructure[/url]
It does not have a component to search through a list of IP addresses yet to find out what SQL services are installed but along with reports that is something I am working on. But it is something to look at.
CEWII
December 21, 2011 at 12:36 pm
To Find SQL server instances in a local network
Simply
SQLCMD -L > c:\servers_filename.txt
You can also use powershell to get
December 21, 2011 at 12:50 pm
That can get you some but my experience shows it is anything but exhaustive.
CEWII
December 21, 2011 at 2:19 pm
A more reliable way to find machines than using NET VIEW is to select directly from Active Directory. This post has a nice script I refer to from time to time:
http://qa.sqlservercentral.com/Forums/FindPost1100455.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 21, 2011 at 2:28 pm
If you have SMS/SCOM/Ops Manager/Openview, look for the SQL services running on machines. Someone ought to be able to run a report using that.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply