Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Gathering Information Expand / Collapse
Author
Message
Posted Friday, November 29, 2013 2:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:00 PM
Points: 96, Visits: 252
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
Post #1518599
Posted Monday, December 2, 2013 4:06 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 15,663, Visits: 28,061
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1518829
Posted Monday, December 2, 2013 7:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:00 PM
Points: 96, Visits: 252
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
Post #1518898
Posted Tuesday, December 3, 2013 1:24 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 4,238, Visits: 3,258
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
Post #1519393
Posted Wednesday, December 4, 2013 2:12 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 4:00 PM
Points: 96, Visits: 252
Thank you very much for the help. I will give it a try.

Jeff
Post #1519783
Posted Thursday, January 9, 2014 2:57 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:15 PM
Points: 80, Visits: 252
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
Post #1529586
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse