Pages

Saturday, August 29, 2015

Automatically Manage Server Registrations

To create, update, or drop server registrations, we can either use the system tables directly:

  • msdb.dbo.sysmanagement_shared_registered_servers_internal
  • msdb.dbo.sysmanagement_shared_server_groups_internal
Or we can use SMO and PowerShell to automatically maintain the Central Management Servers. For  example, three group levels will be created as shown below



The central SQL Server  is named SQL1. The database is named dba_utilities. The table called instances is used to store the instances that are automatically or manually discovered in your organization. The stored procedure called sel_registered_servers is used to show the list of servers and respective groups. Additionally, sel_registered_servers_to_drop is used to show the list of servers that are no longer in your organization.
 
-- table for instances
create table instances(
logical_name varchar(255) not null constraint instances_pk primary key
,physical_name varchar(255) not null
,support_code varchar(10) not null
,sql_version varchar(10) not null
)
go

-- insert instances
insert instances values('SQL1','COMPUTER3\SQL1','TEST','2012')
insert instances values('SQL2','COMPUTER1\SQL2','PROD','2008')
insert instances values('SQL3','COMPUTER1\SQL3','PROD','2008')
insert instances values('SQL4','COMPUTER2\SQL4','PROD','2012')
insert instances values('SQL5','COMPUTER3\SQL5','TEST','2012')
insert instances values('SQL6','COMPUTER3\SQL6','TEST','2014')
go

-- stored procedure to show registered servers
create proc sel_registered_servers
as
select
'Support Version' group1
,support_code group2
,sql_version group3
,logical_name
,physical_name
from instances
where logical_name <> 'SQL1' -- central server cannot be added 
union
select
'Version Support' group1
,sql_version group2
,support_code group3
,logical_name
,physical_name
from instances
where logical_name <> 'SQL1' -- central server cannot be added 
go

-- stored procedure to show obsolete servers
create proc sel_registered_servers_to_drop
as
select distinct name 
into #temp
from msdb.dbo.sysmanagement_shared_registered_servers_internal 

select t.name logical_name
from instances i
right join #temp t
on i.logical_name = t.name
where i.logical_name is null

drop table #temp
go

Output of sel_registered_servers



The PowerShell script can read the results of the stored procedures and hierarchically navigate through the directory tree and create, update, or drop server registrations
 
function Add-RegisteredServer ($group1, $group2, $group3, $logicalName, $physicalName)
{
    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList = @($group1, $group2, $group3)
    
    # navigate down the hierarchy

    foreach ($group in $groupList) 
    {
        if ($serverGroups.ServerGroups[$group] -eq $null)
        {
         $newGroup = New-Object Microsoft.SqlServer.Management.RegisteredServers.ServerGroup($serverGroups, $group)
         $newGroup.create()
         $serverGroups.refresh()
        }
        $serverGroups = $serverGroups.ServerGroups[$group]
    }  

    # create or alter the registered server

    if($serverGroups.RegisteredServers.name -contains $logicalName)
    {
        $oldServer = $serverGroups.RegisteredServers[$logicalName]

        if ($oldServer.ServerName -ne $physicalName)
        {
            Write-Host "$group1 $group2 $group3 $logicalName Altered"
            $oldServer.ServerName = $physicalName
            $oldServer.Alter()
        }
    }
    else
    {
        Write-Host "$group1 $group2 $group3 $logicalName Created"
        $newServer = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServer($serverGroups, $logicalName)
        $newServer.ServerName = $physicalName
     $newServer.Create()
    }
}

function Drop-RegisteredServer ($logicalName)
{
    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList1 = $serverGroups.ServerGroups.Name

    foreach ($group1 in $groupList1) 
    {
        $groupList2 = $serverGroups.ServerGroups[$group1].ServerGroups.Name
    
        foreach ($group2 in $groupList2) 
        {
            $groupList3 = $serverGroups.ServerGroups[$group1].ServerGroups[$group2].ServerGroups.Name

            foreach ($group3 in $groupList3) 
            {
                $oldGroup = $serverGroups.ServerGroups[$group1].ServerGroups[$group2].ServerGroups[$group3]

                if($oldGroup.RegisteredServers.Name -contains $logicalName)
                {
                    Write-Host "$group1 $group2 $group3 $logicalName Dropped"
                    $oldServer = $oldGroup.RegisteredServers[$logicalName]
                    $oldServer.Drop()
                }
            }
        }
    }
}

