Introduction

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.

Requirements

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 in Azure Data Studio

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

CMS

Add a new Server to Central Managed Servers

Press the + icon to add the Server.

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 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 in ADS

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

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 more groups in Central Management Servers

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

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 the connection details

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

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.

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

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.

sp_who

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.

CMS query created

Conclusion

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.