Execute statements against multiple servers simultaneously

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This article describes how to query multiple servers at the same time in SQL Server, 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 result 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.

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

Permissions

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

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

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

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

    USE master
GO
SELECT * FROM sys.databases;
GO

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

Change the multiserver results options

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

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

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

See also