How to autodiscover SQL Servers in your domain?

We’re going to start off today’s blog with an introduction to the SQLDocKit autodiscover feature.

We’ve gotten a lot of questions about the topic of autodiscover and how SQLDocKit gathers all the information and SQL Server inventory, so we decided to give a more detailed explanation here. And, hopefully, light the fuse for further discussion in the comments section below.

Our team mainly focused on the most common SQL admin problem – how to autodetect all SQL Servers in the environment?

You could call this a problem, however, this is only a symptom and the real cause is poor organization with improper SQL Server documentation.

So let’s address the elephant in the room – your SQL admin has no clue how many SQL Server instances you have in your domain. Or even worse, there are no SQL admins in your company at all.

Now the last situation I’ve mentioned might just be a bottomless pit.

You don’t have a proper server inventory, your system admin left, and you need to come up with a precise number: how many SQL Server instances do we have in our company?

How does the autodiscover SQL Servers feature work?

Let’s take it from the beginning.

Tracking down and autodiscovering all SQL Server instances in your environment is a tedious task.

At your disposal you have custom written PowerShell scripts and other ways you can dig out the crumbs scattered in the Domain Controller, but what happens if you need comprehensive documentation regarding your server environment?

Well, it’s safe to say you can’t pull that rabbit out of the hat using a simple PowerShell script without losing your marbles.

And on that bombshell, let me introduce a SQL Server inventory and management tool – SQLDocKit.

SQLDocKit is an agentless SQL Server inventory and management tool. The tool needs to be deployed on one application server or workstation in your domain. It doesn’t require agents to be installed on the monitoring SQLs. SQLDocKit supports all versions and editions of Microsoft SQL.

SQLDocKit has an ace of spades up its sleeve and its main purpose is to help you autodiscover SQL Server instances in your domain.

Let’s return to the initial question: How does SQLDocKit autodiscovery work?

You could say that the feature is rather self-explanatory, however I’d like to discuss some key points and refer to certain details when it comes to autodiscovering SQL Servers.

  • Step one: Autodiscover all servers in your domain

We’re going to call our test domain Contoso. Now, the Domain Controller keeps a list of all servers in your company. You install SQLDocKit in your domain and that’s the first step: retrieving the information from the DC.

The SQLDocKit report lists all servers in your domain. Now you know the total number.

To ease your navigation through the report, you can filter and search the results based on the organizational unit.

  • Step two: Collect the list of services running.

For SQLDocKit to retrieve any data, the Contoso user must have appropriate permissions. Further on, in order to retrieve the information directly from the recently discovered servers, the Contoso user needs to have Administrator permissions granted. Otherwise the user won’t be able to gather the necessary information; the server will not grant access to the user.

Once the SQLDocKit is finished retrieving the list of services that are currently running on all your servers, among them, SQLDocKit can pinpoint the SQL Server services.

  • Step three: Gather all available information on the SQL Server.

After SQLDocKit lists out all of your servers, gathers running services, and counts down all the SQL Server instances running in your environment. You can deepen your inquiry and request that SQLDocKit generates a detailed SQL Server inventory. Among the comprehensive SQL Server documentation are the details such as disk size, disk usage, services, installed programs, and other relevant data.

Note that you need to configure SQL Server to listen on a specific port, because in cases where the server port is already taken, it will not retrieve any information. Refer to the blog post titled: How to change the SQL Server port? to find out more.

Why do many get confused when it comes to autodetecting SQL Servers?

Unlike other Microsoft products, SQL Server offers free editions such as SQL Express. It’s relatively easy to download and install the software, and many people do it without informing the sysadmin about it.

As time goes by, more and more SQL Server instances end up being installed and one day you might wake up to find that somehow production wound up on someone’s PC. Developers may start working on their own little project that no one suspected would become a real thing, but it turns out to be a fully functional application taking up valuable storage. Instead, it should be deployed on a server.

The other reason that you need to be aware of the number of servers you own is for compliance.
Licensing can be difficult to keep track of, especially when you have virtual machines running in the domain.

Confusion can occur if you have licensing per server.
You need to make sure you are using your resources properly. SQLDocKit takes care of that as well, because it gathers all the databases and allows you to manage them.

Example: You are aware that you have more databases than you can count and they are taking up precious storage. We all know most of them are test databases developers are prone to piling up, but how can you be sure you can delete them?

SQLDocKit allows you to change the database state from read/write to read-only. Give it some time, and if your users aren’t complaining or reporting any problems as a result, it means they no longer need those databases and you’re free to do as you see fit.

Tell us you’re your experience!

Why waste valuable and expensive disk space when you can document your entire SQL Server environment down to the tiniest detail? And now that we have elaborated on how the autodiscover feature works, you can download it and try it for yourself. SQLDocKit offers a free 30-day trial, so you have plenty of time to test it out and see it in action.

What kind of situation did you have in your organization? Did you get to the bottom of the SQL server inventory? I’d like to hear from you, so feel free to leave a comment below, and we’ll be happy to join the discussion.