Share via

  • Article

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

In SQL Server 2008, you can query multiple servers at the same time 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

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

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

  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.

Security

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.