I have imagined this article to be some sort of outline of available solutions for SQL administrators. This blog post discusses various solutions for detecting and inventorying Microsoft SQL Server instances.
Before we get started, let the truth be told that there are a great number of free tools and PowerShell scripts designed for the most widely deployed database platform Microsoft SQL Server. However, for this blog post, I have chosen 4 and added our new SQL Server inventory tool – SQLDocKit – to the list.
(SQLDocKit disclaimer) We developed the SQLDocKit tool because we had a need for a similar tool, and none of the existing tools covered all that we wanted to have. So we put some time aside and developed a tool that we first used internally; then, when we saw it was an extraordinary and time-saving tool, we decided to release it to the market.
Everything that we built in the tool initially, we needed for our internal SQL Server nightmare. I thought we had around 30 to 40 SQL servers, but we ended up with 76 (more than double), and a lot of unused databases that took up really valuable space on our internal storage. Imagine when you free few terabytes of ultra fast storage; you have win-win situation for management, the storage guys, and the DBAs.
First things first:
When it comes to Microsoft SQL Server and SQL administration, a few things need to be mentioned. First of all, it’s not an easy task. Making sure SQL Server instances are regularly backed up, that SQL servers are patched with the latest services packs, and so on, requires a comprehensive SQL Server inventory. Knowing exactly what SQL Server administrators monitor and what they are in charge of does make SQL Server management easier.
Problems SQL Admins face
Why is SQL Server management such a nightmare?
The problem is that sometimes – actually, more often than you think – SQL admins are the last ones to find out about the changes made to a server. This is no joke because when it comes to responsibility, the SQL admin is going to be blamed for allowing the whole system to be brought down to its knees. Then what? Then he will be given the task of restoring damaged data on a database no one had a clue existed. And so the horror story begins. Without an SQL Server Inventory and documentation tool, this task sounds even more sadistic than it really is. The SQL clusters where DBAs have full control and carefully share permissions are not a big issue, but the problem lies with all those other servers that are used for development and testing, where normal users have more permissions because they create and delete databases, backup restore, and perform various tasks that are not always just the DBAs’ job.
And not only is that a true nightmare for SQL and DBA admins, it is a great risk for businesses.
Improper server management and maintenance can cost the enterprise resources and real money (remember the story I told you in the second paragraph – when you actually free the expensive SAN storage space, everybody is happy?). This leads to the conclusion that having insight into SQL Server documentation and complete inventory goes hand in hand with successful management, but how does it impact the business itself?
To put it mildly, it all goes to smithereens without proper SQL Server administration. For example, if the SQL admin department isn’t aware of deployed instances or databases running wild in the enterprise what kind of picture would that paint about the department? Can they prove how many licenses they actually need, or how much storage they actually really need?
As the “X-Files” tagline would phrase it, the truth is out there.
(Love the “X-Files,” BTW, and missing Mulder and Scully. That is one of the series I watched as a little kid; every Thursday at 20:15, you knew where you would be.)
On your disposal you have custom-written PowerShell scripts (such as Kendal Van Dyke’s PowerShell script) as well as some of the more advanced solutions in the form of agentless software that takes care of your SQL Server environment. And depending on what the script or a specific tool does, they can be grouped by the different features each solution has, and their complexity. They can be also sorted by whether they are free to use or paid tools.
We’ve selected five solutions including our SQLDocKit: Microsoft Assessment and Planning (MAP) Toolkit for SQL Server, Dell Discovery Wizard for SQL Server (previously known as Quest), SQL Power Doc, CentralDB, and SQLDocKit.
We do not plan to go into quality details and ratings since all of these solutions are developed for more or less the same thing; and given that each SQL administrator has his own specific needs, ultimately it is up to the user to decide which combination of tools he needs.
As you can see in the chart below, we’ve pulled out the main characteristics for each solution.
Microsoft Assessment and Planning (MAP) Toolkit for SQL Server
This solution is mostly used for discovery and SQL Server inventory. It’s best utilized for various platform migrations that work well with both SQL Server and Oracle instances. MAP’s strongpoint is the hardware assessment, which makes planning and migrating database instances possible. What is unique about this Microsoft SQL Server solution is that it has an Oracle schema reporting feature identifying the size and use of each schema. That makes it a good advisor when estimating the complexity of migration to an SQL Server. The biggest problem I’ve always had was exporting. The Toolkit tends to create a mess of everything and you need to select and classify what is of use to you. You might need to use Power BI to create fancy graphs for management. The MAP Toolkit for SQL Server, unfortunately, creates only one view of the whole infrastructure and cannot be automated to take inventory every once in a while so you can monitor changes.
More information is available at: https://technet.microsoft.com/en-us/solutionaccelerators/dd537572.aspx
Dell Discovery Wizard for SQL Server
Dell’s Discovery Wizard was developed for detecting SQL Server instances as well as monitoring network changes. Interestingly, you may run custom queries that run against discovered instances, and the Wizardly tool has a neat feature – the database history review. The Wizard is really small and quick; it discovers servers based on the IP range you submit. That thing it’s missing is automation and development. Dell stopped upgrading and further developing the Wizard few years ago, and I’m guessing it probably won’t ever be updated for the latest SQL servers.
SQL Power Doc
This is a great example of what can be done with a collection of PowerShell scripts – if you’re PowerShell savvy, of course. Like MAP, it’s able to track down SQL Server instances as well as Windows machines. The great thing about it is that it can document the SQL Server inventory. The bonus is that the documentation includes the Windows Azure SQL Database as well. Once you have everything documented, it can help you with security audits, baselines, troubleshooting, and licensing audits. An additional value with this solution is that, with the outline documentation, you can compare servers and databases – manually, but you can. SQL Power Doc can be used for performing various checks in search of performance bottlenecks. Since the SQL Power Doc is a script, you can automate the reports; however, to find the differences between two snapshots you will need to use a third-party tool.
More information is available at: SQL Power Doc – http://sqlpowerdoc.codeplex.com/
CentralDB
With CentralDB, we have yet another PowerShell solution on our hands. It can inventory SQL Server instances that can be viewed using SSRS reports. The fact that CentralDB pings all servers to check if they are running and sends alerts if they are down is a very useful feature. Among the inventory information, you might be pleased to find the number of cores your servers have. Other than that, you have your standard information, such as service packs, CPU, and more. The issue I’ve had with it is that it doesn’t work out of the box. You need to Google a bit to find out how to have it up and running. CentralDB is no longer under development and, unfortunately, at the time I wrote this article, I was not able to access the website.
More information is available at: http://crazydba.com/sql-server-inventory-and-performance-baselining-using-powershell-and-ssrs/
SQLDocKit
It would be unfair to compare this solution with the other free ones we’ve mentioned before since this is the only paid tool in this article. To get SQLDocKit and try it out, let me know and I’ll provide you with the free extended trial license. That way you have enough time to play around with the tool, see how you like it, and share your feedback with me.
SQLDocKit autodetects all SQL Server instances, and it can also track down Windows machines in the environment. The difference is that it’s a full-featured tool and a bit more complex than a simple PowerShell script in the backend. However, in the front end, everything is at your disposal and just a few clicks away.
Even though certain scripts have something similar to the compare feature, with SQLDocKit you can compare two different servers, or the same server but at different points in time. Track changes with the Snapshots option. It allows you to inventory your SQL Server environment and gather the information via reports, which you can then export or save to your File Share system. To top it off, it has this full documentation of the entire SQL Server settings that’s also exportable. Among other features, it has best practices suggested by community best practices.
More information on: www.sqldockit.com
Conclusion
What all these solutions have in common is that they can detect SQL Server instances (some of them proven valuable for discovering Windows Servers as well) and report on SQL Server inventory. Differences vary from small details to larger feature packs; however, it must be noted that for starters they differ in whether we’re talking about a PowerShell script, a single-featured tool, or a full-featured product.
The advantage all of these solutions have is the ability to avoid manual steps and having to dig your way through the Service Principle Names (SPNs) listed in Active Directory or having to query the registry when trying to identify SQL Server instances.
In the table below, we have a summarized checklist of what each solution can accomplish. So before you make your decision, ask yourself what features would help you better manage your SQL Server environment. When you have a brief idea of what you’re looking for, it will be easier to decide on your download.
So before you make your decision, ask yourself what features would help you better manage your SQL Server environment. When you have a brief idea of what you’re looking for, it will be easier to decide on your download.
