Execute SQL Server query on multiple servers at the same time

By:   |   Comments (16)   |   Related: 1 | 2 | 3 | 4 | More > Central Management Servers


Problem

Managing multiple SQL Servers has its challenges. One of the big challenges is having to collect data from each of the servers you manage and figure out which servers need attention.  You can setup a process to connect to each server and collect the data or you can use a third party tool, but starting with SQL 2008 offers a new way of doing this using Central Management Servers.  In this tip I show you what this new feature is, how to setup it and how to collect data from all of your servers at the same time. And best of all, once it is setup you can even use it on SQL 2000 and SQL 2005 servers.

Solution

SQL Server introduced a new feature call "Central Management Servers (CMS)" which allows you to administer multiple servers and consolidate the SQL Server registration for all the SQL Server users such as developers, DBAs, etc.

Pre-requisite

  • You need at least one SQL 2008 Server or later to setup Central Management Servers
  • You also need to setup logins for Administration and General Users

Initial Setup

The setup is very straight forward. You just need to follow the How to: Create a Central Management Server and Server Group (SQL Server Management Studio) process which is outlined below.

  1. In SQL Server Management Studio, on the View menu, click Registered Servers.
  2. In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
  3. In the New Server Registration dialog box, register the instance of SQL Server that you want to become the Central Management Server. I used "DCKLEE\SQL2008" for this setup
  4. In Registered Servers, right-click the Central Management Server, point to New, and then click New Server Group. Type a group name and description, and then click OK.
  5. In Registered Servers, right-click the Central Management Server group, and then click New Server Registration.
  6. In the New Server Registration dialog box, register one or more instances of SQL Server that you want to become members of the server group.
  7. After you have registered a server, the Central Management Server will be able to execute queries against all servers in the group at the same time.

General Structure for Server Groups (How I do it) - Setting up Folders and register servers

  • I typically start by creating a folder called "All" or "[Company Name] - All" and under that I create a folder for each SQL Server version as shown below. But you can set this up any way you want for your environment.
registered servers

Now, here is one little tip. When you try to register the server that is running "Central Management Server" (for my case, DCKLEE3\SQL8008A), you will get the below error which says you cannot add a shared registered server with the same name as the Configuration Server.

registered servers

When you get this error here is the work around. First, you need to find what port number the server is using. You can see "Static Port Assignments in SQL 2005" article to check what port is being using.

(If you are using Dynamic Ports, then you need to create an alias (see How to setup and use a SQL Server alias), but I wouldn't recommend using this because now you have to ask all the users to setup the same alias to use it. To make it easier you can deploy it automatically to all desktops by using a script, but again I wouldn't recommend using it unless you have to use dynamic ports.)

Anyway, once you find the port number, you need to put the port number along with the server name like "DCKLEE3\SQL2008A,2010" (i.e, 2010 is the port number for this server) and now you should be able to register it.

general

Example1: Pull Failed SQL Server Agent jobs for last 7 days for all SQL Servers

First, right click the "All" folder that you just created and choose "New Query":

new query

Second, run "SELECT @@VERSION" to see version information for all servers that have been registered.  If you run the query you will see something like below which will give you this information for all servers in the "All" folder.  (Note: for security purposes I hid the beginning part of the server names below, so the output looks a little weird.)

version

Example 2: Get the query from article "Failed SQL Server Agent Jobs" and try to run it!  Pretty slick!

At this point you can execute any query you want and it will return results for each server in the group you selected.

Security Setup

In order to use this, you will need to setup two different permissions for two different users/groups. One will have management rights and the other read rights.  There are new database roles in the msdb database, the two roles that will be used are:

  • ServerGroupAdministratorRole - management rights
  • ServerGroupReaderRole - read rights
  • For the general Reader, the login should use the 'ServerGroupReaderRole' role under msdb and here is the script that I used to create the login and give it permissions.
