- msdb.dbo.sysmanagement_shared_registered_servers_internal
- msdb.dbo.sysmanagement_shared_server_groups_internal
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
}