Thursday, June 07, 2012

Two Ways to Document Your SQL Server Infrastructure Quickly


To collect information for your SQL Server Infrastructure, there are two ways I can recommend.
 
The first, as mentioned on Technet, is to execute the following parameter details on any SQL Server installation (I tested back to 2000), by run the following command.
exec xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount"
-- result set is a table, with a row for each parameter

The second, and my preference as best pratice 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,
case
when  exists (select * from msdb.dbo.backupset where name like 'data protector%') then 'HPDPused'
else 'Local Copy_Only & Commvault' -- where you would replace the
-- strings with your respective third party or native backup solution
end

To run either of these queries across multiple servers in SSMS 2008 (assuming that you have more than one), under Registered Servers, right click on Local Server Groups, and select New Query.

References:  See all the recent Technet SQL Server Tips

It has been a long walk up for SQL Server, but I feel that we're almost at the summit with this version.

No comments:

Post a Comment