Got more than one DBA? Want to make your life easier? You might want to configure a Central Management Server.

A SQL Server CMS is just a central repository that holds a list of managed servers. Sounds simple - and it is - but it comes in handy, and it’s practically a requirement for a good policy-based management deployment.

In a shop with two DBAs, they both have their own desktops (plus maybe laptops) and each machine has its own list of registered servers. With a CMS, the list of registered servers is stored on the central SQL Server. When the DBA opens SQL Server Management Studio, they point at the CMS, and SSMS grabs the list of registered servers from there.

Central Management Server Tutorial Video

In this video, Brent Ozar explains the CMS concept and what it's good for.

http://tutorials.sqlserverpedia.com/SQLServerPedia-20081209-CMS.flv

For more tutorial videos like this, check out the SQL Server Tutorials page.

Configuring a Central Management Server

To configure it, open SSMS 2008 and go into the Registered Servers window. Right-click on Central Management Servers and you get options to set one up. From there, it’s basically the same as your local registered server list - only it’s centralized:

In that above screenshot, the user connected to a CMS on P-SQL20081\CMS, and that instance stores the list of SQL Servers. The list is initially empty - it doesn’t automatically detect all of the database servers in your enterprise - you just add servers and groups manually.

After you set it up from any workstation, then on any OTHER workstation, you can point SQL Server Management Studio at that CMS, and the list of servers is always in sync. Think of it as a server list repository.

Drawback: Windows Authentication Only, And Only Your Login

The CMS server list is just a list of server names: nothing more, nothing less. Authentication is not saved at all. When you connect to any server in the CMS list, your Windows authentication is used. You can’t save an override list of logins, like an SA login for a specific server in the DMZ.

DBAs in large shops administer databases all over the world, in lots of domains that don’t trust each other, and in DMZs, and the Central Management Server is useless here. We can set up multiple CMS’s, one in each domain, but that’s not exactly ideal.

Executing Queries on More Than One SQL Server

Right-click on a group of servers and click New Query. The query is executed against all of the servers in that group, and SQL Server prefixes a column in front of the result set showing what server each result set is from.

Manage Your Services Remotely

Another big advantage of CMS grouping all your servers together in a single place is that you can administrate the SQL Services on the target box easily. To do this right-click on the instance name. On the submenu you have a couple of service-related options. Under the Service Control option you can quickly Start, Stop, Pause, Resume or Restart your SQL Service. Of note, this action only applies to your SQL Engine service so if you have SQL Agent Service running (which relies on Engine) you may need to manually restart that service depending on how you have your services configured.

For a little more control over the services on the target system select SQL Server Configuration Manager from the menu selection. This opens your typical Configuration Manager console but difference being that it now shows you the services on the target server you selected. To verify this you can see the target server's name in parenthesis next to SQL Server Configuration Manager in the left hand window pane. From here you can look at and configure your various SQL Server Services such as the database engine, Reporting Services, SQL Server Agent or SQL Server Browser. Be aware, however, this remote management of configuration manager only works on SQL Server 2005 editions and above.