What is the differences between Central Management Servers and Master Server (MSX) / Targer Server (TSX) concepts in SQL Server? I know that MSX/TSX is related to SQL Agent and the basic idea is to run jobs from MSX on TSX (possibly many), but I thought this is also possible in CMS scenario (by using scheduled stored procedures). Is that so?
Add a comment
|
1 Answer
The MSX/TSX is indeed related to the SQL Agent, allowing you to schedule jobs on a master server and have those jobs pushed out to slave servers.
CMS is an extension on the registered servers concept and allows you register servers into the MSDB database of a single instance through the SSMS GUI. Once the servers are registered in the CMS you can logically group them by business unit, version, etc. Once you have them registered any DBA/user with permission to the instance can connect to the CMS and see the same grouping.
CMS does not allow you to schedule agent jobs like MSX does, but you are able to run a script through SSMS on servers grouped in the same folder in CMS. For example, if you highligh a subfolder in CMS and hit New Query it will connect to all the instances registered in that folder and run the script on each one. Below is an example script you could run; CMS will return the instance name and the user running the script as columns by default. You can also use CMS for grouping Policy Based Management evaluations and policies.
select @@VERSION as Verison
answered Feb 10, 2012 at 21:52
