Run A query on Multiple servers using CMS

• best_yunus

  SSC-Addicted

  Points: 409

  More actions

  December 29, 2012 at 4:55 pm

  #268791

  Hi ,

  I want to run a query on Multiple servers i am using CMS to run it on multiple on server.

  Now i want to automate this through job,powershell,sqlcmd ...and the result should export to CSV.

  Pls help.

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  December 31, 2012 at 12:10 pm

  #1572577

  Those requirements are a bit vague. Are you running a select statement? An update? What will your CSV look like?

  Here is some boilerplate PowerShell code to help you get you started interacting with your CMS.

  $serverGroupPath = 'SQLSERVER:\SQLRegistration\Central Management Server Group\<GroupName>\'

  $instanceNameList = dir $serverGroupPath -recurse | where-object { $_.Mode.Equals("-") } | select-object Name -Unique

  foreach($instanceName in $instanceNameList)

  {

  # do something against each server here

  }

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato