SQLServerCentral Article

The Central Management Servers Extension in Azure Data Studio



The Azure Data Studio comes with a lot of extensions. One of these is the Central Management Servers (CMS) extension. This extension allows adding servers in groups in order to run a query in multiple SQL Server at the same time. If we work with several SQL Servers at the same time, we sometimes need to run queries to all of them and running queries will take a lot of time. That is why using a CMS can simplify your maintenance job.

In this article, we will install the Azure Data Studio extension, create some groups, Add SQL Servers and run a query into multiple servers at the same time.


  1. Firstly, you will need 2 or 3 SQL Server instances installed
  2. Secondly, Azure Data Studio
  3. Finally, SSMS.

Installing the Central Management Servers Extension

Microsoft introduced this feature a long time ago in SQL Server 2008. This feature allows running a single query across multiple servers. The feature was available in SSMS and now, we have this extension available in Azure Data Studio.

First, go to extensions and search for the CMS to install it.

Install Central management servers extension Install Central Management Servers in Azure Data Studio

Secondly, go to Connections and you will see the CMS.

Connections in ADS CMS

Add a new Server to Central Managed Servers

Press the + icon to add the Server.

Add servers to CMS Add SQL Server to CMS


At the moment only the Microsoft SQL Server connection type is supported. This may change in the future. I am using . for the server which is the local server. You can use Windows Authentication (the recommended one) or SQL Authentication. You can also use the SQL Server name as well.

The name is optional and is like an alias and the description is also optional and is used to describe the server.

Central server Central server in Central Management Servers

Once the Central Management Server is added, you can add groups of servers. You could add servers directly, but it is a good practice to create groups first. You could have groups per region (i.e. America, Asia, Europe), per roles (Development, QA, Production), or group according to customer needs.

Add a group of servers Add a group in ADS

You will need to add a Name and optionally a description.

Add a server group Add Server Group in Central Management Servers

Add Servers to the groups

Also, create different groups. You can have the same servers in different groups. Note that the Central Server is created once and it cannot be in the groups.

Create several groups Create more groups in Central Management Servers

In addition, use the New Server Registration to add new SQL Servers to the groups created.

Add servers Add Servers in CMS

Currently, the only connection type available is Microsoft SQL Server. You need to specify the SQL Server name in Server and optionally you can add the name which is an alias and an optional description.

Check connection details Check the connection details

In Addition, there is an Advanced button. It is possible to set the timeout, language, encryption properties, and more.

Advanced properties of Central Management Servers Connection advanced configuration

Finally, if everything is OK, you will have different groups with Servers. In this example, we have the Certification group with 2 Servers.

Add more servers Central Management Servers to add

Add new servers

Running queries in multiple servers

The CMS does not allow executing the queries yet. To run a query across multiple servers in a group, we use the SQL Server Management Studio SSMS. To view the Central Management Server in SSMS, go to View>Registered Servers

View servers menu of Central Management Servers View the menu

Also, you can create groups and add servers here. For more information about Central Management Servers in SSMS, refer to our article related. Note that you cannot rename groups. If you want to change names, you need to delete them and create them again.

View CMS servers in SSMS SSMS Central Management Servers

Whatever you create or add in SSMS, can be viewed in ADS or vice versa. In the group with the 2 servers create right-click and select New Query.

Also, in the query, we will run a sp_who system stored procedure to get the current users, sessions, and processes in all the group servers.


Finally,  you can see that the sp_who is executed in both servers of the group. You will be able to see the information of the 2 servers registered.

query created CMS query created


In this article, we learned how to install the CMS extension in Azure Data Studio. Also, we learned how to create groups and add servers to them.  Finally, we use SSMS to run a query to a group with multiple SQL Servers and executed the query. At the moment, SSMS has more features in this area. However, in the long run, we expect to have the same functionality in future versions.


5 (2)

You rated this post out of 5. Change rating




5 (2)

You rated this post out of 5. Change rating