managing central management server

SQL Server’s Central Management Server (CMS), first introduced in SQL Server 2008, “stores a list of instances of SQL Server that is organized into one or more central management server groups”.

It’s a super useful feature that not all DBAs know about. Since CMS data is stored in msdb and accessible via SMO, you can access it from SQL Server Management Studio or PowerShell modules like dbatools.

Central Management Server’s essential functionality includes:

Actions that are taken by using a central management server group act on all servers in the server group. This includes connecting to servers by using Object Explorer and executing Transact-SQL statements and Policy-Based Management policies on multiple servers at the same time.

I mostly use it as a visual repository of my SQL Servers. Prior to using dbatools and Invoke-DbaQuery, however, I did use CMS to easily execute code against a number of different servers.

Server grouping

The screenshot above is a sample representation of how SQL Servers can be divided into groups. I generally organize by department, but as ClΓ‘udio Silva pointed out, it’s useful to organize by version when performing migrations. I’ve done that as well.

And because a SQL Server can be listed multiple times within different groups, my buddy Brandon created an automated system that divided by both Application and assigned DBA. Too cool! In this example from, their servers are divided up by environment.

But wait. The screenshot also shows Registered Servers. What are those?

Registered Servers vs Central Management Servers

The article Registered Servers vs Central Management Servers lays out the differences in a straightforward manner.

Registered Servers

  • Connection data is stored locally in an XML file (passwords are encrypted) in the user path.
  • Because it is a local file with encrypted data, Registered Servers are local and only work for the user that created them.
  • Can be used with SQL Server Authentication or Windows Authentication.
  • Can be used for the database engine, SSAS, SSIS and SSRS.

Central Management Servers

  • Connection data is stored in a central SQL Server (in the msdb database), and can be used by other users.
  • Can be used with Windows Authentication only.
  • Can be used for the database engine only.

I work primarily in environments where Windows Authentication works for all of my servers, so I haven’t used Registered Servers in years. I also appreciate that my list of servers within CMS is backed up each day because it’s stored in msdb. And, it’s easily accessible through both SQL Server Management Studio and PowerShell.

dbatools commands

We have a number of commands to help manage CMS! We even updated our command names after this blog post came out,


Gets list of SQL Server objects stored in SQL Server Central Management Server.


Adds registered servers to SQL Server Central Management Server.


Adds registered server groups to SQL Server Central Management Server.


Exports Central Management Server registered servers and registered server groups to file.


Gets list of Server Groups objects stored in SQL Server Central Management Server.


Gets list of Server Groups objects stored in SQL Server Central Management Server.


Gets list of Server Groups objects stored in SQL Server Central Management Server.


Moves registered server groups around SQL Server Central Management Server. This could be useful during migrations.


Removes registered servers found in SQL Server Central Management Server.


Removes registered servers found in SQL Server Central Management Server.


Removes registered server groups found in SQL Server Central Management Server.

🌟🌟🌟🌟🌟 would use again

CMS is a longtime favorite of mine and I’d like to give a shoutout to Bryan Hamby for helping make dbatools commands compatible with Central Management Server.

In case you missed it in the sea of code above, you can pipe a collection of servers right from CMS. Here’s how you’d find all running agent jobs in your estate, if you stored all of your SQL Servers in the CMS on sql2008.

Too cool!

Happy piping,
- Chrissy

Edit: Someone asked what server should be designated as the Central Management Server. A few people on the SQL Server Community Slack said they have a dedicated instance, as do I.

I have a management server where I perform all of my work. If you’re interested in a dedicated instance but don’t have a license, it works on SQL Server Express Edition πŸ‘

18 thoughts on “managing central management server

  1. Pingback: Managing Central Management Server – Curated SQL

  2. Roberto Reply

    Hi Chrissy

    Very nice functions.
    Did you try to add a few hunderts registered server?
    I receive the following exception
    WARNING: [Get-DbaRegisteredServerGroup] Cannot access Central Management Server ” | Exception calling “Open” with “0”
    argument(s): “Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled
    connections were in use and max pool size was reached.”


  3. Jeff Reply

    I like to use CentralManagement as an input to retrieve all the versions of sql server in the enterprise…:

    Get-DbaRegisteredServer -sqlserver ‘CentralManagementServerName’ | Get-DbaSqlBuildReference -update | format-table -property * -AutoSize | Out-String -Width 500 | out-file c:\temp\dbabuildreference.txt

  4. Shawn Reply

    Is it possible to copy the servers of a group to another group?

    I tried to pipe a group into add-DbaCmsRegServerGroup and that didn’t bring the servers
    Get-DbaCmsRegServerGroup -SqlInstance localhost -group QA\ALL_QA | add-dbacmsregservergroup -sqlinstance localhost -group QA\ALL_QA_SSIS

    I tried to pipe the servers into Add-DbaCmsRegServer
    Get-DbaCmsRegServer -SqlInstance localhost -group QA\ALL_QA | Add-DbaCmsRegServer
    -SqlInstance localhost -Group QA\ALL_QA_SSIS

    Piping servers into Add-DbaCmsRegServer still had me enter a ServerName. If I provided one, then a single server of that name would be added.

    I ended up having to use SSMS to export a group, rename the group in SSMS and then import the file to add the group back with its’ old name.

  5. Paul H Reply


    I just downloaded the dbatools module specifically to work with the central management server commands, only I cannot find them at all. Have they been removed? If so, is it possible to get an older version of the tools that does contain these commands?

    • Chrissy LeMaire Post authorReply

      Thanks for your comment, Paul! I need to update this post. They are all there, but now have support for all Registered Server Types so they are have been renamed to RegServer in dbatools 1.0. Just remove the Cms part of the command. I’lll also update the post now.

  6. Paul H Reply

    Thanks for the quick response Chrissy, now it’s working! πŸ™‚

  7. swapnil Reply

    When i am using the file name like -path ‘c:\temp\export.xml’ it creates a folder export.xml and puts the autogenerated file sql2008-xxxxxx.xml to it.
    Is it possible to give name what i wanted instead of autogenerated?

  8. Frank Reply

    Hi Chrissy,

    How can I omit a subgroup? Neither -ExcludeGroup ‘Group\Subgroup’ nor -ExcludeGroup ‘SubGroup’ works.

    • Shawn Therrien Reply

      I just tested and exclude group isn’t working for me.

      Get-DbaRegisteredServer -SqlInstance localhost -group QA -ExcludeGroup QA\QA_DW
      still shows entries that list ‘QA\QA_DW’ for group.

Leave a Reply to Chrissy LeMaire Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.