Administer Multiple Servers Using Central Management Servers
Managing multiple SQL Servers has its challenges. One of the big challenges has 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.
Benefits of Central Management Servers and Server Groups
An instance of SQL Server that is designated as a Central Management Server maintains server groups that contain the connection information for one or more instances of SQL Server. Transact-SQL statements and Policy-Based Management policies can be executed at the same time against server groups. You can also view the SQL Server log files on instances that are managed through a Central Management Server. Versions of SQL Server that are earlier than SQL Server 2008 cannot be designated as a Central Management Server.
Transact-SQL statements can also be executed against local server groups in Registered Servers.
Create a Central Management Server and Server Group (SQL Server Management Studio)
This topic describes how to designate an instance of SQL Server as a central management server by using SQL Server Management Studio. Central management servers store a list of instances of SQL Server that is organized into one or more central management server groups. Actions that are taken by using a central management server group act on all servers in the server group. This includes connecting to servers by using Object Explorer and executing Transact-SQL statements and Policy-Based Management policies on multiple servers at the same time.
To create a central management server
- 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.
- 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.
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 [MyDomain\tusharg] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb] GO USE [msdb] GO CREATE USER [MyDomain\tusharg] FOR LOGIN [MyDomain\tusharg] GO EXEC sp_addrolemember N'ServerGroupReaderRole', N'MyDomain\tusharg' 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 [MyDomain\tusharg] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb] GO USE [msdb] GO CREATE USER [MyDomain\tusharg] FOR LOGIN [MyDomain\tusharg] GO EXEC sp_addrolemember N'ServerGroupAdministratorRole', N'MyDomain\tusharg' GO
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.