Originally Posted 8/24/2014

Before I dive into the next component in the Froogle DBA’s toolbox, I wanted to give you all an idea of what’s to come.  In the original Froogle DBA plan on which my SQL Saturday presentation was built there are three component involved in automatically documenting the managed environment in Central Management Servers (CMS).  I already went over Extended Properties and showed you how to add them to a database.  I spoke a little about the advantage of using Extended Properties to document your environment.  I even talked about using CMS to query the Extended Properties to answer some common questions.

While I was procrastinating from working on my presentation it dawned on me that not everyone will want to run the other piece of the Froogle DBA trinity, Management Data Warehouse (MDW).  A lot of people will already have monitoring tools in place, and MDW is probably the hardest piece to scale and manage.  So I thought, how could people take advantage of tighter integration between CMS and Extended Properties without having to run MDW.  So I spent some time today an through together a PowerShell script that can be used to populate groups in CMS based on Extended Properties you have defined on your databases.

The idea is that you will have a group inside of CMS that lists all the Instances of SQL Server you manage.  This script will query all the Extended Properties defined at the database level on these instances.  It will then look for those that start with a specific Prefix (the default is “CMS “).  It will then build a group for each of these properties under the root of the CMS, removing the prefix,  So, for example the Extended Property “CMS Application” would create a group in CMS called “Application”.  It will then build a sub-group for each unique value defined for that Extended Property.  Then lastly it will register all the instances of SQL Server that had that value under the sub-group.  For example, if you have a database on Instance1 and another on Instance2 with the Extended Property “CMS Application” set with the value of “Application 1” you would see the results in the following picture.

Now I need to fully disclose that this is a script that I have not run in production.  So, please verify this in a test environment before turning it loose on production.  And, I would be very interested on any feedback you might have on the script.  Did you find it useful?  Did you need to modify it to fit in your environment?  Have ideas for enhancements?

Well, I guess it’s time to reveal the script.

#=========================================================================
# Filename:     PopulateCMSFolders.ps1
# Created on:   20140824
# Created by:   John Greeson
#
# Description:  This script will query all the databases belonging to
#               instances in the "Instance List" group and look for 
#               Extended Properties starting with "CMS ".  It will then 
#               build supporting folders in CMS based on those variables if
#               they are missing.  It will empty all the folders it found
#               and then repopulate them with the list of subfolders and 
#               instances if found in the query of Extended Properties.
#
# Requirements: Inorder for this script to run the CMS Server needs to be
#               registered in SSMS by the user running the script on the 
#               machine it will run on.  This means if you want to automate 
#               the task you will need to determine what windows account
#               the automated task will run as, log onto the machine it 
#               will run as as that user, and register the CMS Server in 
#               SQL Server Management Studio.
#
#  The MIT License (MIT)
#
#  Copyright (c) 2014 John Greeson
#
#  Permission is hereby granted, free of charge, to any person obtaining a copy
#  of this software and associated documentation files (the "Software"), to deal
#  in the Software without restriction, including without limitation the rights
#  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
#  copies of the Software, and to permit persons to whom the Software is
#  furnished to do so, subject to the following conditions:
#
#  The above copyright notice and this permission notice shall be included in
#  all copies or substantial portions of the Software.
#
#  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
#  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
#  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
#  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
#  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
#  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
#  THE SOFTWARE. 
#
#=========================================================================


#=========================================================================
#                          VARIABLES TO CHANGE
#=========================================================================

# Central Management Server
$CMSInstance = "SQLMON";

# Master List of Instances
$MasterInstanceList = "Instance List";

# Extended Property Prefix
$Prefix = "CMS ";

#=========================================================================
#                                THE MAGIC
#=========================================================================

Import-Module SQLPS -DisableNameChecking;

# Create data table to store Extended Properties
$table = New-Object system.Data.DataTable “ExtendedProperties”
;
# Add Columns to data table
$col1 = New-Object system.Data.DataColumn Instance,([string]);
$col2 = New-Object system.Data.DataColumn Connection,([string]);
$col3 = New-Object system.Data.DataColumn Database,([string]);
$col4 = New-Object system.Data.DataColumn Name,([string]);
$col5 = New-Object system.Data.DataColumn Value,([string]);

$table.Columns.Add($col1);
$table.Columns.Add($col2);
$table.Columns.Add($col3);
$table.Columns.Add($col4);
$table.Columns.Add($col5);

# Set path to the CMS list containing the master list of Instances
$pathToListOfInstances = "SQLSERVER:\SQLRegistration\Central Management Server Group\" + $CMSInstance + "\" + $MasterInstanceList;

# Get list of instances
$instanceList = get-childitem $pathToListOfInstances -recurse;

# Gather All Extended Properties
foreach($instance in $instanceList)
{
    $instanceName = $instance.Name;
    $instanceConnection = $instance.ServerName;

    $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $instanceConnection;

    foreach($database in $server.Databases)
    {
        foreach($property in $database.ExtendedProperties)
        {
            $row = $table.NewRow();
            $row.Instance = $instanceName;
            $row.Connection = $instanceConnection;
            $row.Database = $database.Name;
            $row.Name = $property.Name;
            $row.Value = $property.Value;
            $table.Rows.Add($row);
        }
    }
}

# Set path to the CMS root
$pathToCMSRoot = "SQLSERVER:\SQLRegistration\Central Management Server Group\" + $CMSInstance;

# Conditionally populate groups
$rootGroups = $table | where Name -like $($Prefix + "*") | Group-Object -Property Name;
foreach($rootGroup in $rootGroups)
{
    $groupName = $rootGroup.Name;

    # Strip off the tag from the Extended Property
    $firstLevelGroup = $groupName.Substring($Prefix.Length);

    # Define path to the first level group
    $groupPath = $pathToCMSRoot + "\" + $firstLevelGroup;
 
    if(!(Test-Path $groupPath))
    {
        mkdir $groupPath;
    }

    # Empty out groups we manage
    Remove-Item $($groupPath + "\*") -Recurse;

    # Create Subgroups
    $subGroups = $table | where Name -eq $groupName | Group-Object -Property Value;
    foreach($subGroup in $subGroups)
    {
        # Define path to the first level group

        $groupPath = $pathToCMSRoot + "\" + $firstLevelGroup + "\" + $subGroup.Name;

        mkdir $groupPath;

        # Add instances to subgroup
        $instances = $table | where Name -eq $groupName | where Value -eq $subGroup.Name | sort-object -Property Instance -Unique;

        foreach($inst in $instances)
        {
           $con = $inst.Connection;
           New-Item -Name $inst.Instance -path $groupPath -ItemType Registration -Value ("Server=$con; integrated security=true");
        }
    }
}