Published on

July 11, 2008

Managing Multiple Servers with SQL Server

As a database administrator (DBA), we often find ourselves performing repetitive tasks when it comes to managing our servers. Running the same script on multiple servers and switching connections can be time-consuming and tedious. While SQL Server 2008 and Policy Based Management help reduce redundant tasks, there are still some tasks that don’t fit into this framework. That’s where Multiple Server Queries come in.

Multiple Server Queries is a feature in SQL Server that simplifies the life of a DBA by allowing them to execute queries on multiple servers simultaneously. Although this feature is not well-documented in the Books Online, it is relatively easy to use. The only requirement is that you have at least one registered server group.

To create a registered server group in SQL Server Management Studio (SSMS), follow these simple steps:

  1. Open the Registered Servers Toolbar by pressing Ctrl+Alt+G or selecting it from the View menu.
  2. Open the Database Engine.
  3. Right-click on Local Server Groups and create a new group.
  4. Add registered servers to the group.

Once you have a registered server group, you can start a Multiple Server Query by right-clicking on the group folder and selecting New Query. This will open a query window with some specific environmental changes. The database dropdown box will only display the names of databases that are common to all servers in the selected group, and the status bar will turn pink to indicate that it is a Multiple Server Query Window.

You can now execute any query that is valid to run on all servers in the group. For example, you can use Multiple Server Queries to identify the most inefficient query plans across all servers in a group. The following query can be used:

SELECT TOP 2 highest_cpu_queries.total_worker_time, q.[text]
FROM (
    SELECT TOP 50 qs.plan_handle, qs.total_worker_time
    FROM sys.dm_exec_query_stats qs
    ORDER BY qs.total_worker_time DESC
) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC

Note that this particular query requires SQL Server 2005 or SQL Server 2008, as it utilizes dynamic management views. However, you can also query SQL Server 2000 servers as part of a group if the query is coded to run against SQL Server 2000.

Multiple Server Queries also support cross-platform abilities. For example, you can issue the following query to retrieve the last date the “sa” user was updated:

SELECT updatedate FROM syslogins WHERE name = 'sa'

The results will be aggregated and include an additional column containing the server name. You can enable or disable this option in Management Studio’s Options dialog.

Multiple Server Queries bring yet another powerful way of managing tasks on multiple servers and with multiple versions of SQL Server running. The number and types of queries that can be run against multiple servers are virtually unlimited, providing DBAs with greater efficiency and productivity.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.