Powershell to configure SQL Server Facets

has anyone tried configuring SQL Server Facets using powershell... i tried with below code..and i am able to find out properties of the Facets but not getting idea on how to set values to these properties.

[System.Reflectio开发者_StackOverflow中文版n.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Dmf') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialNam('Microsoft.SQLServer.Management.Sdk.Sfc') | Out-Null
$conn = New-Object Microsoft.SQlServer.Management.Sdk.Sfc.SqlStoreConnection("server='Ramu-pc';Trusted_Connection=true")
$PolicyStore = New-Object Microsoft.SqlServer.Management.DMF.PolicyStore($conn)
$facets = [Microsoft.SqlServer.Management.Dmf.PolicyStore]::Facets | Where {$_.Name -eq 'ISurfaceAreaFacet'} 
$facets | Format-Table –Auto

when i execute below command, i see different methods but i am not getting help on how to user those methods. $Facets | gm

i need to configure below values in the above Facet:

• AdHocRemoteQueriesEnabled = True
• xp_cmdshell = true

While it's not exactly using what you show but here's what I use.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$server = New-Object 'Microsoft.SqlServer.Management.SMO.Server' ('Ramu-pc')
$server.Configuration.AdHocDistributedQueriesEnabled.ConfigValue = 1
$server.Configuration.XPCmdShellEnabled.ConfigValue = 1
$server.Configuration.Alter()

You can automate with normal batch and sp_configure

I hope this helps someone find an answer while automating setting up facets, it would have saved me some time.

Replace AdHocRemoteQueriesEnabled for your answer instead of my working example below "remote access".

Similar to you I was looking for a way to automate, my case was RemoteAccessEnabled instead of installing the manager on any target machine I wanted to setup as a master. The above special keyword didn't even register in a search.

sp_configure Transact-SQL statement:

exec sp_configure "remote access", 1

also:

remote query timeout
remote proc trans

HTH someone