Create a Central Management Server and Server Group

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This topic describes how to designate an instance of SQL Server as a central management server in SQL 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.

Note

Versions of SQL Server that are earlier than SQL Server 2008 (10.0.x) 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.

Using SQL Server Management Studio

The following procedures describe how to perform the following steps.

1. Create a central management server.

2. Add one or more server groups to the central management server and add one or more registered servers to the server groups.

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, and then click Register Central Management Server.

3. In the New Server Registration dialog box, select the instance of SQL Server that you want to become the central management server from the drop-down list of servers. You must use Windows Authentication for the central management server.

4. In Registered Server, enter a server name and optional description.

5. From the Connection Properties tab, review or modify the network and connection properties. For more information, see Connect to Server (Connection Properties Page) Database Engine

6. Click Test, to test the connection.

7. Click Save. The instance of SQL Server will appear under the Central Management Servers folder.

Create a new server group and add servers to the group

1. From Registered Servers, expand Central Management Servers. Right-click the instance of SQL Server added in the procedure above and select New Server Group.

2. In New Server Group Properties, enter a group name and optional description.

3. From Registered Servers, right-click the server group and click New Server Registration.

4. From New Server Registration, select an instance of SQL Server. For more information, see Create a New Registered Server (SQL Server Management Studio). Add more servers as appropriate.

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

See Also

Administer Multiple Servers Using Central Management Servers