December 28, 2015 at 8:45 pm
Comments posted to this topic are about the item Server.Configuration Properties
December 28, 2015 at 11:58 pm
Nice question, had 2 test this, not a big user of PS, thanx.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
December 29, 2015 at 6:24 am
I don't use it either. I guessed. 😉
December 29, 2015 at 6:42 am
Guessed right, thanks.
December 29, 2015 at 6:43 am
Ed Wagner (12/29/2015)
I don't use it either. I guessed. 😉
Me too.
December 29, 2015 at 10:44 am
Quite a nicequestion, but why involve PowerShell?
I have very rarely used PowerShell but didn't allow the code shown as part of the question to distract me from the real question, which was about the object Server.Configuration.Properties so not about PS at all but about a SMO object. The earlier comments were something of a surprise - apparently people were fooled by the way the question was presented into thinking that it's not a SMO object but something else that is related to PS. Most people who have used SMO will have used it with JavaScript or VBS or C# or some other scripting language dating from long before PowerShell came along, since SMO dates from long before PowerShell.
Having answered, I looked at the comments and noticed that the PS seemed to have distracted people from the point of the question; I also noticed that the code will only run in a context which allows scripts to be run. My default context doesn't allow scripts to be run, only individual statements (and from a security point of view I believe that should be the default for everyone who doesn't use powershell much - if I ever start using PowerShell often enough to be irritated by having to enable scripting each time I want to use scripts I'll change the default PE Execution policy of my [non-privileged] username scope to avoid having to type -ExecutionPolicy RemoteSigned when starting PS or Set-ExecutionRemoteSigned -Scope Process before running a script, but until then I might as well be that little bit more secure) so perhaps there's a risk of making PS novices adopt poor security on this. I could have run the code if I wanted to (PS script execution policy is trivial to bypass unless it's enforced at machine or user level by Group Policy) but I decided not to bother because (a) it's obvious what the code does and (b) the final line would generate far more output than I would want to read.
Tom
December 29, 2015 at 10:50 am
TomThomson (12/29/2015)
Quite a nice question, but why involve PowerShell?
I was wondering that myself. It is easy enough to get a list of the configs from a simple select statement, unless the SMO is revealing a setting that the sql statement would not.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 29, 2015 at 12:07 pm
SQLRNNR (12/29/2015)
TomThomson (12/29/2015)
Quite a nice question, but why involve PowerShell?I was wondering that myself. It is easy enough to get a list of the configs from a simple select statement, unless the SMO is revealing a setting that the sql statement would not.
Well, SP_CONFIGURE (without parameters) will display only the settings that don't require 'show advanced options' to be 1; so in SQL server 14 it will show only 18 of the 70 options unless show advanced options is on. The SMO object contains all the options, and Steve's SMO shows the whole object.
In addition, the SMO object has 9 attributes per option intead of just 5; the 4 missing from SP_CONFIGURE output are the Option Number (these are not a dense set, but I've found them useful because they are not as big as the names), whether the option is advanced or not, whether the option requires an instance restart to change or not, and a plain English explanation of what the option is (maybe useful when the display name is a bit opaque, but I've never had a use for it). I've never bothered to find out how to get these additional columns in SQL because it wasnt in SQL that I needed them - but I can see it being a simple select statement as you suggest.
Tom
December 29, 2015 at 1:59 pm
Got it right. 🙂
Thanks.
January 4, 2016 at 4:45 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 4, 2016 at 12:18 pm
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.
January 10, 2016 at 1:26 pm
TomThomson (12/29/2015)
SQLRNNR (12/29/2015)
TomThomson (12/29/2015)
Quite a nice question, but why involve PowerShell?I was wondering that myself. It is easy enough to get a list of the configs from a simple select statement, unless the SMO is revealing a setting that the sql statement would not.
Well, SP_CONFIGURE (without parameters) will display only the settings that don't require 'show advanced options' to be 1; so in SQL server 14 it will show only 18 of the 70 options unless show advanced options is on. The SMO object contains all the options, and Steve's SMO shows the whole object.
In addition, the SMO object has 9 attributes per option intead of just 5; the 4 missing from SP_CONFIGURE output are the Option Number (these are not a dense set, but I've found them useful because they are not as big as the names), whether the option is advanced or not, whether the option requires an instance restart to change or not, and a plain English explanation of what the option is (maybe useful when the display name is a bit opaque, but I've never had a use for it). I've never bothered to find out how to get these additional columns in SQL because it wasnt in SQL that I needed them - but I can see it being a simple select statement as you suggest.
for Steve : a nice question which was an easy one as I have studied deeply SMO ( and Configuration.Properties especially )
For Tom : you are absolutely right. If a person tries to find the good answer , it is impossible except if he/she has used SMO and especially Smo.Configuration ( which has the same number of properties than sp_configure has possibilities of options to configure ). Be careful anyway , as I am not sure that SMO accept all the new possibilities provided by SQL Server 2016 , there is always a little latency in the reactivity of the team maintaining the SMO libraries...
I am often grumbling against you because of your style of writing not always easy to understand ( I am a poor old french man reading the English language in a difficult way and my written English is an horror ) but , your explanation is easy to understand and full. Cheers to you...
February 13, 2016 at 9:55 am
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply