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 Firstly, you will need 2 or 3 SQL Server instances installed Secondly, Azure Data Studio 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
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.
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.