Blog

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

This is useful for fast-changing shops where there’s a lot of servers added and removed.  At my last company, we had a hard enough time telling everybody when we’d finished adding a new server, or what the new server’s name was, or when we didn’t need to look at a server anymore.

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.

How to Set Up 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:

Registered Servers window showing a CMS

Registered Servers window showing a CMS

In that above screenshot, I 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.

CMS 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.

I hate this limitation.  It means the CMS is nothing more than a centralized text file list of servers.  But it’s what we’ve got, so let’s get over it.  Either you can use a CMS, or you can’t.  Those of you who can, keep reading.

Next Drawback: Group Management Won’t Apply to the CMS

When you pick your Central Management Server, it needs to be a server that you won’t need to run multi-server queries against.  In the screenshot below, my CMS is the server P-SQL20081, and I’m trying to register P-SQL20081 as one of the registered servers:

Cannot Register the CMS Server In Itself

Cannot Register the CMS Server In Itself

To understand why this is an issue, you have to understand what CMS registered server groups are useful for: multi-server queries and policy-based management.  Say I have groups for Development, Lab, QA and Production.  When I want to run a multi-server query against all of my development boxes, or I want to evaluate a policy against all of them, I right-click on the Group D registration and click New Query or Evaluation Policies:

Taking an Action on a Registered Group

Taking an Action on a Registered Group

The problem is that if I can’t register the server P-SQL20081 inside Group D, then I can’t include it in group queries or policy-based management for that group.

Furthermore, if I right-click on P-SQL20081 and click New Query, I do get a group-execute query – but it does not include the CMS.  The query only executes against all of the registered servers, and since you can’t include the CMS as a registered server in its own server lists, it’s effectively outside of all management groups.  Therefore, when choosing a CMS, choose carefully – it needs to be a somewhat highly available server, since all of your DBAs will be relying on it for a server list, but at the same time, it can’t really be managed the same way, so it’s almost a throwaway.

My solution was to add a separate instance on a development box just to be my CMS – MyDevServerName\CMS.  That way, the box was reliable, but the instance doesn’t store any databases.

More About SQL Server’s Central Management Server

Here’s some more articles that take advantage of the CMS:

↑ Back to top
  1. Does it support registering older SQL Server instances in the CMS? 2005 or 2000?

  2. Yes, it does support registering older SQL Server instances in the CMS, and it even supports running multi-server queries against those instances. Of course, your query has to be compatible with all of the versions of SQL that you’re querying. For example, SELECT @@SERVERNAME will work great, but if you try to query new DMVs on an old SQL 2000 box, the query will fail.

    Same thing with case-sensitive servers, actually: if any of the servers in the multi-server query are set up with case-sensitive collation, then the multi-server query syntax has to be in the correct case.

  3. Hi,

    I’m having trouble starting the Central Management Server. I tried opening the Configuration tab in the its Properties, but everytime i do, i get the error “Failed to retrieve cluster name fom database. Reason: Database access failure”. Would you know how to fix this? Also, i’m wondering.. we are using different data sources (per client), is this possible in Crystal Report Server 11?

  4. Any updates in the near future coming from MS about allowing SQL Logins or capability for editing the Windows authentication credentials?

    We do have SQL Servers on different domains/networks and it is one of the drawbacks that really damp this awesome tool!

  5. Frank – I haven’t seen any, no.

    Mark – sorry, I don’t know anything about Crystal. The other error sounds like you can’t connect to the CMS.

  6. Is there any way to add lots of servers to a CMS without registering them all manually?

    • I haven’t tried this, but you might be able to manually insert records into the MSDB tables for the CMS. That’s obviously unsupported though.

    • Hi Iris,

      I checked this blog late else could send this infor a little earlier. So using below method we can manually add servers in CMS. I have tested it multiple times and its working for me without any problem.

      – -> create the CMS server group and register any one instance .
      – -> right click on the group >> tasks >> export >> CMS.regsrvr
      – -> open this file in SSMS
      – -> find string : .You will see all instances
      registered .copy the same code and paste it below with the new instance name .
      – -> then you will see a block of code like below , starting from and ending
      with :

      /RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test/RegisteredServer/abchaudh\ASLAN

      /RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test

      abchaudh\ASLAN

      abchaudh\ASLAN
      false
      -2039837
      DatabaseEngine
      server=abchaudh\ASLAN;trusted_connection=true;pooling=false;packet size=4096;multipleactiveresultsets=false
      PersistLoginName

      – -> modify this code with the instance name.
      – -> repeat for as many instances as you need to .
      – -> save this file and import it (the way we exported it)
      – -> You will see all the instances showing up in the CMS .

      Regards
      Gurpreet Sethi (GURSETHI)

  7. I have just setup my CMS, and I know that I can run a search of one query through all of the servers.

    However, now I want to create a job that runs that same search query through the servers, every morning.
    I can create a job like usual, but it won’t run it through the CMS, how can I get that job to run the search query through all the servers?

    Thanks

  8. Brent, we have been able to successfully add the CMS server itself to the list of registered servers for group management by simply using its IP address as the host name for the registered server config. The display name can be anything you want (within reason), so this works perfectly (at least for us). We have yet to run into any reason why MS would stop you from adding the CMS server to the list, so if you, or anyone else knows of a reason that it would be dangerous to include the CMS server in group management, please let me know. Otherwise, enjoy the tip!!

    -Patrick

    • It’s definitely not dangerous – I’ve just had hit or miss results when adding itself to the list. Sometimes I get a warning saying I’m not allowed to do that. I’ll dig deeper into that to find out what’s going on. Thanks!

    • Thanks for the tip Patrick! That trick worked for me. I actually used the FQDN\InstanceName for a registered server under the same named CMS that was registered using the ShortHostName\InstanceName syntax. By having the these names in the registration properties slightly unique, the issue is resolved.

  9. Can’t we register SQL Server in CMS(Central Mgt Servers) using SQL Server authentication

    • Ravindar – no, sorry, as I stated in the post, it’s only Windows authentication.

      • One workaround for the no-SQL-logins limitation is to create a local Windows account on the stand-alone SQL Server with the same username and password of the domain at you’re connecting with. Give the newly-created account access to the SQL Server and you’ll be able to connect using Windows Authentication. Obviously this requires you to keep your password manually updated, and some SysAdmins may not allow this, but it does work.

  10. This tool has saved a lot of time, thank you!

    Unfortunanlty the machine that my CMS is installed on is starting to die and I would like to preserve the effort of entering ~900 instances.

    How can I easily migrate this tool? Can I just backup/restore master on another instance somewhere?

    I’m an accidental DBA and so am a little unfamiliar with digging deep into the bowels of this tool.

  11. Thanks for the tip! I was able to save the CMS by copying the data from these msdb tables:

    sysmanagement_shared_server_groups_internal
    sysmanagement_shared_registered_servers_internal

    Once the data from these tables was copied everything worked just fine.

  12. Does anybody have answer to Jon’s question?

    I have just setup my CMS, and I know that I can run a search of one query through all of the servers.

    However, now I want to create a job that runs that same search query through the servers, every morning.
    I can create a job like usual, but it won’t run it through the CMS, how can I get that job to run the search query through all the servers?

    • You can’t do that through a job. You’ll need to write PowerShell scripts to leverage the CMS for things like that.

      • CMS is fire and forget, your query will either work at destination or not. CMS does not care it will just go onto next so you don’t want to set up any critical tasks using it (or if you do make sure your output shows a fail). Also you can’t output results to a central table (unless you have linked servers set up – prob not worth doing just for this though)

  13. here i included my local instance as configserver thru named pipes and it was included in the server group with different registered server name.
    Config server: Bhavyaa
    created group: local instance and added named instance thedn after when i tried to add config server.it gave error.
    when i tried to enlist with differenet register server name and named pipes. it added successfully.

  14. Hi Brent! I love your blog and it has been a great resource for me in my first SQL Server DBA position (I was a developer and then a systems/DB2 DBA admin before this). I just wanted to let you know, in case you wanted to include an update in your post, that Microsoft’s Official Course for SQL Server 2008 R2 walked us through a lab that adds the CMS to a server group. This contradicts your second drawback, which is great because I don’t want to install another instance just to use CMS :-)

  15. I am trying to issue a query to a group of registered Servers in the CMS. Now this query is actually designed to fetch SQL Agent Job failure result. Is it possible to log the out put to a database table on the CMS Server instance.

    Please help me find a solution to this issue!

    -Sid

  16. Hi All,

    We can use SQL authentication from SQL 2012 onwards.

    I have installed CTP version of SQL 2012 and have tried to configure servers with SQL authetication.

    Regards,
    Dilip

    • Dilip – you can use SQL authentication to connect to the CMS, but you can’t store SQL authenticated servers inside the CMS registered servers list. (Just tested myself with SQL 2012 RTM.)

  17. Hi Brent,

    Thank you for the insightfull blog, but I haven’t been able to add a new server to the CMS. Is it because I don’t have Kerberos delegation enabled for my account in the AD?

    Regards, Suzanne

    • Suzanne – well, it depends on the error message you’re getting. What’s the exact one you get? And is there someone else in your organization who can add a server to your CMS? Are there any error messages in the SQL Server’s error logs?

  18. Hi Brent,

    Today one of my colleagues (Patrick de Jong) told me it is possible to add the CMS server in order to run multi-server queries. the trick is to register the CMS using the port number. I tested and it works just fine. I will blog about it soon.

  19. Pingback: How to include your Central Management Server in your CMS registered Servers group

  20. Pingback: Workaround to Register an Instance from Another Domain in Central Management Server | Nick Xu's SQL Server Blog

  21. Pingback: Workaround to Register an Instance Out of Your Domain in Central Management Server | Nick Xu's SQL Server Blog

  22. I have different server groups in different domains the at were previously accessible. Due to security improvements the logins to these groups have changed. Can I Change the authentication for different groups of servers?

  23. Disregard last post Didn’t see workaround post going to try that first….

  24. What if you want to delete the Central Management Server? I understand how to delete the Register Servers but how do you delete the actual Central Management Server?

  25. Pingback: Using Central Management as Documentation | Ken Wilson

  26. Pingback: T-SQL Tuesday #050 Automation | Boris Hristov's Blog

  27. Pingback: T-SQL Tuesday #050 Automation | Boris Hristov

  28. This is a very helpful post — even 6 years after it was originally posted!
    Thank you!

  29. When we build a new server for the organization part of the “induction” process for it (yes even servers follow an induction process here) is to register that database server with CMS. I love the simplicity of CMS because by interrogating the groups and tables that are created in the msdb database I can provide to the IT managers of the organization a clear picture of those servers that are in DEV, TEST, UAT and PROD groups. For those with the required privileges I also provide SSRS reports that connect to the list of servers dynamically (via a drop down in SSRS that is a list of unique server names pulled from “sysmanagement_shared_registered_servers” view) which allows managers to pull back performance stats via DMV’s etc for that specific server. This way each time a new server is “inducted” they pop up on the report list dynamically without any further work from myself. I only hope CMS functionality stays around for a while. ;)

  30. Anyone know, is there a 50 instance limit with 2012+? Cannot find it at MS site, but this is experience I heard from other DBAs of the 2008 version.

  31. Are you asking if there is a limit as to how many servers you can add to a Central Management Server? Currently I have 80 set up. It was my understanding there was no limit.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php