function Drop-RegisteredServerGroup ()
{
    # Drop group3

    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList1 = $serverGroups.ServerGroups.Name

    foreach ($group1 in $groupList1) 
    {
        $groupList2 = $serverGroups.ServerGroups[$group1].ServerGroups.Name
    
        foreach ($group2 in $groupList2) 
        {
            $groupList3 = $serverGroups.ServerGroups[$group1].ServerGroups[$group2].ServerGroups.Name

            foreach ($group3 in $groupList3) 
            {
                $oldGroup = $serverGroups.ServerGroups[$group1].ServerGroups[$group2].ServerGroups[$group3]
                
                if($oldGroup.RegisteredServers.Count -eq 0)
                {
                    Write-Host "$group1 $group2 $group3 Dropped"
                    $oldGroup.Drop()
                }
            }
        }
    }

    # Drop group2

    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList1 = $serverGroups.ServerGroups.Name

    foreach ($group1 in $groupList1) 
    {
        $groupList2 = $serverGroups.ServerGroups[$group1].ServerGroups.Name
    
        foreach ($group2 in $groupList2) 
        {
            $oldGroup = $serverGroups.ServerGroups[$group1].ServerGroups[$group2]
                
            if($oldGroup.ServerGroups.Count -eq 0)
            {
                Write-Host "$group1 $group2 Dropped"
                $oldGroup.Drop()
            }
        }
    }

    # Drop group2

    $serverGroups = $registeredServer.DatabaseEngineServerGroup
    $groupList1 = $serverGroups.ServerGroups.Name

    foreach ($group1 in $groupList1) 
    {
        $oldGroup = $serverGroups.ServerGroups[$group1]
                
        if($oldGroup.ServerGroups.Count -eq 0)
        {
            Write-Host "$group1 Dropped"
            $oldGroup.Drop()
        }
    }
}

# Main Program

$tarSrv = "COMPUTER3\SQL1"
$tarDB  = "dba_utilities"

try
{
    $server = New-Object Microsoft.SqlServer.Management.Smo.Server($tarSrv)
    $registeredServer = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($server.ConnectionContext.SqlConnectionObject)

    $tarCon = New-Object System.Data.SQLClient.SQLConnection("Data Source=$tarSrv;Initial Catalog=$tarDB; Integrated Security=True;")
    $tarCon.Open()

    $tarCmd = New-Object system.Data.SqlClient.SqlCommand("exec sel_registered_servers", $tarCon)   
    $instances = $tarCmd.ExecuteReader()

    while ($instances.Read()) 
    {
        $group1 = $instances.GetValue(0)
        $group2 = $instances.GetValue(1)
        $group3 = $instances.GetValue(2)
        $logicalName = $instances.GetValue(3)
        $physicalName= $instances.GetValue(4)
    
        Add-RegisteredServer $group1 $group2 $group3 $logicalName $physicalName
    }
    $instances.Close()

    $tarCmd.CommandText = "exec sel_registered_servers_to_drop"
    $instances = $tarCmd.ExecuteReader()

    while ($instances.Read()) 
    {
        $logicalName = $instances.GetValue(0)
    
        Drop-RegisteredServer $logicalName 
    }
    $instances.Close()

    Drop-RegisteredServerGroup

    $tarCon.Close()
    $tarCon.Dispose()

}
catch
{
    $e = $_ | select -ExpandProperty InvocationInfo
    $m = $_.Exception.Message.TrimEnd().Replace("'","") + ", " + $e.ScriptLineNumber.ToString() + ", " + $e.OffsetInLine.ToString()
    throw $m
}