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.
Best Answer
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.