Pros and Cons of a Central Management Server

Question

Pros and Cons of a Central Management Server

rummings

SSCarpal Tunnel

Points: 4575
More actions
January 27, 2014 at 7:38 am

#284793

We have a two node "Active\Active" 2008R2 SQL Server environment. We have seven standalone servers running SQL Server 2008R2 as well. We recently hired a new employee that is saying we defintely need a CMS system installed. I have never set one up in the past. Does anyone have good or bad things to say about this process. I have to discuss in a meeting in 3 hours. Thanks!!
Charlie

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

benjamin.reyes SSCertifiable Points: 5249 More actions · Answer 1

The only bad thing I can think of is how easy it is to run a query against all of the servers at the same time. But you should have internal controls to prevent that causing a problem. This can also be a good thing.

The good is having centralized server management that is easy to categorize and share between all of your people.

http://technet.microsoft.com/sqlserver/gg508902.aspx

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 2

The pros:

You can easily repeatably execute queries against all servers (except the CMS, though there's a workaround). This is very handy for administrators that try to standardize and manage servers as a group. This is a best practice.

Cons:

Can be an extra instance to host the CMS, but not required if you hack a bit.

You could force standarization or mis-administer things because you want it easier, but that's not really a CMS issue. the CMS just makes it easier.

Overall, not a reason not to do it. If you have a product like Red Gate's MultiScript, then you don't need it. There are a few other advantages with that product as well.

Disclosure: I work for Red Gate.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

So far from my personal use, the pros out weigh the cons. We have been using CMS for a very long time and have not found an issue.

-Roy

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 4

Roy Ernest (1/27/2014)
So far from my personal use, the pros out weigh the cons. We have been using CMS for a very long time and have not found an issue.

So, what is the plan if the CMS server goes down, other than doing a panic build of another CMS server? Not asking to be contensious. I'm asking because it seems like a single point of failure and was wondering if it would be setup to "flop" to a mirror or something if the server hurled for some reason.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

If it fails, its no big deal. It is not a critical system. All the information are already in each of your servers. It just makes my job easy to manage servers. I can still do it one by one if I have to.

-Roy

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 6

I believe you can export the CMS settings and save them. However, as Roy mentioned, not a big deal. It's just a place you connect that sends your queries to all the other servers in the group. Worse case, you do that manually.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 7

Thank you both. That's more inline of what I thought CMS should be for. What I've found is that a lot of people have written PoSH (interesting abbreviation 😉 ) to "automatically do backups (including Point-in-Time log backups) for all servers" or "automatically do index maintenance for all servers" from the CMS server and that just seems like a huge centralized single point of failure to me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736129 More actions · Answer 8

Jeff Moden (1/28/2014)
Thank you both. That's more inline of what I thought CMS should be for. What I've found is that a lot of people have written PoSH (interesting abbreviation 😉 ) to "automatically do backups (including Point-in-Time log backups) for all servers" or "automatically do index maintenance for all servers" from the CMS server and that just seems like a huge centralized single point of failure to me.

It is. I think a CMS is nice to ad hoc stuff or deployments, but for critical items, like backups, I want each server/instance to manage itself.

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 9

Steve Jones - SSC Editor (1/28/2014)
Jeff Moden (1/28/2014)
Thank you both. That's more inline of what I thought CMS should be for. What I've found is that a lot of people have written PoSH (interesting abbreviation 😉 ) to "automatically do backups (including Point-in-Time log backups) for all servers" or "automatically do index maintenance for all servers" from the CMS server and that just seems like a huge centralized single point of failure to me.
It is. I think a CMS is nice to ad hoc stuff or deployments, but for critical items, like backups, I want each server/instance to manage itself.

Whew!!! I was begining to think I was the only one that felt that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)