Since I changed organisations in January this year, I asked one of my colleagues to export his SQL Server Management Studio Registered Server list. Ever since March I have been updating our list on nearly a daily basis, since I believe this is very important to managing multiple SQL Servers. For DBA teams, I believe one should make the registered server list available for the rest of the team via a network share.

Note that you can open REGSRVR file with a text editor to search through the XML data, such as to figure out within which group a specific server resides.

Multiple people cannot update this list unfortunately (unless manually) thus I do my best to keep it up to date. Maybe, at a later date, we`ll be able to have this managed under a Central Management Server (CMS), but that`ll have to wait until we begin to manage through Policies from a single CMS server.

If you have not used Registered Servers in SQL Server Management Studio before, simply click View, Registered Servers. You will also notice the option Types, which groups by Database Engine, Analysis Services, Reporting Services, SQL Server Compact, and Integration Services. Our list has very few updated for anything other than the Database Engine Type, but that will change over time!

Remember to re-import it frequently if this is the case in your organisation: to do so, right click on top of the Registered Server list, Local Server Groups, and import from the a shared folder. 

One of the nice features to take advantage of with a Registered Server list is querying across multiple servers.  By right clicking on any group, even the entire Local Group, you can select New Query to view, for example, Server Infrastructure details quickly. 

 The first method to going essential server properties, as mentioned on Technet, is to execute the following parametr details on any SQL Server installation (I tested back to SQL 2000), by executing the following command.

xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount", "ProcessorType"

-- result set is a table, with a row for each parameter

-- thus if you execute xp_msver, you will have twenty rows

The second, and my preference as best practice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):

select serverproperty('MachineName') MachineName

,serverproperty('ServerName') ServerInstanceName

,replace(cast(serverproperty('Edition')as varchar),'Edition','') EditionInstalled

,serverproperty('productVersion') ProductBuildLevel

,serverproperty('productLevel') SPLevel

,serverproperty('Collation') Collation_Type

,serverproperty('IsClustered') [IsClustered?]

,convert(varchar,getdate(),102) QueryDate
,serverproperty('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName],

case

when exists (select * from msdb.dbo.backupset where name like '%backup%') then 'SQLBackupUsed'

else 'Local Copy_Only'

-- where you would replace the

-- strings with your respective third party or native backup solution

End

More examples? Sure:

I was asked the other day to find out how many physical sockets (not logical) that are used across servers, so one would write this:

SELECT  cpu_count / hyperthread_ratio AS physical_cpu_sockets

FROM    sys.dm_os_sys_info ;

How about all cluster node details across a group of clustered servers:

SELECT * FROM sys.dm_os_cluster_nodes

--All non-background processes and their wait times across servers:
select * from sysprocesses where status <>'background'order by waittime desc
-- Read Error logs across servers
exec master.dbo.xp_readerrorlog

Thanks to SQL Server Central forums for the physical socket query, no wonder I enjoy blogging here

...and thanks to Microsoft Premier Field Engineers for the last three during a recent Clustered Server resolution analysis issue.