-- Setting up User for General Reader
USE [master]
GO
CREATE LOGIN [ILOVESQL\GroupDeveloper] FROM WINDOWS 
 WITH DEFAULT_DATABASE=[msdb]
GO
USE [msdb]
GO
CREATE USER [ILOVESQL\GroupDeveloper] FOR LOGIN [ILOVESQL\GrpDeveloper]
GO
EXEC sp_addrolemember N'ServerGroupReaderRole', N'ILOVESQL\GroupDeveloper'
GO
  • For the DBAs, the login should be on 'ServerGroupAdministratorRole' role under msdb and here is the script that I used to create the login and give it permissions.
-- Setting up User for Administrator
USE [master]
GO
CREATE LOGIN [ILOVESQL\GrpDBA] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb]
GO
USE [msdb]
GO
CREATE USER [ILOVESQL\GrpDBA] FOR LOGIN [ILOVESQL\GrpDBA]
GO
EXEC sp_addrolemember N'ServerGroupAdministratorRole', N'ILOVESQL\GrpDBA'

That's all there is to it.  Just setup a Central Management Server, register your servers, grant permissions and start executing queries across all of your SQL  Servers.

Next Steps
  • Now you can have a script to check disk space, locks, usage, etc for all the servers that you are interested in and get results all at the same time.
  • You can run "Audit" queries to find out who has permission on what servers
  • And there are so many others that you can run across all servers that you manage

References



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, January 18, 2019 - 8:33:32 AM - Debbie Back To Top (78809)

Hi, I have read your article and set up a Central management Server, Created a Server Group underneath it called "Production" and listed my servrs under it.  When querying it see it it worked or not, i am using select * from sysmanagement_shared_server_groups and Select * from sysmanagement_shared_registered_servers_internal, however the second one isn't working, it has no data returned in the query grid.  Your article mentions permissions, I am a sysadmin on all our srervers, would that not impley that I have the permissions needed, also, if not, what msdb database would i need them on, all of the registered servers?

I would appreaciate your help

Friday, January 22, 2016 - 2:46:42 PM - Pal Back To Top (40480)

Hi,

Helpful Post!

I am working with CMS and maintaining compliance. I am trying to create and schedule job with a policy, condition and check the role of availability replica on all servers listed in a server group(AG Servers- with Server Groups Test, Dev, Prod- Each listed with multiple servers). I am not sure about how to include the power shell script in a job step to refer back to apply the policy check (from SQL scheduled JOB) on all servers included in (CMS- Server- Server Group-Server groups- Server Instances). Need some power shell scripts to point to and if this is appropriate?

Can you please help?

Thanks in advance!

Thursday, September 19, 2013 - 2:36:32 AM - rajarshee Back To Top (26854)

Hi Kun,

 

I would like to know can i execute a query to extract data from CMS via a Batch file and then store the output in notepad.If yes can you guide me in that

 

Thanks,

Rajarshee

Tuesday, September 17, 2013 - 2:56:28 PM - Peter Nerida Back To Top (26833)

Kun,

Can I run my script in batch and output the same to excel or flat file on my desktop?

Thanks,

Pete

 

Tuesday, September 10, 2013 - 4:09:39 PM - Kun Lee Back To Top (26717)

Peter,

I can't think of what you want to do easy way but you can do work around if you know how to write SSIS package or SQLCMD mode via job scripting. I am sure you already know the answer but just to give you a tip for SSIS package, here is what I would do.

Write a SSIS Package and setup a container of connection string and you can get the server name from like this from your CMS (Central management server).

"select * from [msdb].[dbo].[sysmanagement_shared_registered_servers_internal]"

And use loop to collect those information and populate the table on the master server and have sharepoint to read it from it.

Or, you can just have reporting server point there just query off those information directly from msdb db and create a webpart as web serivces to show your reporting server, etc.. There are so many ways to get those information with and without CMS.

