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 (https://blog.sqlauthority.com)
8 Comments. Leave new
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 :\
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.
what if i want to add 100′s of servers at a time which are with same sa password.
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: .
Do let me know your thoughts on the same.
Thank-you, and have a great day!
Thanks & Regards,
Nakul Vachhrajani
so,nice
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” ?
How do I change the name of the first column, currenty labelled as ‘Server Name’? I would like to have a different name for that column.
How do I rename the first column currently labelled as ‘Server Name’ to something like ‘URL’? Thanks,