You are here:Home » tsql » Multi server Query with Central Management Servers - SQL Server 2008

Multi server Query with Central Management Servers - SQL Server 2008

Before SQL Server 2008, when we need to gather all information and details related to a server or database level, we must run the script individually by connecting each SQL Server instance. But SQL Server 2008 came up and easy our work for that. It has introduced a new feature - Central Management Servers (CMS).

With Central Management Servers we can configure and register SQL Server instances with shared SQL Server instances. Then we run the query against all the SQL instance and get the details for all instances. Let's you demonstrate the same in details here.

1. How to open Central Management Servers?

Go to View --> Registered Servers 
or
press Ctrl + Alt + G.

2. How can register SQL Server instances in CMS?

Expand Database engine from Registered Servers. Right click on the CMS and click on Register SQL Server Management. A new screen will appear below,


In the above, I have registered SQL Server Denali instance, which will be shared SQL Server instance. Now I am creating a new SQL Server Group under CMS and then register SQL Server 2008 and SQL Server 2011 by right click on the group and then go to the link of registration and then go on the same way as I did for SQL Server Denali instance.


3. How can we perform multi server query against all SQL Server ?

Go on right click on Shared SQL Server instance under CMS and click on New Query.


Let's do here same and execute the query and see what will be the result?


This feature very help us to run the script against all the registered SQL Server instances. Hope you liked this post.