Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automatic Discovery, Documentation and Best Practices for your SQL Servers with SQLDocKit


By:   |   Last Updated: 2017-04-27   |   Comments   |   Related Tips: More > Database Administration

Problem

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.

Solution

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.

Performing Autodiscovery

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.

Manage Domains for Autodiscovery

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.

Set domain connection credentials

Whether you specify SQL Servers manually or you autodiscover them, you'll have a list of SQL Servers that SQLDocKit is aware of.

Server List

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.

Taking snapshot

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.

Snapshot collected information

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.

Comparison Summary

SQLDocKit also provides the specific details:

Comparison 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.

Document Options

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:

  • Simple
  • Regular
  • Full

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:

Storage Metrics Report

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.

Best Practices Dashboard

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.

Best Practices TempDB Exceptions

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:

Best Practices List

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:

Schedule Subscription

Where it puts the information:

Subscription Delivery

And what to include:

Subscription Report Selection

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:

Server Role Tracking

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?

  1. Check out the free resources available for SQLDocKit.
  2. Download SQLDocKit to see how it can help you.
  3. Think about all of the challenges you face with current SQL Server documentation and how SQLDocKit can solve these problems.
  4. Communicate with your team and management about how you think SQLDocKit will help.
    1. SQL Server Inventory Management across Active Directory and Specific Organizational Units
    2. Current documentation recorded as a snapshot to compare with historical snapshots
    3. Inherent reporting and best practices to ensure your SQL Servers are running smoothly
    4. Highly configurable alerting for proactive notification
    5. Security reporting options to quickly understand permissions
  5. Put SQLDocKit through its paces in your environment, share the results with your team and determine your next steps.

Final Thoughts

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.

Next Steps

MSSQLTips.com Product Editorial sponsored by acceleratio makers of SQLDocKit.



Last Updated: 2017-04-27


next webcast button


next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools