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-DbaSqlQuery, 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 sqlmatters.com, 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!

Get-DbaRegisteredServer

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

Add-DbaRegisteredServer

Adds registered servers to SQL Server Central Management Server.

Add-DbaRegisteredServerGroup

Adds registered server groups to SQL Server Central Management Server.

Export-DbaRegisteredServer

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

Get-DbaRegisteredServerGroup

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

Get-DbaRegisteredServerStore

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

Import-DbaRegisteredServer

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

Move-DbaRegisteredServer

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

Move-DbaRegisteredServerGroup

Removes registered servers found in SQL Server Central Management Server.

Remove-DbaRegisteredServer

Removes registered servers found in SQL Server Central Management Server.

Remove-DbaRegisteredServerGroup

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 πŸ‘

9 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.”

    regards
    roberto

  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

Leave a Reply

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