Learn more about SQL Server tools

   
   















































Execute SQL Server query on multiple servers at the same time

MSSQLTips author Kun Lee By:   |   Read Comments (14)   |   Related Tips: 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 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 your SQL 2000, SQL 2005 and SQL 2008 servers.

Solution
SQL 2008 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 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.

 

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.

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.

 


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":

 

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.)

 


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



Last Update: 6/8/2009


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.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Thursday, September 19, 2013 - 2:36:32 AM - rajarshee Read The Tip

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 Read The Tip

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 Read The Tip

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 09, 2013 - 4:47:12 PM - Peter Nerida Read The Tip

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 Read The Tip

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 Read The Tip

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 09, 2010 - 10:40:55 AM - Robert Read The Tip

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 - kunsikl@gmail.com Read The Tip

 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 Read The Tip

 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 09, 2010 - 4:44:06 PM - ocean_emerald Read The Tip

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 08, 2009 - 3:29:42 PM - kunsikl@gmail.com Read The Tip

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 08, 2009 - 3:09:09 PM - ellis Read The Tip

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 08, 2009 - 2:40:59 PM - kunsikl@gmail.com Read The Tip

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 08, 2009 - 4:18:35 AM - ellis Read The Tip

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...




 
Sponsor Information