THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Working with the SQL Server Powershell Provider and Central Management Servers from Powershell

I am a big fan of the Central Management Server setup that exists in SQL Server 2008, as well as how you can leverage this for the Enterprise Policy Management Framework that Laura Rubbelke (Blog|Twitter) created on Codeplex.  Today one of my DBA’s asked a question about how to leverage the list of servers in the CMS through PowerShell and I had to double back on my own code because in PowerGUI the SQL Server Powershell provider is not natively registered so I had to write some additional code to check whether or not the PowerShell Provider had actually been loaded or not before running code to return a list of the registered servers from the CMS.  The following code will validate whether or not the PowerShell provider for SQL has been loaded as a PS-Drive or not and load it if it is not loaded, then return a list of the registered servers from the CMS.

# Load the Snapins for SqlServer if they aren't currently loaded
if((Get-PSSnapin -Name SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null) 
{    Add-PSSnapin SqlServerCmdletSnapin100    }

if((Get-PSSnapin -Name SqlServerProviderSnapin100 -ErrorAction SilentlyContinue) -eq $null) 
{      Add-PSSnapin SqlServerProviderSnapin100    }

$CMSPath = 'SQLSERVER:\SQLRegistration\Central Management Server Group'
foreach ($RegisteredServer in (dir $CMSPath -recurse  | where-object { $_.Mode -eq "-" }))
{
    $RegisteredServer.Name
}

Published Saturday, October 09, 2010 12:45 AM by Jonathan Kehayias

Comments

 

The Rambling DBA: Jonathan Kehayias said:

This month’s TSQL Tuesday event is being hosted by Pat Wright (Blog | Twitter) and  the topic this

February 7, 2011 11:19 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement