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:
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:
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:
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.
62 Comments. Leave new
Does it support registering older SQL Server instances in the CMS? 2005 or 2000?
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.
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?
Didnt anyone answer this? I am having exact same issue
Go to “View” – click “Registered Servers” or you can do a Ctrl+Alt+G
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!
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.
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)
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
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.
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.
Hello David, I’ve just tried out your workaround approach and it worked fine! Thanks very much for posting this!
Hello David,
I tried the workaround, but got “The login failed. The login is from an untrusted domain and cannot be used with Windows authentication”, do you have another workaround ? 🙂
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.
The data’s stored in msdb, not master, so you might be in luck. Buck Woody has described how to query the CMS tables – you might be able to copy just some of those:
http://blogs.msdn.com/buckwoody/archive/2009/04/20/listing-the-central-management-servers-programmatically.aspx
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.
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)
ps thanks Brent for the post, great as usual
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.
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 🙂
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
Sid – you can do that by running the query in PowerShell (or any coding language) instead of SQL Server Management Studio.
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.)
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?
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.
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?
Disregard last post Didn’t see workaround post going to try that first….
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?
Just delete all the registered servers. The CMS isn’t a separate service or anything – it’s just tables in MSDB.
This is a very helpful post — even 6 years after it was originally posted!
Thank you!
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. 😉
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.
Brian – probably best to just test this for yourself.
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.
What are the prerequisites for setting up CMS – Version of SQL Server etc
Bhupinder – they’re in the post above. Thanks!
I use a DNS alias to register my CMS host. That works well.
This might seem like an obvious question, i know backwards compatability is fine as long as multi server queries are syntactically ok across the versions, however how about using a 2008R2 CMS tp manage a 2012 instance? Cant think of anything off the top of my head that would prevent this but thought id throw it out there anyway.
hope to see you at SQL bits 2015!
Jay – that’s an interesting question, but I wouldn’t have the answer to that. If you’re facing that problem, your best bet will be to just try it. It only takes a matter of seconds to set up this scenario if you’ve got those servers already. (I don’t, thus the answer, heh.) Thanks!
I have setup a CMS to manager our MSSQL servers landscape successfully under my own ID authenticated as a Server admin person. We are a team of two with the same role of managing the servers and databases.
When my colleague login to the MSSQL server (same CMS instance) with his account he does not see the CMS setup. Any Ideas why?
Abbas – no, got me there.
When he brings up SQL Server Studio Management under Registered Servers can he right click on Central Management Servers and click on Register Central Management Server? Then under server name add the CMS?
I would have set up a service account and have created the CMS under that service account. Thus, if you want to run jobs against the CMS under that service account you can.
Hi, I have created a Connect Item in hope that Microsoft will add the ability to filter Registered Servers (that way you could run queries on a subset of instances instead of whole folders). https://connect.microsoft.com/SQLServer/Feedback/Details/1051746
One other annoying limitation of CMS is that you can only register instances of the database engine.
So in a shop with a lot of SSAS instances, you’re back to everyone working with their own localised list of registered instances.
I don’t think that it would be difficult for MS to enhance the functionality of CMS, and there’s already a Connect item for this, so it’s worth a vote.
https://connect.microsoft.com/SQLServer/feedback/details/760425/register-analysis-service-in-central-management-server
Hello,
I am working with CMS and maintaining compliance. I am trying to create and schedule job with a policy and check the role of availability replica on all servers listed in a server group(AG Servers- with Server Groups Test, Dev, Prod- Each listed with multiple servers). I am not sure about how to include the power shell script in a job step to refer back to apply the policy check (from SQL scheduled JOB) on all servers included in (CMS- Server- Server Group-Server groups- Server Instances). Need some power shell scripts to point to and if this is appropriate? I need to schedule some Job, policy check against the servers in a server group with many server groups in it.
Can you please help?
Thanks in advance!
Pal – for questions unrelated to a specific blog post, head over to https://dba.stackexchange.com. Try to summarize the question as clearly and concisely as possible. Good luck!
Dear Brent and team,
have you thought of implementing sp_blitz with the powershell “Invoke-PolicyEvaluation” method? (i.e. have a folder of .xml policy files to check against, instead of one big TSQL script that will become harder to maintain over time).
In any case, awesome site, resources and philosophies!
Cheers!
Martin – nope, haven’t thought about it at all. We don’t do PowerShell work. Check out SQLPowerDoc though: https://sqlpowerdoc.codeplex.com/
As far as registering the CMS server itself into a group….It’s a simple string check, so you can use a DNS alias, or, what I do for a server that uses a static IP, add the port:
MYSERVER, 1433
That is exactly how I handled it. I can execute against the CMS all day with a DNS listener.
By the way, Brent, I enjoyed your PASS 2017 keynote session immensely!
Thanks, glad you liked it!