Click here to monitor SSC
  • Av rating:
  • Total votes: 20
  • Total comments: 0
Phil Factor

Registered Servers and Central Management Server Stores

28 August 2012

SQL Server Management Studio (SSMS)  has many features that make it particularly convenient for the database developer and DBA.  Registered Servers are typical: poorly documented, but essential for the busy DBA, since they are easily scriptable via TSQL or PowerShell as well as the SSMS GUI, to access groups of servers to perform a wide range of tasks.

The working DBA is likely to have a large number of servers to look after. Sure, you’ve got some production servers, but once you add in the test servers, development servers, staging servers, standbys and other odds and ends, you are up in the hundreds. I defy anyone to maintain or monitor that lot manually, by disconsolately clicking and tapping away in SSMS, Server by Stultifying Server. (SBSS). No, be realistic, you need help, and one of the first steps is to get used to using Registered Servers, and in particular, Central Management Servers. Suddenly, with the latter, you have a list that every person and process can share, with different ways of sorting and categorizing servers .

Why use Registered Servers?

If ever you need to perform actions on, or gather information about, a number of SQL Server instances, then you probably want to use Registered Servers. Whether you are using SSMS’s Object Explorer, executing Transact-SQL statements, performing Policy-Based Management, or maybe scripting everything in PowerShell, then it is a very effective way of working. There are certainly simpler ways of maintaining lists of database servers for a wide range of purposes, but none are this versatile. You can import and export lists, and perform operations on every list member, but what clinches it for me is that the same hierarchical list can be used from within SSMS, TSQL or from .NET SMO. Within the registered servers, there are, of course, the Local Server Groups, but these merely represent the Servers that a particular user has registered on a particular machine. The more useful type of registered server store is the Central Management Server Store. These stores can be shared between different users who have the correct roles assigned to them on the server. They can also be accessed by scheduled tasks and ad-hoc automated tasks. They are represented as tables on a designated Central Management Server. The only disadvantage of Central Management Serves is that SQL Server Security cannot be used since passwords are not stored, so automatically accessing servers outside the domain is more complicated in scripts and impossible from SSMS. For this, you'll need to use  Local Server Groups and synchronize them by hand.

Within SSMS, you can access a number of Central Management Servers.

The relationship between the two basic types of Registered Server list can be represented like this.

What is a Central Management Server

A Central Management Server does nothing more than store a hierarchical list of the names and Aliases of instances of SQL Server in tables within the server. It is just like a file hierarchy except that the directories are called groups. They can be saved as XML to a file, and loaded from a file. You can access or update the list via TSQL.

Actions that are taken in SSMS on Registered Servers, such as executing SQL statements, connecting to all instances, or viewing the SQL Server log files, act on all servers in the server group. This includes connecting and performing whatever actions you specify at the same time.

Within scripting, you can access the contents of the group, or all the contents under that group recursively. This means that you can, if you take care with the categorization of your servers, perform some operations, such as checking that backups worked, on all your servers, and perform other checks such as CPU usage only on production servers, or automatically put a day’s changes in the development servers into source control.

The Central Management Servers can easily be accessed from SMO and PowerShell. Both the Local Server Groups and the Central Management Servers can be accessed from SQLPS in PowerShell. There are two main groups in the ‘SQLSERVER:\sqlregistration\’ path

  • Central Management Server Group
  • Database Engine Server Group

The ‘SqlServer::SQLSERVER:\sqlregistration\Central Management Server Group’ contains the Central Management Server groups whereas the ‘'SQLSERVER:\sqlregistration\Database Engine Server Group' path contains the local Server groups.

If you are scripting, then the Central Management Server Group is likely to be visible to your scripts. For your local Server Groups to be visible, you have to have your scheduled tasks using your login and credentials. By using the Central Management server, you can set up a special login with the correct role to run the script, which is much more satisfactory.

Permissions to access Server collections

If you only need to connect to, and read, the server collections in a central management server, then you just need membership in the ServerGroupReaderRole for the server that hosts the list. Only members of the ServerGroupAdministratorRole role are allowed to edit the contents of central management server.

When you execute queries in SSMS for all the members of a Central Management Server group, each query will execute in the context of the user, by using Windows Authentication. The information can include connection information, but unlike the Local Server Groups, this isn’t currently used.

Where is the information, and how can you access it.

Centralized Management Server details about servers and groups are stored within the msdb of the Central Management Server in the ...
dbo.sysmanagement_shared_registered_servers_internal
 ... and the ...
dbo.sysmanagement_shared_server_groups_internal
... tables.

Information about local groups is held in a file called RegSvr.xml, held in one of a number of places depending on the OS and version of SQL Server
e.g. C:\Documents and Settings\[USERNAME]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSvr.xml
, C:\Users\[USERNAME]\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml, or C:\Users\[USERNAME]\AppData\Local\Microsoft\Microsoft SQL Server\100\Tools\Shell\RegSrvr.xml

 Accessing the information Via SQL

