SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Gathering Information


Gathering Information

Author
Message
jayoub
jayoub
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 540
I am a newbie working as a DBA and wanted to pull together some information about my environment. I created an Access database where I plug in all me SQL server, instance, database and maintenance plan information. In previous environments I have just manually entered the information because thier were only a few servers. I have recently started working in a much larger environment and cannot hand jam all that information into my Access CMDB(Configuration Management Database).

I was wondering if there are system views or DMV that I can use to get the information easily. For example I collect data on the instance of the server. I would like the following information about the instances

Name
Type (Name or default)
SQLVersion
SQL service pack number
Cluster Name (if clustered)
Agent (Is SQL Server Agent enabled
Login audit settings
Memory AWE Setting
Minimum Memory setting
Maximum Memory setting
Default data File location
Default Log file location

This may be a little too much to ask, but if you just point me in the correct direction I can work from there.

Your help is appreciated.

Jeff
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39452 Visits: 32630
Three suggestions.

Most of what you're going for from that basic list are either system variables (@@servername, @@version, etc.) or can be accessed from the sp_configure query. So yeah, getting that information is pretty straight forward.

Why on earth, when you're already using & supporting SQL Server, would you put the data into Access? Just store it in a SQL Server database?

I'd suggest looking at this as an opportunity to start learning PowerShell as a means for trolling through your servers to run these queries. It'll make automating the process a heck of a lot easier.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
jayoub
jayoub
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 540
thank you for the help.

At the moment Access is easier to manage because I can easily build a GUI and generate reports using it.

I will look into your suggestions and again thanks for your help

Jeff
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16532 Visits: 10059
There's a pretty cool feature in SSMS that allows you to run a query against multiple instances at the same time and return the results in a single result set. The requirements are that A) you have a registered server for each one and; B) the SQL can execute in the different versions of SQL Server where you want to run it. In other words, don't run 2008-specific code on a 2005 server.

First, navigate to Registered Servers in SSMS. The servers are grouped into folders. Right-click the folder and then select New Query in the context menu. A new window will open; note the change in the status bar at the bottom. Type your query (say, SELECT @@version) and press F5. Your result set will include the server name as the first column and then your selected values as the rest of the columns. I find this approach to be very efficient if I have to check for logins I have to delete or a quick permissions check.

HTH


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
jayoub
jayoub
SSC-Addicted
SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)SSC-Addicted (431 reputation)

Group: General Forum Members
Points: 431 Visits: 540
Thank you very much for the help. I will give it a try.

Jeff
dbassassin
dbassassin
SSC-Enthusiastic
SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)SSC-Enthusiastic (112 reputation)

Group: General Forum Members
Points: 112 Visits: 290
Brent Ozar's blitz is pretty helpful. Doesn't get you all of the information you specified, but it goes well with a bowl of wheaties

http://www.brentozar.com/blitz/

Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search