Blog Post

SQL Server Save Time Managing Multiple Servers With Central Management Servers

,

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.

access registration wizard

register new central server

Then optionally create a group for your server registrations to live under. I like to do this to keep things tidy.

access server group wizard

register new server group

Then add your server registrations to the new group…

access server wizard

register new server

One thing to note here is SQL Server will stop you from registering the Central Management Server instance as a server on itself…

registration error

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…

registration success

Once you’ve setup a few servers you can start to issue queries against them as a group…

new group query

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

results

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.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating