Execute Statements Against Multiple Servers Simultaneously
This topic describes how to query multiple servers at the same time in SQL Server 2016, by creating a local server group, or a Central Management Server and one or more server groups, and one or more registered servers within the groups, and then querying the complete group.
The results returned by the query can be combined into a single results pane, or can be returned in separate results panes. The results set can include additional columns for the server name and the login used by the query on each server. Central Management Servers and subordinate servers can be registered by using only Windows Authentication. Servers in local server groups can be registered by using Windows Authentication or SQL Server Authentication.
NOTE! Before you execute the following procedures, create a Central Management Server and server group. For more information, see Create a Central Management Server and Server Group (SQL Server Management Studio).
Because the connections maintained by a Central Management Server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might 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.
Execute statements against multiple configuration targets simultaneously
In SQL Server Management Studio, on the View menu, click Registered Servers.
Expand a Central Management Server, right-click a server group, point to Connect, and then click New Query.
In Query Editor, type and execute a Transact-SQL statement, such as the following:
USE master GO SELECT * FROM sysdatabases; GO
By default, the results pane will combine the query results from all the servers in the server group.
To change the multiserver results options
In Management Studio, on the Tools menu, click Options.
Expand Query Results, expand SQL Server, and then click Multiserver Results.
On the Multiserver Results page, specify the option settings that you want, and then click OK.