There is a powerful SQL Server documentation tool available for you to use today, and you might not even know it exists. This free tool isn’t new, but it has been maintained and supported, so it is worth your evaluation if you want an easy to use scripting tool to output your documentation into Excel format.
This is a PowerShell-based tool that is a collection of scripts that you execute on your client PC, like your desktop, and it scans your network looking for Window instances running SQL Server. It then uses it’s collection of scripts to collect data on Windows and SQL Server, then dumps all that collected information into well-formatted and easy to read Excel documents.
Like the website says:
SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2014, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 R2 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.
Find SQL Server Services on your network by:
- Active Directory DNS
- Subnet Scan
- Computer Name
Collect comprehensive details about SQL Server instances and their underlying Windows OS, including:
- Service Details For All Installed SQL Sever Services
- Database Engine
- Server Objects
- Database Objects
- Service Broker
- SQL Agent
- Windows OS
- Machine Information
- OS Information
This documentation is useful for:
- Baselines – know what your SQL Server environment looked like last week, last month, etc.
- Security Audits
- Licensing Audits
- Provide a complete look at how your servers are configured without having to grant access
- Comparing servers and databases
- Creating a runbook that you can give to your operations team
- Planning upgrades – see what hidden features are in use on an instance
SQL Power Doc performs over 100 checks to find hidden problems and performance bottlenecks on your SQL Servers before they turn into major headaches.
But Wait, There’s More!
SQL Power Doc isn’t limited to just SQL Server – you can also use it to collect an inventory of all the Windows machines on your network. If you’re in need of a free documentation solution for Windows SQL Power Doc is up to the task!
The stated goal of the “SQL Power Doc” scripts is to allow everyone to identify SQL Server instances, document the settings of SQL Server and the Windows hosts, and help diagnose any issues with those instances.
I’m no PowerShell expert, but I was able to use the available documentation and get it working in my environment. I will tell you that if you do run into issues, there is very limited help documentation available on the site. The more you know about PowerShell in general, the easier this will be for you if you need to troubleshoot issues.
After you download the free installation, the next step is to follow the instructions to get it installed on the client PC you are going to use for the information collection process. There is a very good guide provided that will help walk you through that quick process.
The next step is to discover your servers and collect an inventory. While your environment is probably different from other networks for some reason or another, it should be fairly easy to scan your environment.
There are three ways to discover servers on your network:
- You can query Active Directory, using DNS, for a list of endpoints
- You can scan a specific subnet using IP Address ranges
- You can use a list of specific machine names in a text file
I use a fairly generic method of scanning all instances in a specific IP Address range. This method works well for me because I know the range of IP Addresses used for my SQL Server instances. If you have several different ranges or other network configuration issues that make this difficult to use, one of the other techniques listed should work in your environment (you may need to scroll the PowerShell code example to see the entire text).
./Get-SqlServerInventoryToclixml.ps1 -Subnet 10.10.10.0/24 -LoggingPreference Verbose
This script is an easy to understand example. It looks for all instances in the 10.10.10.0 – 10.10.10.255 range of IP Addresses looking for systems that respond to a ping. Then it attempts a WMI query on those systems that responded to the original ping, looking for instances with SQL Server. If both of those steps complete successfully, it begins collecting very detailed information about the Windows instance, as well as the SQL Server instance, on that device.
The “-LoggingPreference Verbose” at the end of the command forces the script to provide very detailed logging. The default setting is “standard”, but that will not provide you with the detailed data required for troubleshooting issues. Once you know your scripting solution is working as expected, you can drop this portion of the command or change the settings to your desired level of logging detail.
In my environment, this data collection script took less than 10 minutes to execute.
The next scripting command is to take the collected data and convert it into a human-readable format. This PowerShell script is also provided, and it just uses the data collected in the first script to output 3 Excel spreadsheets (you may need to scroll the PowerShell code example to see the entire text).
.\Convert-SqlServerInventoryClixmlToExcel.ps1 -FromPath "C:\Inventory\SQL Server Inventory.xml.gz"
This step took another 10-15 minutes to generate the desired Excel files.
Script Tips and Tricks
Here are the hints about my environment that might be a little different from your setup, but might be helpful if you are trying to learn about how to use these scripts.
- The installation examples put the PowerShell scripts in your “My Documents” folder on your client, and it expects the scripts to be located in a folder named “WindowsPowerShell”. You can change this, but expect that to add some complexity to the installation and also the required troubleshooting steps if you run into an issue. My advice is to follow the examples and get the scripts working, then make changes to put everything where you want it, if a change is required.
- The first script will expect to output the results into a folder on the root of your system disk, which is normally your C: drive. It attempts to output the results to “C:\Inventory”. This can also be changed, but once again I warn you to get the scripts working first, then explore the changes required to get a path change working correctly.
- The second script expects the path to the output (the xml.gz file) from the first step. Don’t make this path too complicated or it could add too much complexity to the PowerShell script. Remember: K.I.S.S. = Keep It Simple Stupid
- I found that you need to be a local administrator on the target Windows instance for the scanner to operate correctly. I’m sure there is a way around this issue, and I haven’t investigated a solution yet, but that will limit which systems will be discovered until that is resolved.