Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
This article describes how to designate an instance of SQL Server as a Central Management Server (CMS) in SQL Server using SQL Server Management Studio (SSMS). A CMS stores a list of instances of SQL Server that is organized into one or more groups. Actions that are taken by using a CMS server group act on all servers in the 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
SQL Server 2008 (10.0.x) and earlier versions can't be designated as a CMS.
Permissions
Two database roles in the msdb database grant access to Central Management Servers. Only members of the ServerGroupAdministratorRole role can manage the CMS. Membership in the ServerGroupReaderRole role is required to connect to a CMS.
Because the connections that are maintained by a CMS execute in the context of the user, the effective permissions on the registered servers can 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.
Create a central management server
If the Registered Servers tool window isn't visible in SSMS, select View > Registered Servers, or type Ctrl + Alt + G.
In the Registered Servers pane, expand Database Engine, right-click Central Management Servers, and then select Register Central Management Server....
In the New Server Registration dialog, enter the information for the instance of SQL Server that you want to be the CMS.
| Setting | Description |
|---|---|
| Server type | The Server type box is read-only. Only a Database Engine can be a CMS. |
| Server name | For Server name, enter the fully qualified name of your SQL Server (you can also use localhost as the server name if you're connecting locally). If you're NOT using the default instance - MSSQLSERVER - you must enter in the server name and the instance name. If you're unsure how to determine your SQL Server instance name, see Find SQL Server instance name. |
| Authentication | Windows Authentication is set as default. You can also use SQL Server Authentication to connect. However, if you select SQL Server Authentication, a username and password are required. Microsoft Entra authentication is available for SQL Server 2022 (16.x) and later versions. For step-by-step configuration instructions, see Tutorial: Set up Microsoft Entra authentication for SQL Server For more information about authentication types, see Connect to Server (Login page) - Database Engine. |
| Login | The user ID from the server account used to sign in to the server. A login is required when using SQL Server Authentication. |
| Password | The password from the server account used to sign in to the server. A password is required when using SQL Server Authentication. |
| Remember password | Select to have SQL Server encrypt and store the password you have entered. This option is displayed only if you have selected to connect using SQL Server Authentication. |
| Encryption 1 | Select the encryption level for the connection. The default value is Mandatory. |
| Trust server certificate | Check this option to bypass server certificate validation. The default value is False (unchecked), which promotes better security using trusted certificates. |
| Host Name in Certificate | The value provided in this option is used to specify a different, but expected, CN or SAN in the server certificate. |
1 The default value is Mandatory in SQL Server Management Studio (SSMS) 20. Strict (SQL Server 2022 and Azure SQL) encryption should be used for Azure SQL Database and Azure SQL Managed Instance. Strict (SQL Server 2022 and Azure SQL) encryption can be used for SQL Server when the instance has Force Strict Encryption enabled. In SQL Server Management Studio 21, this is called Strict (Minimum SQL Server 2022 and Azure SQL).
You can modify more connection options by selecting Options. Examples of connection options include the connection timeout value, application intent, and the network protocol. This article uses default values for these fields.
Create a new server group and add servers to the group
From Registered Servers, expand Central Management Servers. Right-click the instance of SQL Server added in the previous steps and select New Server Group.
In New Server Group Properties, enter a group name and optional description.
From Registered Servers, right-click the server group and select New Server Registration.
From New Server Registration, select an instance of SQL Server. For more information, see Create a new registered server in SQL Server Management Studio.
Repeat these steps to add more servers to the server group.
Execute multi-server queries
After you create a CMS, one or more server groups, and one or more registered servers, you can execute queries against a group of servers 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 in SQL Server Management Studio.