We all know that we should document our SQL Servers, right? RIGHT?
So let's say you had some time on your hands and decided that today's the day you're going to get it done. You might open SSMS, connect to a server in Object explorer, start clicking around, and copying properties into an Excel spreadsheet. That gets old quick, so you write TSQL to grab information...until you realize that you're going deep down the rabbit hole accounting for all the nuances and new features between versions of SQL Server. If you're adventurous you might crack open your IDE of choice and dig into SMO, but that's another rabbit hole of a different kind, too. Frustrated, you give in and download a trial version of a commercial documentation tool. It works pretty well, except it's taking forever to manually go through every database on every server. A couple hours later you've got a handful of databases documented...but what about server configurations or details about the Windows OS they're installed on? How do you compare server configurations against each other? Or spot potential configuration problems? And what about those servers you just inherited - or don't even know are out there yet?
Wow, you think to yourself as you head home, this could be someone's full time job just keeping track of all this stuff!
There Is a Better Way: Introducing SQL Power Doc
Starting today, there is a better way. I'm proud to officially announce the release of SQL Power Doc.
Imagine combining the functionality of what Quest's Discovery Wizard for SQL Server, your favorite commercial SQL documentation tool, msinfo32.exe, and Brent Ozar Unlimited's sp_Blitz™ script does into a single standalone tool that you can run against all of your SQL servers at once. That's SQL Power Doc.
Here's a look at what SQL Power Doc does:
Discover
Find SQL Server Services on your network by:
- Active Directory DNS
- Subnet Scan
- Computer Name
Document
Collect comprehensive details about all your SQL Server instances and their underlying Windows OS, including:
- Service Details For All Installed SQL Sever Services
- Database Engine
- Configuration
- Security
- Server Objects
- Databases
- Configuration
- Database Objects
- Service Broker
- Storage
- Security
- SQL Agent
- Configuration
- Jobs
- Alerts
- Operators
- Windows OS
- Machine Information
- OS Information
- Software
Diagnose
SQL Power Doc performs over 100 checks to find hidden problems and performance bottlenecks on your SQL Servers before they turn into major headaches.
SQL Power Doc does all of this using Windows PowerShell and Microsoft Excel. Never touched PowerShell before? Don't worry, I've written a How To Guide for PowerShell Beginners to walk you through every step of the way.
While you're on your coffee break you can put SQL Power Doc to work for you to generate comprehensive, top to bottom documentation of your SQL Server environment. 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
- Troubleshooting
- 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
One More Thing...
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 your IT department is looking for a way to document their Windows machines do them a favor and tell them about SQL Power Doc.
Ready To Get Started?
Now that you know there's a tool that can do the work for you there's no excuse not to document your servers. Head on over to the SQL Power Doc CodePlex site, check out the Requirements and How To Guide, then grab the code to discover, document, and diagnose your SQL Servers today!
About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
14 comments
This is incredible, Kendal. Thank you for all of your efforts and for sharing it with the community. You rock!
This looks really powerful! Just one note of clarification - the description:
"and Brent Ozar's sp_Blitz script into a single tool that you can run against all of your SQL Servers at once."
That implies it's running sp_Blitz™. Just to be clear, it's not - Kendal's neat scripts are completely standalone and aren't involved with sp_Blitz™. Great job!
I thought you were joking about the sp_Blitz™ trademark Brent but it actually is trademarked!
http://tess2.uspto.gov/bin/showfield?f=doc&state=4002:7oeta0.2.1
Brent, thanks for the clarification. I've updated the blog post and CodePlex project site to include the TM symbol.
I've already run a quick test against my local machine and the information it collects is awesome!
I've had "document servers" on my todo list since I started in my current job. I think it's going to be crossed off very soon ;-)
Great effort Kendal!
It sounds great, I will give it a try. Thank you!
@Jorriss - yeah, we trademarked it to be safe. We've heard from several folks that they wanted to build their own sp_Blitz™ tool, or integrate sp_Blitz™ into their tool, and we just have to be careful to protect our work. I love seeing people build their own stuff and give it away, too, like Kendal's doing. Hopefully we'll never see a company or a slimy individual do something bad with our work - and trademarking it is the first step to protecting that name that I've worked so hard to build.
Brent, you're making me think I should trademark this now! ;-)
Thanks for this! A simplu ui would have been nice ;)
Oh... Wow!
That is just redonkulous - only done a quick couple of known servers and I'm blown away by the info returned - now it's scouring the network and has found a whole bunch of previously unknown instances (guessing - hoping! - these are MSDE/express on desktops).
Hats off and a crate of beer for you, Kendal. Epic!
Awesome!! A tremendous piece of work. I anticipate running small scale tests and ramping this up as soon as possible.
Well done for a great piece of work!
Not criticizing the tasks...just doing it on a coffee "break". What's the point of a "break" if you're working?
The point is that you kick off something which automates the work for you while you're on break. You're not working - the script is. :-)
This is incredible, Kendal. Just a few queries from my side. Could we customize the output of excel worksheet for only the required tabs as there are some tabs which we don't require? What are the steps that we need to disable on your script to have filtered output?
Post a Comment