Multiple Server Query Execution in SQL Server 2008

  • Article

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. Some of useage include:

· Configure group of servers or server farm

· Generate report or document from multiple servers

· Analyze result from multiple servers

· or Run any sql query against multiple servers

Pre-requisite

To deploy or test Multiple Server Query Execution, you need to setup SSMS in SQL Server 2008 November CTP (CTP5 version 10.00.75.23) or later.

Setup Registered Server Group

Multiple Server Query Execution requires at least one registered server group to run a script against multiple server. To setup a registered server group and servers, refer to

· https://msdn2.microsoft.com/en-us/library/ms181228(SQL.100).aspx

· https://msdn2.microsoft.com/en-us/library/ms183353(SQL.100).aspx

Create a new query for Multiple Server Query Execution

On the existing or newly created registered server group, click right mouse button and select New Query menu item. SSMS opens a new QE session with multiple connections against all registered servers in the group. Note that QE status bar indicates multiple server connection with a background color and text.

From this point on, script editing and execution are identical to single connection mode. Some behavioral differences include that:

· Database drop-down-list on QE toolbar only displays common databases among connected server instances.

· Status bar visually indicates multiple server connections with color and text; it can be customized through Option.

· F4 Property tools-window displays attributes for multiple connection and execution.

To open existing sql script file, click on a registered server group and then click Open File toolbar button or File | Open | File… (Ctrl + O) menu item.

Execute a script and retrieve results from multiple servers

Let’s run the following sample query on a database; this query retrieves size and used space in MB per database filegroup, and calculates percentage of the used space.

-- Database space as a whole (per filegroup)

SELECT

  FGName = CASE df.type_desc

    WHEN 'LOG' THEN 'Transaction log'

    ELSE ( SELECT MIN(Name)

FROM sys.data_spaces ds

WHERE ds.data_space_id = df.data_space_id)

    END

  ,SizeMB = SUM(Size * 8 /1024) -- Size is 8-K pages, converting to MB

  ,UsedMB = SUM(FILEPROPERTY (Name, 'SpaceUsed')/128)

,UsedPercent = CONVERT(numeric(5,2), 100.00*SUM(FILEPROPERTY(Name, 'SpaceUsed'))/SUM(Size))

FROM sys.database_files df

GROUP BY df.data_space_id,df.type_desc

 

Now, execute the query in Result to Grid mode. Note that the result grid combines retrieved data sets from multiple servers by indicating each row with Server Name column. You can copy the grid to Excel or save it as *.csv or tab delimited format for further analysis and documentation.

User preference options

You can access options for Multiple Server Query Execution in two different places.

· Query | Query Options | Results | Multiserver; this applies only to the current editor session but does not reset the default options.

· Tools | Options | Query Results | SQL Server | Multiserver Results; this applies to New editor session and remains as a default.

Options in this page allow you to customize:

· Show / hide login name per row.

· Show / hide server name per row.

· Merge results in to a single grid or let each grid display the result per server.

You can also customize the default color of status bar in Tools | Options | Text Editor | Editor Tab and Status bar.

· Group connection to indicate multiple server connection.

· Single server connection for traditional single connection mode.

Known Issues

As of November CTP, Multiple Server Query Execution contains following known issues:

· Status bar needs to indicate partial success for query execution.

· Connection dialog needs to allow connection change to another registered server group.

· Some data type issues are also going through investigations.

FAQ

Please let us know your feedback on Multiple Server Query Execution through this blog distribution list or Connect. It is the direct pipeline to our dev team.

  • Does Multiple Server Query Execution support JOIN or ORDER BY across servers? : No. The execution module connects each server and executes the query separately. Once, results from each server are returned, QE concatenates multiple result into a single grid.
  • Does it support transaction across servers to rollback when some servers in the group fail to execute? No. Again the trasaction boundary is set on each server. However, this feature area is something we wan to improve in the future.

Eric Kang Program Manager
SQL Server Manageability

Comments

  • Anonymous
    February 07, 2008
    I just wanted to say thanks for this! I've been playing around with the November CTP and I found this feature by mistake. It's AWESOME for planning upgrades to servers when you need to include steps for checking db online/offline status, build levels, etc. The only thing I'd request is that give us an option to manually select one or more target servers from the registered server list with a ctrl-click / shift-click style (like a multi-select drop down list...) Cheers!

  • Anonymous
    February 12, 2008
    this is a fantastic feature from a scalability perspective --- a sample showing how to connect from a C# app would be quite helpful -- also some details on how it would fit within overall MARS framework

  • Anonymous
    August 14, 2008
    The comment has been removed

  • Anonymous
    August 14, 2008
    The comment has been removed

  • Anonymous
    August 14, 2008
    Blogs like these are really great. If you could provide some SAMPLE Code (or pointer to it) that is most commonly used within Microsoft Team for your testing the applications, that can be helpful too, whereby this page can become the START POINT (for all of us) who is looking for this feature of SQL Server :)

  • Anonymous
    November 12, 2008
    I'm gathering that multiple server queries can only be run on common databases across servers (master, msdb, etc.), correct?  We have the need to run the same query on differently named databases across servers.  If we could have a way of selecting the databases on each server to run against, that would save our team week (possibly months) of time per year.

  • Anonymous
    January 30, 2009
    Author: Mike Weiner Contributor: Burzin Patel Reviewers: Lubor Kollar, Kevin Cox, Bill Emmert, Greg Husemeier

  • Anonymous
    September 23, 2010
    How to store the output from the multiple servers into a single instance.

  • Anonymous
    December 02, 2010
    Absolutely love the feature.  Agree with Sacker above.  We have 1,000's of uniquely named databases that we need to run same ad-hoc queries against and compile into one result set.  Would be a great feature to have if I could specify not only database names that are exactly the same - but ones that have a certain naming convention such as '%partofdatabasename%'