I was working with someone today that had little knowledge of navigating SSMS and other common tools for SQL Server. At times, we really need to step back and remember that not everyone touches a tool like SSMS almost daily. That is in part a consulting concept and one that should be taken seriously when it comes to not letting your work with others frustrate you. Remember, if something isn’t going right in a situation, even as minor as retrieving SQL Server configuration information, it typically means you need to be better at how you are explaining it or take a better path to obtaining the information.
SQL Server Built-in Reporting
The method I often choose to gather information on a high level when working with an individual not having much SQL Server experience is to take advantage of the built-in reporting in SSMS. Now, throwing scripts or statements for someone to run is truly ideal and can gather drilled down information. However, in a few cases you will find yourself having troubles with the execution of those scripts. Remember, don’t get frustrated, and find a different path.
In this example, a high level view of SQL Server is required. Last start time, number of logical processors, clustering, edition, total memory and so on. A visual representation of this is always easier for people that are not dug down deep in SQL Server or Windows Server technology to understand. Even more easy is the click and drag concept of obtaining the information.
Open SSMS and connect to a server. In many cases, this point is a hard point if the server name isn’t known. Ask, if they are a developer, to check connection strings in applications or even the services.msc listing for SQL Server. If a named instance is installed, the name will be in the service name.
Once connected to an instance from SSMS, right click the server name and scroll to Reports-->Standard Reports-->click Server Dashboard
This will run the packages dashboard to show the high level configurations and some resource information that can be valuable in making decisions based on future health and performance checks.
Notice that the “Non Default Configuration Options” section has been expanded. This exposes a few values that show best practice points. In my image above, you’ll see that the max server memory has been re-configured from the default of 2TB. This would be important to know as all the information would be.
Getting the information back to you
All the information in the report is great but it doesn’t do much good if it cannot easily get back to you or become mobile. Luckily, the report(s) can quickly be saved to a few common formats.
Right click an area in the report and scroll to export-->PDF
This will export the report to a PDF file. Although the PDF file may not be formatted all that great at times, based on the report executed, the information is really the only key point needed. So don’t read into the formatting on export too far. Remember the goal we need to accomplish.
I hope this short tip helps out as it has helped me on several occasions when getting the critical information needed wasn’t always an easy task.