Was this page helpful?
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Execute Statements Against Multiple Servers Simultaneously (SQL Server Management Studio)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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 that are 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 that is 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 Note

Before you execute the following procedures, create a Central Management Server and server groups. For more information, see Create a Central Management Server and Server Group (SQL Server Management Studio).

In This Topic



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.

Arrow icon used with Back to Top link [Top]

To execute statements against multiple configuration targets simultaneously

  1. In SQL Server Management Studio, on the View menu, click Registered Servers.

  2. Expand a Central Management Server, right-click a server group, point to Connect, and then click New Query.

  3. In Query Editor, type and execute a Transact-SQL statement, such as the following:

    USE master
    SELECT * FROM sysdatabases;

    By default, the results pane will combine the query results from all the servers in the server group.

To change the multiserver results options

  1. In Management Studio, on the Tools menu, click Options.

  2. Expand Query Results, expand SQL Server, and then click Multiserver Results.

  3. On the Multiserver Results page, specify the option settings that you want, and then click OK.

Arrow icon used with Back to Top link [Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft