Multiple Server Query Execution in SQL Server 2008

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

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

·         http://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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS