SQL SERVER – 2008 – SSMS Feature – Multi-server Queries

In my recent visit to TechEd India 2009 at Hyderabad, I had taken a technical session on SQL Server Management Studio 2008 New Features, which was attended by a huge number of participants and was very successful. I got loads of requests from my readers for posting the session online. My presentation involved several videos and demos, so practically it is not possible for me to post my original session online. But as I do not want to disappoint my readers I have one solution; what I can do is that I can share some valuable tips from the session with you all.

Using SQL Server Management Studio (SSMS) DBAs can now query multiple servers from one window. It is quite common for DBAs with large amount of servers to maintain and gather information from multiple SQL Servers and create report. This feature is a blessing for the DBAs, as they can now assemble all the information instantaneously without going anywhere.

Prior to running a multi-server query, it is mandatory to first create a Registered SQL Server Group with the desired servers.

Create Registered Servers Group:

Go to Toolbar >> Views >> Registered Servers

In the opened window, Right Click on Local Server Groups and add a new server.

After all the desired servers are added run the following query in Query Editor. It will produce the result from all the registered servers. Make sure that when Queries are run on the left side the entire group of servers is selected. In the following example, Local Server Groups node is selected.

SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion
GO

In my local machine, two different SQL Server 2008 are installed: SQL Server Dev Enterprise and SQL Server Express. These queries bring back two rows with expected results. Another thing to notice is that even though we have not selected, this result has brought back additional column of Server Name. This additional column has been added to differentiate our resultset and indicate which row belongs to which server.

In my case, I have only two registered servers, but it is absolutely possible to have hundreds of SQL Servers registered as group. If user wants to query few selected servers, it is presently not possible to make selection from server groups. User will have to create a new server group and the registered selected servers all over again. This is possible as one SQL Server can belong to multiple SQL Servers.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – 2008 – SSMS Feature – Multi-server Queries

  1. This is a nice function. Wouldn’t have been my first choice for new features, but certainly a plus.

    What irks me in 08 is that opening a table in SSMS only opens TOP 200

    Sure, I can see the point of that being an OPTION.. but I couldn’t find a way to stop it being the norm :\

  2. Came across this capability in SQL2k8 but was hoping it could replace the Linked Servers for doing multi-database queries.

    Unfortunately this appears to be for multi-server admin scripted tasks. Where the databases and structures are identical on each of the Registered Servers in the Group. Such as with the sys.* databases.

    Definitely nice but it would be of even greater benefit if one could do a 4-part naming convention within these aggregate groups.

  3. Hello, Pinal!

    As you might know, I am writing a series on the Underappreciated features of Microsoft SQL Server. The series is actually inspired from Andy Warren’s editorial of the same name in SQL Server Central.

    One of the features that came up was the multi-server queries feature. I have thus referred your post in my article at: http://beyondrelational.com/blogs/nakul/archive/2011/02/04/multi-server-queries-underappreciated-features-of-microsoft-sql-server.aspx.

    Do let me know your thoughts on the same.

    Thank-you, and have a great day!

    Thanks & Regards,

    Nakul Vachhrajani

  4. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 8 of 31 Journey to SQLAuthority

  5. Pingback: SQL SERVER – Remove Cached Login from SSMS Connect Dialog – SQL in Sixty Seconds #049 | SQL Server Journey with SQL Authority

  6. Thank you Dave but you stopped halfway

    Now that You’ve registered two servers how do you go about using them at the same time?

    How can I write “select * from server1.database1.dbo.table1 join server2.database2.dbo.table2 on table1.col1 = table2.col2″ ?

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s