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.
You need at least one SQL 2008 Server to setup Central Management Servers
You also need to setup logins for Administration and General Users
In SQL Server Management Studio, on the View menu, click Registered Servers.
In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
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
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.
In Registered Servers, right-click the Central Management Server group, and then click New Server Registration.
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.
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.)
At this point you can execute any query you want and it will return results for each server in the group you selected.
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
CREATE LOGIN [ILOVESQL\GroupDeveloper] FROM WINDOWS
CREATE USER [ILOVESQL\GroupDeveloper] FOR LOGIN [ILOVESQL\GrpDeveloper]
EXEC sp_addrolemember N'ServerGroupReaderRole', N'ILOVESQL\GroupDeveloper'
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
CREATE LOGIN [ILOVESQL\GrpDBA] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb]
CREATE USER [ILOVESQL\GrpDBA] FOR LOGIN [ILOVESQL\GrpDBA]
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.
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
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
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.
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)
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 - email@example.com
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.