Powershell for CMS

Question

Post reply

Powershell for CMS

psred

SSCrazy

Points: 2049
More actions
April 11, 2017 at 2:38 pm

#337047

Is there a way we can run a powershell script that use tsql to Insert the select output into SQL table across Central management Registered server list.

Viewing 4 posts - 1 through 3 (of 3 total)

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

maudzedunjp Old Hand Points: 312 More actions · Answer 1

maudzedunjp

Old Hand

Points: 312

April 12, 2017 at 5:10 am

#1937800

Anybody know? Please, need you help too

Sue_H SSC Guru Points: 90860 More actions · Answer 2

maudzedunjp - Wednesday, April 12, 2017 5:10 AM
Anybody know? Please, need you help too

This article is pretty close to what you are looking for. It walks through using the list of servers registered servers in your CMS server and running Powershell commands against those:
Registered Servers and Central Management Server Stores

Sue
.

mrrobsewell SSC-Addicted Points: 496 More actions · Answer 3

Hi,

assuming you have the latest SSMS installed https://sqlps.io/dl then you will have the SQLServer module. (if you don't you probably have the SQLPS module which will work but better to use the latest version. You will be able to get the sqlserver module separately very soon from the PowerShell gallery)

Anyway, I digress

With the sqlserver module you will have a PSDrive. If you run Get-PS-Drive you will have a SQLSERVER:\ drive which you can treat like a directory
so you can cd SQLSERVER:\
if you ls (or dir or gci or Get-ChildItem)
you will see a SQLregistration
If you cd to that and ls then you will see Central Management Server Group
if you cd to that you can then do something like
$Instances = Get-item
(You may want to navigate to a folder if you have them)
Then if you examine $Instances using
$instances | gm
you will see that it is a collection of Microsoft.SqlServer.Management.RegisteredServers.ServerGroup objects

Now the world is your oyster
you could
$query = @"
YOUR QUERY HERE
"@
foreach($I in $instances.name)
{
Invoke-sqlcmd2 -serverinstance $I -database DBNAME -query $query
}

or
$query = @"
YOUR QUERY HERE
"@
foreach($I in $instances.name)
{
$srv = New-object Microsoft.SQLServer.Management.Smo.Server $i
$SqlConnection = $srv.ConnectionContext
$SqlConnection.StatementTimeout = 8000
$SqlConnection.ConnectTimeout = 10
$SqlConnection.Connect()
$Results = $SqlConnection.ExecuteWithResults($Query).Tables
$SqlConnection.Disconnect()
}
However you are saying that you are inserting data so I would say lets bulk copy it in

your object needs to be of a data table type and then
foreach($I in $instances.name)
{
$sqlserver = $i
$database = ''
$table = ''
$batchsize = 5000

# Build the sqlbulkcopy connection, and set the timeout to infinite
$connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;"
$bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock)
$bulkcopy.DestinationTableName = $table
$bulkcopy.bulkcopyTimeout = 0
$bulkcopy.batchsize = $batchsize
$bulkcopy.WriteToServer($datatable)
$datatable.Clear()
}

or just use the dbatools module from https://dbatools.io or by running install-module dbatools

and then you can

$dt = YOUR DATA | Out-dbadatatable (something like get-content FILENAME, or import-csv FILENAME)

foreach($I in $instances.name)
{
$dt | write-dbadatable -sqlserver $I -InputObject $dt -Database mydb -Table customers -KeepNulls
}

https://blog.robsewell.com Its where I blog
SQL Community Slack Channel https://sqlps.io/slack
The Best PowerShell Module for the Modern SQL DBA https://dbatools.io
Data South West User Group http://sqlsouthwest.co.uk/[/url]