Keep up to date with CMS and registered servers, using PowerShell


Working for a large company with multiple SQL instances can be a lot of fun, a challenge, overwhelming, a headache, a great motivator for automation. You choose.

For me automation is the best answer here, working with hundreds of instances and 1000s of databases is impossible, unless you can work centrally and collect everything you need into one easily accessible location. There are many great ways to manage this automation, PowerShell, VBScripts, SSMS, SSRS etc. etc. Microsoft have been improving the lives of DBAs since day 1 and every version released introduces new ways of working.

One of the best tools they introduced was way back in SQL 2008, when they introduced us to SQL Central Management Servers (CMS). This allowed us to run single commands against multiple databases, and also have quick access to all of our managed instances.

When you are working with a small number of instances, manual updating is easily manageable. When you are managing hundreds of instances, not so much. I keep a track of all of my live ‘managed’ (we have many unmanaged remote site databases where offers of help are flatly refused) in a homemade inventory database, and occasionally I need to update my CMS with the correct list of live databases. The script below was written for just this task. I keep my list arranged by version, but it can easily be broken down into whatever category you require (prod \ non-prod, site etc.). When it comes to updating my CMS, I can simply open up PowerShell and run this script, et voila.

The Code
#Using SQLPS? make it so, number 1!
Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

#set a top level location for selecting instance list.
#If done at lower levels errors are generated (e.g. in SQLRegistration).
Set-Location SQLServer:\

#get all of the servers from inventory database
$str = "SELECT
WHEN CHARINDEX('8.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2000'
WHEN CHARINDEX('9.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2005'
WHEN CHARINDEX('10.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2008'
WHEN CHARINDEX('10.5', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2008 R2'
WHEN CHARINDEX('11.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2012'
WHEN CHARINDEX('12.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2014'
END as instance_version
SQLMonitor.dbo.instance s"

$results = Invoke-Sqlcmd -query $str -ServerInstance "SERVER1"

#make sure we are on the right server and in the right place
Set-Location 'SQLSERVER:\SqlRegistration\central management server group\SERVER1';

#clean up all existing entries
dir -Recurse | Remove-Item -force;

#go through each new entry
foreach($result in $results)
#get our values in vars
$regserver = $result.instance_name;
$reggroup = $result.instance_version;

#check that the group exists or not
if(-Not (Test-Path -LiteralPath "SQLSERVER:\SqlRegistration\central management server group\SERVER1\$($reggroup)"))
#couldn't find the path, let's add the top level directory
Set-Location -LiteralPath "SQLSERVER:\SqlRegistration\central management server group\SERVER1"
New-Item -Path $reggroup -ItemType "Directory";
#add the new registration
New-Item -Name $(encode-sqlname $regserver) -path "SQLSERVER:\SqlRegistration\central management server group\SERVER1\$($reggroup)" -ItemType registration -Value ("Server=$regserver ; integrated security=true");

Thanks for reading, hope this is useful.

This entry was posted in Powershell, SQL Server and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s