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

  • Article

 

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!