You can read information and modify existing information directly in SQL if you have access to the Central Management Server. Here is a routine to view the servers currently registered and the groups they belong to.

SELECT     

      TheGroup.name AS [Server Group],

      TheGroup.[Description] AS [Group Description],

      TheServer.name,

      TheServer.server_name AS [Server name],

      TheServer.[description] AS [Description]

FROM    msdb.dbo.sysmanagement_shared_server_groups_internal TheGroup

LEFT JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal TheServer

      ON    TheGroup.server_group_id = TheServer.server_group_id

WHERE TheGroup.server_type = 0 --only the Database Engine Server Group

AND server_name IS NOT null

ORDER BY [Server Group], [Server name]

 

You can, of course, update these tables directly, (they have constraints to prevent you doing anything silly), and for the seasoned DBA, this will be by far the easiest way of maintaining the lists. You can import a list of two hundred servers in milliseconds believe me. However, doing it via SSMS allows you to manipulate the hierarchy more easily, and gives you access to the file export and import facilities.

Here is a simple SQL Update statement to change a description of a group

UPDATE msdb.dbo.sysmanagement_shared_server_groups_internal

SET DESCRIPTION = 'List of servers that are polled for a backup task.'

WHERE NAME='TestCell'

You’ll notice that the description fields are NVarchar fields of 4000 characters. A lot of information about your servers can be stored here, and there isn’t much that can be done to restrain you, beyond appealing sense of relational purity, from packing in a sorts of information about the server, and using it to do advanced filtering of servers, once the mechanism of hierarchical groups becomes too constraining.

Here is a simple way of inserting lists of servers. You’ll have to imagine a whole long list of servers. Obviously, you have to specify what group you want them in rather than put them all in 'DevelopmentServer'.

 

DECLARE @CurrentServerGroup INT --id of the group where you want to place them

IF NOT EXISTS --does the DevelopmentServer group exist already?

     (SELECT * FROM msdb.dbo.sysmanagement_shared_server_groups_internal

        WHERE name LIKE 'DevelopmentServer') --should test only for same parent!

INSERT INTO msdb.dbo.sysmanagement_shared_server_groups_internal

    (name,Description, Server_Type, parent_ID, is_system_object)

    SELECT 'DevelopmentServer','all the servers under developers desks',0,1,0

     --server type 0=DatabaseEngineServerGroup, id=1,and it isnt a system object

SELECT @CurrentServerGroup= server_group_id --and remember the ID

FROM msdb.dbo.sysmanagement_shared_server_groups_internal

    WHERE name LIKE 'DevelopmentServer'

 

 

INSERT INTO msdb.dbo.sysmanagement_shared_registered_servers_internal

  (Server_Group_ID, name, Server_name, description, Server_Type)

SELECT @CurrentServerGroup, 'Dee', 'PhilFactor.com\SQLthumper', 'The Shared Development server', 0

UNION ALL

SELECT @CurrentServerGroup, 'Dozy', 'PhilFactor.com\SQLbasher','The integration server', 0

UNION ALL

SELECT @CurrentServerGroup, 'Mick', 'PhilFactor.com\SQLfighter','The sandbox', 0

UNION ALL

SELECT @CurrentServerGroup, 'Tich', 'PhilFactor.com\SQLbruiser','Test server', 0

Note that Microsoft don’t make this sort of method public, and may change the database tables in future releases. If you want a routine that will work in subsequent versions of SQL Server, you’re better off with using an SMO-based routine to populate and maintain the lists. However, if you export the list before you upgrade, then you are likely to be safe from losing your data.

Accessing a Central Management Server in SSMS.

You can connect to several Central Management Servers at once.

Most of the time, you’ll probably connect to one or more existing Central Management Servers. In order to connect to a central management server, you simply

  • Click on the View menu in SQL Server Management Studio, and click Registered Servers.
  • In the browser pane, right-click on ‘Central Management Servers’ and left click on the ‘Register Central Management Server …,’
  • When the dialog box appears, fill everything in, hit ‘save’, and you’ve registered the server.

Creating a Central Management Server in SSMS

If you haven’t got a Central Management Server, then you’ll need to designate an instance of SQL Server as a central management server in SQL Server 2008 or 2012 by using SQL Server Management Studio. You can even use SQLExpress for this server, though you wouldn’t be able to rum policy-based Management from it.

  • In the View menu In SQL Server Management Studio, click on ‘Registered Servers’ item.
  • In the Registered Servers Explorer, expand Database Engine, right-click Central Management Servers, point to New in the context menu, and then click Central Management Servers.
  • In the New Server Registration dialog box, register the instance of SQL Server that you want to become the central management server.
  • In the Registered Servers Explorer, right-click the central management server, point to New in the context menu, and then click New Server Group. Type a group name and description, and then click OK.
  • In Registered Servers, right-click the central management server group, and then click New Server Registration.
  • In the New Server Registration dialog box, register  instances of SQL Server that you want to become members of the server group.
  • After you have registered a server, the central management server will be able to execute queries against all servers in the group at the same time.

