SQLRNNR (12/29/2015)
TomThomson (12/29/2015)
Quite a nice question, but why involve PowerShell?
Because we can, of course!
And this:
Steve Jones - SSC Editor (1/4/2016)
PoSh might be more convenient if you were deploying this across many instances with checks. I don't know that it's better or worse than using a SQL statement, but if you are working in the PoSh model, and getting properties and calling methods, this makes the code more consistent, IMHO.Not that you should do this, but it feels more consistent in a PoSh script than invoking a SQL command and reading back the result set.
If you have multiple lifecycle environments (dev, qa, uat,, prod, dr, etc.) spanning multiple network segments or domains PowerShell is better positioned and equipped to handle the connectivity needs than the pure T-SQL methods. In my experience admin work quickly extends into auditing disk, cpu and memory, checking Active Directory, inspecting Windows and SQL Server settings, etc. Standing up an app server to run centralized PowerShell scripts is a lot of times simpler than getting all of that working in SQL Server. A simple Windows VM is also a lot less expensive than standing up a SQL Server instance to host these kinds of admin scripts, or having them ride shotgun on a server handling a production database workload. Of course, YMMV.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato