Skip to content
LICENSEWARE Help
Sign in

SQL Server Management Studio

File Upload

Get the SQL Server edition from many instances at once by running one query through Central Management Servers (CMS) in SQL Server Management Studio.

Considerations

Section titled “Considerations”
  • Impact on Performance: Be aware of the potential impact on network and server performance, especially with a large number of instances. It might be better to perform this operation during off-peak hours.
  • Permissions: Ensure you have the necessary permissions on all SQL Server instances.
  • Security: Be cautious with the authentication method and credentials used, especially if they are stored within CMS.
  • Server Grouping: Use server groups to organize your servers logically (e.g., by environment, location, or application).

Setting up Central Management Servers in SSMS

Section titled “Setting up Central Management Servers in SSMS”
  1. Establish a Central Management Server:
  • Open SSMS.
  • In the “Registered Servers” window (View -> Registered Servers), right-click on “Central Management Servers” and select “New Server Registration”.
  • Register a SQL Server instance as the Central Management Server. This server will store the list of registered SQL Server instances and manage multi-server queries.
  1. Registering SQL Server Instances:
  • Under the newly created Central Management Server, right-click and choose “New Server Group” to create groups for organizing your servers (optional).
  • Right-click on the Central Management Server or a server group, then select “New Server Registration”.
  • Add each SQL Server instance you want to manage. Provide the necessary connection information (server name, authentication mode, and credentials).

Executing the query across multiple servers

Section titled “Executing the query across multiple servers”
  1. Connect to the Central Management Server:
  • In SSMS, connect to your Central Management Server in the “Registered Servers” window.
  1. Execute a Multi-Server Query:
  • Right-click on the Central Management Server or a specific server group.
  • Select “New Query”. This opens a query window that is connected to all registered servers within that group.
  • In the query window, write your SQL script: SELECT @@version.
  • Execute the query.

Viewing and exporting results

Section titled “Viewing and exporting results”
  • The results tab in the query window will show the output of the query for each server, including server names and their respective SQL Server version information.
  • You can export these results to a file (like CSV or Excel) for further analysis or documentation, using the standard SSMS export options.