Central Management SQL Server – Multi Server Query

Leave a comment (1) Go to comments

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

  1. In SQL Server Management Studio 2008, on the View menu, click Registered Servers.
  2. In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
  3. In the New Server Registration dialog box, register the instance of SQL Server that you want to become the central management server.
  4. 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.
  5. In Registered Servers, right-click the central management server group, and then click New Server Registration.
  6. 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)

  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.

Central Management SQL Server – Multi Server Query sql server video multi server query sql server video central management server sql server muti server query video sql server multiple server query video sql server multiple server query sql server multi server query sql server execute query against multiple servers multi Server query How to execute a SQL Statement against multiple Servers Execute query for multiple SQL Servers video Central Management SQL Server


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.
EOF - Central Management SQL Server – Multi Server Query, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Trackbacks and Pingbacks: