I recently did a post on testing a linked server where I said I would explain why I wanted to make the test. Basically I needed to scan a few hundred instance names and do the following
– Check if the instance is one we have access to or even exists? If not make a note of the error so we can tell the difference.
– Collect information like instance size (total size of all databases), CPU count, memory count etc.
– Collect a list of database names on the instance, their status, size, etc.
I’m of mixed feelings about this. On the one side, I appreciate that it’s pretty simple and does what is promised: a quick and dirty method for reaching a SQL Server instance (or something which might be one). On the other hand, it feels like this is trying to be the combination of two or three things which do the job better:
- Central Management Server, for managing the SQL Server instances available to you. This is where you’d put confirmed instances and start running checks like Kenneth wants.
- Nmap, for determining whether there are servers which are not part of your CMS listing. Nmap can check for availability on port 1433 and see if that’s a SQL Server instance on there, as well as hitting the SQL Browser service for instances not on 1433 (and assuming you leave the Browser on).
- A Powershell script to combine these together, or at least a script in some language which does a decent job with looping. Even better if it offers parallelism.