Central Management Servers give us a way to manage a collection of SQL Servers as one, a query executed against a Central Management Server will run against every server in the group.
You can designate any SQL Server instance to be a Central Management Server by following this process in SQL Server Management Studio…
First under registered servers create a new Central Management Server and specify and instance for it to run on.
Then optionally create a group for your server registrations to live under. I like to do this to keep things tidy.
Then add your server registrations to the new group…
One thing to note here is SQL Server will stop you from registering the Central Management Server instance as a server on itself…
If you need to do this you can get round it by changing the server name so it doesn’t match the one you set on the Central Server registration by something like using it’s IP address or fully qualifying it or using a different DNS entry, basically anything that will cause the built in string comparison to pass…
Once you’ve setup a few servers you can start to issue queries against them as a group…
The results will come back in a single resultset like they have been UNION ALL’d across servers with an additional field prefixed which is the name of the server registration the row has come from..
This can be really useful for management of multiple servers. Suppose you have a script that lists all databases that have overdue backups, You can run that script once on server group rather than having to connect to each server individually. As an example run…
SELECT @@ServerName
This can really save time as you get more and more instances to manage, for example people often have a set of morning check script they run on each instance when they come in to work, using this approach of running each script only once without having to connect to every instance manually can save a lot of time.