November 29, 2013 at 2:22 pm
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
December 2, 2013 at 4:06 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 2, 2013 at 7:34 am
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
December 3, 2013 at 1:24 pm
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
December 4, 2013 at 2:12 pm
Thank you very much for the help. I will give it a try.
Jeff
January 9, 2014 at 2:57 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply