SQL Server 2008 SSMS introduces a new feature, Multiple Server Query Execution, in Query Editor. This feature intends to increase the productivity of running same query against multiple servers at once. Using this functionality, SQL Server Management Studio (SSMS) DBAs can now query multiple servers from one window. To use this functionality you must have
- You need at least one SQL 2008 Server to setup Central Management Servers
- You also need to setup logins for Administration and General Users
Video – How to execute a SQL Statement against multiple Servers
How to create a central management server Steps
- In SQL Server Management Studio 2008, on the View menu, click Registered Servers.
- In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
- In the New Server Registration dialog box, register the instance of SQL Server that you want to become the central management server.
- In Registered Servers, right-click the central management server, point to New, and then click New Server Group. Type a group name and description, and then click OK.
- In Registered Servers, right-click the central management server group, and then click New Server Registration.
- In the New Server Registration dialog box, register one or more instances of SQL Server that you want to become members of the server group.
After you have registered a server, the central management server will be able to execute queries against all servers in the group at the same time
Execute statements against multiple SQL Servers (Steps)
- 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:
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
- 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.