Server identification

Question

Server identification

mtz676

SSCrazy Eights

Points: 9214
More actions
February 18, 2021 at 2:17 pm

#3846767

We have an end to end server inventory collection process - all automated from server collection to data collection from those servers.Servers do get decommisioned in our domain but they still remain for 30 days in the domain.How do we identify such servers as decommisioned in this inventory collection process.We would still like to gather inventory for such servers but have an additioned column or something stating if they are decommisioned or not.What is the best possible way to achieve this.
Thanks
March 8, 2021 at 7:54 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Depends on your database design. Could be another column or be another table. You haven't provided enough information to give a recommendation.

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 2

mtz676

SSCrazy Eights

Points: 9214

February 18, 2021 at 5:30 pm

#3846854

Additional column would suffice.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

mtz676 wrote:

Additional column would suffice.

mtz676 wrote:

We have an end to end server inventory collection process - all automated from server collection to data collection from those servers.Servers do get decommisioned in our domain but they still remain for 30 days in the domain.How do we identify such servers as decommisioned in this inventory collection process.We would still like to gather inventory for such servers but have an additioned column or something stating if they are decommisioned or not.What is the best possible way to achieve this.
Thanks

If you have a list of servers somewhere that controls the collection of the data, add a "DecommissionedOn" date column to that and simply carry it forward in your reporting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101105 More actions · Answer 4

After the 30 days, move them to some type of historical table. Personally I would never permanently delete such data. Compress it to the max and it will take very little space anyway.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

ScottPletcher wrote:

After the 30 days, move them to some type of historical table. Personally I would never permanently delete such data. Compress it to the max and it will take very little space anyway.

Totally agreed that I wouldn't (and don't... I have a similar system) delete data from such a table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 6

mtz676 wrote:

Additional column would suffice.

Indeed ... but maybe it would be easier / safer to leave the existing "automated inventory collection process" unchanged and add a 1:1 table alongside for the new / additional info.

A VIEW could join the two (actually OUTER JOIN so the new table only has to have rows for Inventory records where there is actually some extra info).

We had to add some extra data to our Asset Register at the start of the first lockdown to indication Location "For Items that staff had taken home" 🙂 We did a similar thing for that.

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 7

@jeff,

Thanks for the feedback but my question was how do I autoupdate the decommisioned column using a query or some script that says the server is decommisioned ? My actual question is - what windows server or db changes can be done that wouldnt affect anything(for the next 30 day decommisioned period) and I get to pick that change using scripts and update the decommisioned column accordingly and then I can make that a standard to identify all decommisioned servers down the line?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

mtz676 wrote:

@Jeff,
Thanks for the feedback but my question was how do I autoupdate the decommisioned column using a query or some script that says the server is decommisioned ? My actual question is - what windows server or db changes can be done that wouldnt affect anything(for the next 30 day decommisioned period) and I get to pick that change using scripts and update the decommisioned column accordingly and then I can make that a standard to identify all decommisioned servers down the line?

If the "decommissioned" server is simply not being used anymore, then I don't know of a consistent method to determine such a thing. If the name of the server "alias" has been changed, then routine that I use will simply not find the server. If it has been made so that the server is still online but connections to it have been disabled, then doing something like pinging the server will tell you that it's no longer accessible. You won't know just exactly when that happened unless you do regular checks.

I have such a routine that runs every night to check disk space on a lot (>200) boxes that aren't necessarily just SQL Servers. When our "NetOps" group disables access, that routine will till me that access is disabled. I don't know if it still exists or not but I do know that it's no longer available for use and it shows up in my "Enterprise Disk Report" as no longer being reachable.

My routine uses xp_CmdShell to make WMI calls to the machine names I have in a table. I don't have it setup to "auto-discover" boxes because I work pretty closely with my "NetOps" group to keep the report in shape but it's a minimal effort on my part. We also have the "browser" service disabled for security reasons, which also makes "auto-discovery" a bit more difficult and not worth it.

Most people pitch a fit about the use of xp_CmdShell (it's actually NOT the security risk that a lot of people make it out to be when setup properly) and so I don't generally offer it as a solution to folks (although its VERY effective). There are PowerShell routines that can do the same thing (although I'm amazed they allow PowerShell for such things and not xp_CmdShell). I don't happen to know any links for the PowerShell methods simply because I don't use PowerShell for such things. Rumor has it that "DatabaseTools" (a free lot of such PowerShell tools) has something similar in it. You can do a search for it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 9

Thanks @jeff for your time and the elaborate answer.

This reply was modified 3 years, 9 months ago by mtz676.

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 10

Hello @jeff,

We use MAPS to collect SQL inventory from all servers, but MAPS cannot be automated.Are there any other free tools which can scan our network periodicaly and identify for new servers inside the domain and if yes can they be automated ? Thanks

This reply was modified 3 years, 8 months ago by mtz676.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

mtz676 wrote:

Hello @jeff,
We use MAPS to collect SQL inventory from all servers, but MAPS cannot be automated.Are there any other free tools which can scan our network periodicaly and identify for new servers inside the domain and if yes can they be automated ? Thanks

I don't personally know of any that work consistently, especially if you take the extra security step of disabling the SQL Browser service. I also warn against using MAPS because it "phones home" to Microsoft about all your servers it finds and that bit of functionality can't (AFAIK) be disabled.

It also seems to me that it wouldn't/shouldn't be too much of a hassle for the infrastructure team to actually maintain and provide a simple list of servers in the form of a file you could import. Something like that might even be automated through some WMI call or a Powershell routine that would examine the Domain Controllers. I'm not an infrastructure guy, though, and so I don't actually know for sure but it sure seems like something like that could be done without much fuss.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Sometimes, you've just gotta love the internet search capabilities. Check out the following Google search for what you seek.

https://www.google.com/search?q=powershell+to+find+all+sql+server+servers+in+a+domain&oq=powershell+to+find+all+sql+server+servers+in+a+domain

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 13

p.s. Just noticed that they also list the old command of "SQLCMD -L" to find servers. I discovered very long ago that if the SQL Browser service on a server is disabled, that command will not find the server and that's one of the reasons why my standard setup includes disabling the SQL Browser service.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)