3

We have just recently come across the Central Management Server feature in SQL2008. We currently have a number of environments and think that this feature could really help us in running multiple instances queries. But before making the move to implement this to our production environment, would anyone with experience please help us with the following points?

  • Are there any best practices you recommend?

  • Are there any drawbacks/cons to consider if we decide to use the feature?

  • Any negative in creating a group consisting of both 2005 and 2008 servers?

  • And in regards to transactions, is this per instance or a whole group?

any reply would be very much appreciated.

Mbuyig
  • 31
  • 1

2 Answers2

2

There is no real best practice, but if you want to use a job server as well you should choose your CMS as a server which is a standard edition, if not you can even use a SQL express edition as your CMS.

If you are managing a multi server/instance environment, there are no real cons/drawbacks, this will enhance the way you can monitor & query your environment.Also if you have multiple dba's in your environment you can work together on the same CMS, and see the newly installed instances from your Collegue.

You can build your groups the way you like and want, I have environments where I have 2005 & 2008 & 2012 & 2014 together because they are production. The only thing that is a drawback with that is that some dmv's are different in 2005 then in 2008/2012/2014. So some multiserver-queries will fail if you don't make them 2005 compatible.

This is a transaction per instance, the result is returned as one result, but it will be a transaction for every instance.

I hope this answered all your questions.

Stijn Wynants
  • 1,792
  • 12
  • 18
1

Are there any best practices you recommend?

To my knowledge there are no best practices around CMSs.

Are there any drawbacks/cons to consider if we decide to use the feature?

If your CMS gets fairly large (100+ entries) it can take a few seconds (30-40) to load. Also multi server queries have their drawbacks. Multi server queries work great for updates or for displaying data, however you aren't going to be able to store your results into any type of table/temp table. You'd have to dump the results into a CSV and re-load if you want to do something like that. Also if you try to connect to more than 4-5 servers in a single connection it can take some time to finish. There is also a limit but I don't remember what it is. ~50 is the practical limit I think.

Any negative in creating a group consisting of both 2005 and 2008 servers?

No, I've done this and it works great as long as your queries work for both 2005 and 2008.

And in regards to transactions, is this per instance or a whole group?

As I alluded to above it's going to be per instance. No data travels between them.

Additional info

All that said I love CMSs. They are a wonderful way to keep a shared list of instances that the entire team can reference. Also if you are interested I have a script that will load a CMS from a table. One of the benefits to this is that you can add the same instance multiple times in different groupings. So one group is broken down by version (2005, 2008 etc) and another by SDLC stack (dev, test, prod) or any other format you can think of.

Kenneth Fisher
  • 24,307
  • 13
  • 63
  • 116