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://darkslategrey-bat-805937.hostingersite.com)
