During my recent visit to TechEd India 2009 at Hyderabad, I had the opportunity to present a technical session on SQL Server Management Studio 2008 New Features. The session was a great success and received a lot of positive feedback from the attendees. Many of my readers have requested me to share the session online, but due to the inclusion of videos and demos, it is not feasible to post the entire session. However, I don’t want to disappoint my readers, so I have decided to share some valuable tips from the session with all of you.
One of the most useful features of SQL Server Management Studio (SSMS) is the ability to query multiple servers from a single window. This feature is particularly beneficial for database administrators (DBAs) who need to maintain and gather information from multiple SQL Servers and create reports. With SSMS, DBAs can now assemble all the required information instantaneously without the need to switch between different windows or tools.
To run a multi-server query, the first step is to create a Registered SQL Server Group with the desired servers. Here’s how you can create a Registered Servers Group:
- Go to Toolbar >> Views >> Registered Servers
- In the opened window, right-click on Local Server Groups and add a new server
Once you have added all the desired servers, you can run the following query in the Query Editor to retrieve results from all the registered servers:
SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion
GO
Make sure that when running queries, you have selected the entire group of servers on the left side. In the example above, the Local Server Groups node is selected.
When executing the query, you will notice that an additional column of Server Name is included in the result set. This column helps differentiate the results and indicates which row belongs to which server. In my case, I have two registered servers, so the query returns two rows with the expected results.
It’s worth mentioning that you can register hundreds of SQL Servers as a group, making it a powerful tool for managing large-scale environments.
However, if you want to query only a few selected servers, the current version of SSMS does not allow you to make a selection from server groups. In such cases, you will need to create a new server group and register the selected servers again. This is possible because a single SQL Server can belong to multiple server groups.
SQL Server Management Studio 2008 provides DBAs with a convenient and efficient way to query multiple servers and gather information from a centralized location. This feature saves time and effort, allowing DBAs to focus on other critical tasks.
I hope you find these tips helpful in your SQL Server management journey. Stay tuned for more exciting articles on SQL Server and its various features!