I am sorry that I am not giving out the full detail suggestion but just work around but I hope that helps.

Monday, September 9, 2013 - 4:47:12 PM - Peter Nerida Back To Top (26688)

Kun,

I have a script to check when the last backup was taken. I run this to all servers.  Can I run this in batch and push it sharepoint list and display it thru sharepoint.

Thanks,

Pete

 

Tuesday, April 30, 2013 - 4:40:31 PM - Kun Lee Back To Top (23638)

Yes, you can load that to a table and use "msdb.dbo.sp_sysmanagement_add_shared_registered_server" proc to load it.

Monday, April 29, 2013 - 9:29:14 AM - Roderick McBan Back To Top (23608)

Is it possible to import servers to the Central Management Servers Group through a script ?

I have 150+ servers that I would like to add to Central Maangement Server and if I could avoit the pain of donig them all manually it would be great. 

Tuesday, November 9, 2010 - 10:40:55 AM - Robert Back To Top (10341)

Is there a way to connect to the CMS server using reporting services and run a report?

Wednesday, June 16, 2010 - 1:51:23 PM - [email protected] Back To Top (5708)

 Unfortunately, that is not built in feature. In my company, we actually have customize Central Management DB via .NET code and that code uses the connect string to push things out. Also since we have SQL server name stored as table, I have SSIS package to grab that as connection string and run the command or you can use “SQLCMD.exe” with xp_cmdshell or OPENQUERY commend to execute the query to all servers. I am sorry that I can’t give you easy answer for this. 


Wednesday, June 16, 2010 - 11:19:28 AM - MikeC Back To Top (5707)

 The CMS is handy for a hands on view of your servers; However, I cannot figure out how do access a CMS group via a job or script.  Do you know of anyway to run a multi server query from a job?

 

Thanks.

 Mike


Wednesday, June 9, 2010 - 4:44:06 PM - ocean_emerald Back To Top (5675)

Is there a way to schedule something using central management server?   For instance, I want to run a same query against a list of servers each day and email the result to an email addr?

Monday, June 8, 2009 - 3:29:42 PM - [email protected] Back To Top (3528)

Absolutely Not :)

You do not need to execute the quer multiple times! As long as you have SQL 2008 Management Studio and once you setup on your local machine, just right click the folder and  and choose "New Query".  That will get you what you need. Of course, assume you have servers that registered. Again it will initiate multi server query on all different servers that you registered and it will return the result set as one grid or text. If you do not want to see the result in one grid or text, you can change the option to disable/enable under Options (Query Results/SQLServer/Multi-Server)

See this for more information on http://msdn.microsoft.com/en-us/library/bb895275.aspx

Thanks,
Kun

Monday, June 8, 2009 - 3:09:09 PM - ellis Back To Top (3527)

Hi?

Thanks for the articles, will this process allow me to execute, say a query returning failed jobs in one place and get the results of many servers. I know i can register servers and connect to them easily enough but to check several server i guess i still need to execute the query multiple times on different servers

Monday, June 8, 2009 - 2:40:59 PM - [email protected] Back To Top (3526)

Dear Ellis,

You brought a good point. Unfortunately, as far as I know, you are right about that. CMS will only work with Windows authentication because it only uses AD authenticates to connect SQL server.  So, you cannot have all the features that provided by CMS; however, you still can use the Multi Server Query feature by setting up the folder structure on your local machine. You might already know about that.
To make a little easier, you can still get the folder structure copy from existing to your local machine by Export/Import the structure and setup to use SQL Login and save the credential. Here are two articles that you can take a look at if you haven’t done before.



Thank you for a great input.
Kun

Monday, June 8, 2009 - 4:18:35 AM - ellis Back To Top (3520)

is it true that CMS will only work with Windows authentication, so you can't connect to servers vis a sql login, it has be windows...

that kind of makes it useless to me, for the servers i can only connect to using SQL Logins...














get free sql tips
agree to terms