May 10, 2021 at 4:09 pm
I'm using my registered server list to generate a collection of servers in powershell. The problem I ran into is some of the registered servers are named instances and I can't figure out how to filter those out using the SMO server props.
I'm creating the SMO server as new objects of type Microsoft.SqlServer.Management.Smo.Server. There is an "InstanceName" property but it's coming up blank.
The reason this is an issue is because I'm trying to access the windows server properties using Get-WmiObject, which of course fails on the named instances. I could do a string search on my list of servers and exclude anything that has a "\" in the name, but would rather use the server property to determine this.
Any help would be appreciated, thx.
May 10, 2021 at 7:03 pm
I was doing some digging into this and best I can tell there is no way to do it without looking for a "\" in the ServerName field. And this isn't going to be reliable either. I think you will also need to watch for SQL Aliases as well.
If I am not mistaken, get-wmiobject is not valid for a SQL instance though and would require connecting to a Windows Server. If this is correct, relying on the SQL instances for getting the server names feels unreliable and may even result in duplicate lookups if your server has multiple SQL instances hosted on it.
I imagine the list of windows servers hosting SQL instances is probably a pretty small list. It may make more sense to maintain this list in an excel file and pull the data from Excel. At my workplace, I have an excel file that has all of the servers along with which SQL instances is hosted on which server. This is then stored in sharepoint for all of the DBA's to access. The advantage to this is in the event of a disaster, we have a list of where the instances live and various other useful configuration options (max memory, max dop, cost threshold for parallelism, hostname, instance name, associated application (if applicable), test vs live, etc). So if someone asks me "how many instances are running on SqlHost123?" I can grab the excel and tell them. If someone asks for a new SQL instance, I can grab the excel file and see which one is the best candidate based on disk space used and memory allocated. Results in a file that needs to be manually maintained, but so do registered servers and those lack the additional details that I can capture by having it in Excel.
Just my 2 cents on the approach you are taking.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply