how to manage your SQL Servers with Central Mangement Server and Powershell

 

One of the features I like most with 2008 is Central Management Server. CMS introduces us with multi server management. However, it only shows the results in SSMS and does not save the result set. To get the result set in a SQL Server table, the only thing you need is a handy Power Shell script. Let me explain this script with a simple example.

I have my default instance ISILEFE01 registered as a Central Management Server and two instances registered as members, named as ISILEFE01\ISIL and ISILEFE01\EFE.

 

Now, let’s run a very simple query to get the versions of all SQL server instances registered under CMS.

 

That’s all what CMS can do. Further we will go with Powershell. Please review the powershell script below:

 

$CentralManagementServer = "ISILEFE01"
$HistoryDatabase = "PSDatabase"

function ResultInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedResult)
{

 $sqlQueryText = "INSERT INTO dbo.test  (servername1,servername2) VALUES('$EvaluatedServer', '$EvaluatedResult')"
 write-output $sqlQueryText
 Invoke-Sqlcmd -ServerInstance $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText

}

 

$sconn = new-object System.Data.SqlClient.SqlConnection("server=$CentralManagementServer;Trusted_Connection=true");

$q = "select name from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal];"

$sconn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn);
$cmd.CommandTimeout = 0;
$dr = $cmd.ExecuteReader();

 

while ($dr.Read()) {
 $ServerName = $dr.GetValue(0);
     
      $sqlQueryText = "select @@version"
     
      $selectResult=Invoke-Sqlcmd -ServerInstance $ServerName -Query $sqlQueryText
      [string]$a=$selectResult.column1

      ResultInsert $CentralManagementServer $HistoryDatabase $ServerName $a;
      
 }

 

$dr.Close()
$sconn.Close()

 

According to the script, I would like to get the results into a sql server table on my default instance, ISILEFE01. I created a database called “PSDatabase” and a table called “test” on my default instance, ISILEFE01.

The important point here is that the names of the SQL Server’s instances registered in CMS is written in a table called as “[msdb].[dbo].[sysmanagement_shared_registered_servers_internal]” . By running a select query to that table, I can retrieve the names of the sql server instances and then in a simple loop I can run any T-SQL query on all instances registered under CMS.

Lastly, in my main function I am calling a function to insert the values that I retrieve, to the “test” table under “PSDatabase”. Now pls check what is inserted in the test table:

Enjoy managing your SQL servers with Powershell!

Comments

• Anonymous
  August 17, 2012
  hello, Thanks for this great post! I tried your example and it worked  however, when I tried to a  different query with more columns, it did not work. I tried to modify it a little but I have no powershell or .Net knowledge so that did not work out. I would really appreciate it if you could tell me how to go about getting more result columns from CMS query result. Example: I want to get the results from this query in CMS 'select name, type_desc, is_disabled, create_date, modify_date from sys.server_principals' into a table. How do I go about modifying your query?

• Anonymous
  September 20, 2012
  Very useful. We actually manage our SQL Servers using PowerShell

• Anonymous
  June 11, 2013
  Excellent post !!

• Anonymous
  June 18, 2013
  I finally found an example that I understand.  How would you set up this script as a SQL Server Agent Job?

• Anonymous
  January 15, 2014
  Hello, Thanks for this post, i tried executing above queries but results returned are showing blank. Can you please help me on that

• Anonymous
  February 24, 2014
  @vikas, are you trying "select @@version" or another query?

• Anonymous
  March 12, 2014
  Add-PSSnapin SqlServerCmdletSnapin100 Add-PSSnapin SqlServerProviderSnapin100 $CentralManagementServer = "server" $HistoryDatabase = "SQLServerMetaData" function ResultInsert($sqlServerVariable, $sqlDatabaseVariable, $EvaluatedServer, $EvaluatedResult) { $sqlQueryText = "INSERT INTO dbo.CMSserverdata  (servername,version) VALUES('$EvaluatedServer', '$EvaluatedResult')" write-output $sqlQueryText Invoke-Sqlcmd -ServerInstance  $sqlServerVariable -Database $sqlDatabaseVariable -Query $sqlQueryText } $sconn = new-object System.Data.SqlClient.SqlConnection("server=$CentralManagementServer;Trusted_Connection=true"); $q = "select name from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]where server_group_id in (197);" $sconn.Open() $cmd = new-object System.Data.SqlClient.SqlCommand ($q, $sconn); $cmd.CommandTimeout = 0; $dr = $cmd.ExecuteReader(); while ($dr.Read()) { $ServerName = $dr.GetValue(0);      $sqlQueryText = "select @@version"      $selectResult=Invoke-Sqlcmd -ServerInstance $ServerName -Query $sqlQueryText      [string]$a=$selectResult.column1      ResultInsert $CentralManagementServer $HistoryDatabase $ServerName $a; } $dr.Close() $sconn.Close() **** For some reason it only insert the version as " Microsoft SQL server 2005" regardless of orginal version ***Pl note if i run against <10 it runs fine but if i run against >100 servers i am seeing this. Any help would be appreciated. Any idea why we are seeing this?

• Anonymous
  March 27, 2014
  Hi Gnach what do you get if you only run for MS SQL 2005? Could be regarding to the length the column and it could be truncated, one thing I would recommend you could try to convert the parameter to string.