There are a number of ways to collect basic information about SQL Server, its version, edition and other top-level properties. What is the build, and current service pack level? What is the instance name vs. the actual server name? Is it clustered or stand-alone and is it virtual or is it physical? What is the security mode of the server? Another thing I’m often asked what version of .NET is currently installed on the SQL Server? Need a quick way to get all this info from one simple script? Read on.
While some of this information you can get from the older, time tested T-SQL command, select @@version, which returns system and build information for the current installation of SQL Server, its output is one long nvarchar string. See @@Version information on MSDN, by clicking the highlighted link
You would in fact, need to parse the string, in order to derive some of its useful information in readable format, and one that can be output to a column, and thus stored in a table. You can also use, as is suggested, the SERVERPROPERTY (Transact-SQL) function to retrieve the individual property values.
The SEVERPROPERTY function has been around for several versions now, and have added some useful parameters for SQL Server 2012-2014. One obvious example, with the release of 2012 is the AlwaysOn features, formerly known as HADR, is IsHadrEnabled. This new property tells us that AlwaysOn Availability Groups is enabled on this server instance. Again, this is only available in versions SQL 2012 and higher.
So, when I need a quick way to retrieve this basic data, I run a quick script I put together that uses both SERVERPROPERTY, and @@Version. I also like to use a batch script, or central management server, to run this against multiple SQL Servers in the environment. It comes in most handy, when doing inventory, and Microsoft true-ups. After I collect the data, I can store it, parse it, query it and run reports on it. I will leave that up to your imagination, though perhaps I’ll do a write-up on this further.
The script uses what I see as the most significant properties for the purposes I mentioned. You certainly can add some of the other properties, and modify the script to your own desires.
The properties I use here, are available and runs on versions 2005 through 2012 (haven’t tested in on 2014, but will likely work)
The output (for one server here), will look like this:
And now, here is the script:
SET NOCOUNT ON DECLARE @ver NVARCHAR(128) DECLARE @majorVersion NVARCHAR(4) SET @ver = CAST(SERVERPROPERTY('productversion') AS NVARCHAR) SET @ver = SUBSTRING(@ver,1,CHARINDEX('.',@ver)+1) SET @majorVersion = CAST(@ver AS nvarchar) SELECT SERVERPROPERTY('ServerName') AS [ServerName],SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], SERVERPROPERTY('ProductVersion') AS [ProductVersion], CASE @MajorVersion WHEN '8.0' THEN 'SQL Server 2000' WHEN '9.0' THEN 'SQL Server 2005' WHEN '10.0' THEN 'SQL Server 2008' WHEN '10.5' THEN 'SQL Server 2008 R2' WHEN '11.0' THEN 'SQL Server 2012' WHEN '12.0' THEN 'SQL Server 2014' END AS 'SQL', SERVERPROPERTY('ProductLevel') AS [ProductLevel], SERVERPROPERTY('Edition') AS [Edition], SERVERPROPERTY ('BuildClrVersion') AS NET, CASE SERVERPROPERTY('IsClustered') WHEN 0 THEN 'NO' WHEN 1 THEN 'YES' END AS [IsClustered], CASE When CHARINDEX('Hypervisor',@@VERSION)>0 THEN 'VM' ELSE 'PHYSICAL' END AS [VM_PHYSICAL], CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'WINDOWS AUTHENTICATION ONLY' WHEN 0 THEN 'SQL & WINDOWS AUTHENTICATION' END AS 'SECURITY MODE'
Hope this helps!
You can and should follow me on twitter: @Pearlknows
We offer a comprehensive performance review and 15-point health check of your SQL Server(s). Take our HealthySQL challenge - if we don't find anything wrong with your SQL Server, the report to you is FREE! Contact us at email@example.com