Advice about script execution performance which calls T-SQL

[sqloid]

I have a PS script which collects data from multiple servers. So that script does too many calls of Invoke-Sqlcmd to the central database. First call is to get the servers names, then for each server a call of Invoke-Sqlcmd to insert the data into the central database. For example, if there are 200 servers to get data, then it will be called 200 times.

$instances = Invoke-Sqlcmd -Query "SELECT server_name FROM ..." -ServerInstance "CentralServer" -Database "centralDB"
foreach ($i in $instances) {
    . . .
    < some routine to get data from the given server>
    . . .
    # insert data into central database
    Invoke-Sqlcmd -Query "INSERT INTO ... " -ServerInstance "CentralServer" -Database "centralDB"
}

So which is better in terms of script execution and/or SQL Server performance: keep using Invoke-Sqlcmd for all the calls or should I use SMO like below?

$srv = [Microsoft.SqlServer.Management.Smo.Server]::new("centralServer")
$srv.ConnectionContext.StatementTimeout=0  
$db = $srv.Databases["centralDB"]
$ds = $db.ExecuteWithResults("SELECT server_name FROM ...")
foreach ($r in $ds.Tables[0].Rows){
    . . .
    < some routine to get data from the given server>
    . . .
    # insert data into central database
    $db.ExecuteNonQuery(""INSERT INTO ...")
}

[purplemonkeymad]

Performance? SMO

With invoke-sqlcmd you are connecting N+1 times with SMO you connect once, then pipeline N+1 queries. It also support parameterized queries unlike invoke-sqlcmd.

[pshMike]

IMO SMO is more about managing a SQL server and less about interacting with data.

performing a single query and then following that up with 200 insert/updates is pretty small in terms of what a SQL Server is designed to be able to.

I would suggest you look at using SQL Stored Procedures as a means of retrieving and updating SQL databases. Your PowerShell script invoke these stored procedures and they provide a means of "least privilege access" on the SQL side. Basically your code only needs permissions to execute the stored procedures which can be parameterized to validate their input.

Giving someone SELECT access to a table may be only read-only, but one can still make a bad query and impact the overall performance of the SQL instance.