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 in SQL Server 2012 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.

Note

Versions of SQL Server that are earlier than SQL Server 2008 cannot be designated as a central management server.

In This Topic

• Before you begin:

  Security

• To create a Central Management Server and Server Group, using:

  SQL Server Management Studio

Before You Begin

Security

Permissions

Two database roles in the msdb database grant access to central management servers. Only members of the ServerGroupAdministratorRole role can manage the central management server. Membership in the ServerGroupReaderRole role is required to connect to a central management server.

Because the connections that are maintained by a central management server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.

[Top]

Using SQL Server Management Studio

To create a central management server

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.

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.

   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.

To execute queries against several configuration targets at the same time

• After you create a central management server, one or more server groups, and one or more registered servers, you can execute queries against a whole group at the same time. For more information about how to execute Transact-SQL statements on the servers in a server group at the same time, see Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio).

[Top]

See Also

Concepts

Administer Multiple Servers Using Central Management Servers