By: K. Brian Kelley | Last Updated: 2017-04-27 | Comments | Database Administration
I need documentation on my SQL Server farm. Right now what documentation we have is generated manually. Therefore, there are servers that don't have all the documentation required, documents are out of date, etc. Is there a tool that can inventory my SQL Server environment and compile all of the information I need? I'd also love to see if it could look at some of my settings and let me know if any of my systems have issues.
As you have discovered, manual documentation methods = out-of-date documentation. Every time something changes, someone needs to update a document and practically speaking, this doesn't typically happen. There are some tools out there on the market. One of them is SQLDocKit from acceleratio. Let's take a look at what SQLDocKit can do.
SQL Server Auto-Discovery
One of the problems organizations face in this age of virtualization is the ease in which systems can be provisioned. While this is an advantage from the perspective of being able to get systems up and running quickly, it means documentation effort lags behind. Keeping an accurate inventory is key not only to proper management of your SQL Server environment, but also is necessary from a security perspective.
SQLDocKit allows you to enter servers in manually, but you also have the capability to check Active Directory to locate SQL Servers.
You can configure what domains you'll scan. You can even configure down to the OU level if your systems are deployed to particular OUs.
If you need a particular set of credentials to get that information, you can configure that when you set up a domain to be scanned.
Whether you specify SQL Servers manually or you autodiscover them, you'll have a list of SQL Servers that SQLDocKit is aware of.
As you can see from the above list, SQLDocKit is capable of inventorying not just the database engine, but Analysis Services, Integration Services, and Reporting Services as well. And once you have a SQL Server in the list, if you need to set an additional set of credentials in order to get the information out, you can do that by clicking on the particular SQL Server and clicking Edit.
SQL Server Inventory Information with Snapshots
Once you have your servers, you can use SQLDocKit to inventory them. You do this by taking a snapshot. A snapshot compiles all the data for the SQL Servers you choose to have snapshotted. You can then view the results of a particular snapshot or compare two snapshots for changes.
Once you've completed a snapshot, you can view what was captured. Select the snapshot (using Snapshots in the left pane) and the click on SQL Inventory and you can view the various settings. Here's an example of the type of data that's collected for the database engine.
However, the real power comes from doing a comparison. Using the Compare Wizard, you can look at the differences between two snapshots. When you do a compare, SQLDocKit gives you a quick glance view to show what has changed.
SQLDocKit also provides the specific details:
Documenting Your SQL Servers
SQLDocKit is a great tool at collecting information. However, that doesn't do you any good if you can't get that information out into another format. As you might expect, SQLDocKit does handle exporting that information. The current version will export the information as documentation in PDF, Excel, or Word format. The good news is that you can select what you want to include in your documentation.
If you're interested in seeing what the documentation looks like, SQLDocKit's site has examples of it for all three formats. When you go to generate documentation, there are three options which configure settings for you. You can customize these, but those settings are:
The documentation examples on the web site correspond to these settings.
SQL Server Reporting
Speaking of documentation, SQLDocKit also comes in with some useful reporting with regards to usage. For instance, here's an example for how much storage and what percentage of the allocated space each file is using:
There aren't a lot of "reports" at this time because a lot of the information is tied instead to Best Practices.
SQL Server Best Practices
With a snapshot selected, if you view Best Practices, the first thing you see is a dashboard showing how well your systems are doing.
If everything is meeting best practices, you'll see a checkbox. Otherwise, you'll see a number indicating how many items are at issue with that particular category. Here's a small example of a different snapshot showing why SQLDocKit flagged TempDB Configuration as an issue.
There are a number of best practices and they're being updated regularly. Here's a partial list of what's being looked at based on the time of this review:
Subscriptions and Alerting
If you have SQLDocKit installed as a service, you also have the ability for SQLDocKit to schedule and take snapshots automatically. From these snapshots you can set up report subscriptions and alerting. You can set up how often the subscription runs:
Where it puts the information:
And what to include:
SQL Server Security Management
The SQLDocKit team is constantly adding new features to the product. As I was conducting the review, the team added more reporting on the security side. For instance, here's the logins and server level roles on a particular SQL Server:
There is also some management capability at this level, however, SQLDocKit isn't intended to be a security management tool (at least not yet).
How do I get started with SQLDocKit?
- Check out the free resources available for SQLDocKit.
- Download SQLDocKit to see how it can help you.
- Think about all of the challenges you face with current SQL Server documentation and how SQLDocKit can solve these problems.
- Communicate with your team and management about how you think SQLDocKit
- SQL Server Inventory Management across Active Directory and Specific Organizational Units
- Current documentation recorded as a snapshot to compare with historical snapshots
- Inherent reporting and best practices to ensure your SQL Servers are running smoothly
- Highly configurable alerting for proactive notification
- Security reporting options to quickly understand permissions
- Put SQLDocKit through its paces in your environment, share the results with your team and determine your next steps.
If you need to document your SQL Servers and keep on top of how they change over time, SQLDocKit is a solid tool to do this work for you. You'll definitely see a significant time savings with the information it collects and how it prepares it for consumption. Also, the built in compare tools as well as the best practices analysis will help you spot issues with your SQL Server environment, helping you to stay on top of the configurations. Finally, this is a tool that's actively being worked on, with best practices being added regularly and new features, like the security reporting, being included to help the DBA with his or her environment. I would definitely recommend including this product in your consideration for managing your SQL Server environment.
- Download a copy of SQLDocKit and begin your evaluation.
- Review the document samples and use them to build your case with management.
MSSQLTips.com Product Editorial sponsored by acceleratio makers of SQLDocKit.
Last Updated: 2017-04-27
About the author
View all my tips