PowerShell Scripting with centrally-registered servers.

You can choose to use SQLPS or do without it when accessing servers. Here is a routine for listing all the logins for all the servers in a group without SQLPS. You will, of course, need to change the name to your CMServer, and change the name of the group from ‘MyProductionServers’ to the group you want a list of logins from!

#Load SMO assemblies
$CentralManagementServer = "PhilFactor.com\MyCMServer"
$MS='Microsoft.SQLServer'
@('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
     foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}

$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$My="$ms.Management.Smo" #
$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups[ "MyProductionServers" ].RegisteredServers|
   Foreach-object {new-object ("$My.Server") $_.ServerName } | # create an SMO server object
     Where-Object {$_.ServerType -ne $null} | # did you positively get the server?
       Foreach-object {$_.Logins } | #logins for every server successfully reached
           Select-object @{Name="Server"; Expression={$_.parent}}, Name, DefaultDatabase , CreateDate, DateLastModified   |
              format-table

If you wish to do an operation on all your servers, or all the servers in subgrpups of the group you choose , a slight modification will give you what you want

#Load SMO assemblies
$CentralManagementServer = "PhilFactor.com\MyCMServer"
$MS='Microsoft.SQLServer'
@('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
     foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}


$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)


$My="$ms.Management.Smo" #
$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].GetDescendantRegisteredServers() |
     foreach-object {new-object ("Microsoft.SqlServer.Management.Smo.Server") $_.ServerName } | # create an SMO server object
       Where-Object {$_.ServerType -ne $null} | # did you positively get the server?
          Foreach-object {$_.Logins } | #logins for every server successfully reached
             Select-object @{Name="Server"; Expression={$_.parent}}, Name, DefaultDatabase , CreateDate, DateLastModified   |
                format-table

If you use SQLPS, it becomes even easier, ( change the path 'SQLSERVER:\sqlregistration\Central Management Server Group\PhilFactorsCMS\Backup\’ to whatever your CentralMS group is

Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers
#now fetch the list of all our registered servers

get-childitem 'SQLSERVER:\sqlregistration\Central Management Server Group\PhilFactorsCMS\Backup\' -recurse |
Foreach-object {new-object ("Microsoft.SqlServer.Management.Smo.Server") $_.ServerName } | # create an SMO server object
     Where-Object {$_.ServerType -ne $null} | # did you positively get the server?
       Foreach-object {$_.Logins } | #logins for every server successfully reached
           Select-object @{Name="Server"; Expression={$_.parent}}, Name, DefaultDatabase , CreateDate, DateLastModified |
              format-table

…and you can use the same code to do the Local Server Group. All you need to do is to change the path for the get-childitem cmdlets.

You may be wondering to yourself ‘this is a lot of trouble just to get a list of all the logins’. If so, then pull yourself together and think. This is good for any server-based operation. In a previous article, I showed how one routine could be used for a large variety of tasks. If you use something like the routine I showed you then, with the use of centrally-registered servers, then there is no limit: You can script anything you can do in SSMS.

If you wish to export the entire list of centrally-managed servers, and their hierarchy, you can save it as an XML file from SSMS. You can even save it via PowerShell and SMO (I’d forget to do it via SSMS). This routine saves only the contents of the ‘MyGroup’ group and its groups. You’ll need to alter the name of the server and the serverGroup, of course.

#Load SMO assemblies
$CentralManagementServer = "PhilFactor.com\MyCMServer"
$MS='Microsoft.SQLServer'
@('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
     foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}

$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups[ "MyGroup" ].Export('e:\MyGroupcmsExport.xml', [ Microsoft.SqlServer.Management.RegisteredServers.CredentialPersistenceType ]::PersistLoginNameAndPassword)

"did that work"

No longer do you need to perform your routine tasks Server by Stultifying Server. (SBSS).

 

 

    Phil Factor

    Author profile:

    Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career. See also :

    Google + To translate this article...

    Search for other articles by Phil Factor

    Rate this article:   Avg rating: from a total of 20 votes.


    Poor

    OK

    Good

    Great

    Must read
    Have Your Say
    Do you have an opinion on this article? Then add your comment below:
    You must be logged in to post to this forum

    Click here to log in.
     

    Phil Factor
    Searching for Strings in SQL Server Databases

    Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

     View the blog

    Top Rated

    Searching for Strings in SQL Server Databases
     Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

    Continuous Delivery and the Database
     Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

    The SQL Server Sqlio Utility
     If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

    The PoSh DBA - Reading and Filtering Errors
     DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

    MySQL Compare: The Manual That Time Forgot, Part 1
     Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

    Most Viewed

    Beginning SQL Server 2005 Reporting Services Part 1
     Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

    Ten Common Database Design Mistakes
     If database design is done right, then the development, deployment and subsequent performance in... Read more...

    SQL Server Index Basics
     Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

    Reading and Writing Files in SQL Server using T-SQL
     SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

    Concatenating Row Values in Transact-SQL
     It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

    Why Join

    Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.