Get SQL Server details using T-SQL

Here is a T-SQL script which gives you the details of a SQL Server.
This will be very useful when you are gathering SQL Server information from multiple servers and works for SQL Server 2005 and above.

CREATE TABLE #ServerDetails(ID int,  Name  sysname, Internal_Value int, Value nvarchar(512))
INSERT #ServerDetails EXEC master.dbo.xp_msver

DECLARE @InstanceName nvarchar(50)
DECLARE @value VARCHAR(100)
DECLARE @RegKey_InstanceName nvarchar(500)
DECLARE @RegKey nvarchar(500)
DECLARE @AuditLevel int
DECLARE @DataDirectory nvarchar(500)
DECLARE @LogDirectory nvarchar(500)
DECLARE @BackupDirectory nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),
'MSSQLSERVER'))

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8
BEGIN
SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread
  @rootkey = 'HKEY_LOCAL_MACHINE',
  @key = @RegKey_InstanceName,
  @value_name = @InstanceName,
  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\'

    
EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='AuditLevel',
  @value=@AuditLevel OUTPUT

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='DefaultData',
  @value=@DataDirectory OUTPUT

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='DefaultLog',
  @value=@LogDirectory OUTPUT

EXEC master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE',
  @key=@RegKey,
  @value_name='BackupDirectory',
  @value=@BackupDirectory OUTPUT

END

SELECT  SERVERPROPERTY('ComputerNamePhysicalNetBIOS') [Machine Name]
    ,SERVERPROPERTY('ServerName') AS [SQL Server Name]
    ,SERVERPROPERTY('InstanceName') AS [Instance Name]
    ,SERVERPROPERTY('Collation') AS [Server Collation]
    ,'Microsoft SQL Server ' + CAST(SERVERPROPERTY('Edition') AS varchar(250)) AS Edition
    ,SERVERPROPERTY('ProductLevel') AS [Product Level]
    ,(SELECT Value FROM #ServerDetails WHERE Name = N'Language') AS [Language]
    ,(SELECT Value FROM #ServerDetails WHERE Name = N'Platform') AS [Platform]
    ,(SELECT 'Microsoft Windows NT ' + Value from #ServerDetails where Name = N'WindowsVersion') AS [Operating System]
    ,(SELECT Internal_Value FROM #ServerDetails WHERE Name = N'ProcessorCount') AS [Processors]
    ,(SELECT CAST(Internal_Value AS varchar)+ ' (MB)' FROM #ServerDetails WHERE Name = N'PhysicalMemory') AS Memory
    , CASE WHEN SERVERPROPERTY('IsClustered') = 1 THEN 'True' ELSE 'False' END AS IsClustered
    ,(SELECT value from sys.configurations where name='min server memory (MB)') AS 'Min Server Memory (MB)'
    ,(SELECT value from sys.configurations where name='max server memory (MB)') AS 'Max Server Memory (MB)'
    ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity mask') AS 'Automatically set processor affinity mask for all processor'
    ,(SELECT CASE WHEN value=0 THEN 'True' ELSE 'False' END from sys.configurations where name='affinity I/O mask') AS 'Automatically set I/O affinity mask for all processor'
    ,CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 1 THEN 'Windows Authentication Mode' 
    WHEN SERVERPROPERTY('IsIntegratedSecurityOnly')= 0 THEN 'SQL Server and Windows Authentication Mode' END AS [Server Authentication]
    ,CASE WHEN @AuditLevel = 0 THEN 'None' 
    WHEN @AuditLevel = 1 THEN 'Successful Logins Only'
    WHEN @AuditLevel = 2 THEN 'Failed Logins Only'
    WHEN @AuditLevel = 3 THEN 'Both Failed and Successful Logins'
    END AS [Audit Level]
    ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow remote connections to this Server'
    ,(select CASE WHEN value = 0 THEN 'unlimited' ELSE value END from sys.configurations where name='user connections') AS 'Max number of concurrent Connections'
    ,(select CASE WHEN value = 0 THEN 'No Timeout' ELSE value END from sys.configurations where name='remote query timeout (s)') AS 'Query Timeout (s)'
    ,(select CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations where name='remote access') AS 'Allow Remote Connections to this server'
    ,@DataDirectory AS 'Default Data Directory'
    ,@LogDirectory AS 'Default Log Directory'
    ,@BackupDirectory AS 'Default Backup Directory'
    ,(SELECT value from sys.configurations WHERE name='max degree of parallelism') AS 'Max Degree of Parallelism'
    ,(SELECT value from sys.configurations WHERE name='remote login timeout (s)') AS 'Remote Login Timeout (s)'
    ,(SELECT CASE WHEN value = 0 THEN 'False' WHEN value = 1 THEN 'True' END from sys.configurations WHERE name='scan for startup procs') AS 'Scan for Startup Procs'

DROP TABLE #ServerDetails

Performance Dashboard Reports - Microsoft SQL Server 2012

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.

Common performance problems that the dashboard reports may help to resolve include:

• CPU bottlenecks (and what queries are consuming the most CPU)
• IO bottlenecks (and what queries are performing the most IO)
• Index recommendations generated by the query optimizer (missing indexes)
• Blocking
• Latch contention

This is a downloadable available from Microsoft and can be downloaded from the link here.

This also works for SQL Server 2008 R2 and SQL Server 2008 as well

Reference: http://www.microsoft.com/download/en/details.aspx?id=29063

Cannot Connect to WMI provider - Error while trying to open SQL Server 2008 Configuration Manager

When you try to open SQL Server Configuration Manager, you might get an error which states
"Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]"

During the setup sometimes, some .mof files don't get installed and registered properly and this causes the above error to pop up.
To fix this issue,

1. Go to the path "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\" using command prompt.
2. And run the following command mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Once the commands execute successfully, you will be able to open the SQL Server configuration Manager.

Startup Parameters - A new tab in Denali's SQL Server Configuration manager

We all know that Denali was launched with many new things built within. In those wide range of new enhancements, a separate tab for Startup parameters is one among them.
To check this out,

1. Go to Denali's "SQL Server Configuration Manager"
2. Right-Click on a SQL Server Service and Choose "Properties"
3. Now, in the properties page you can find a new tab for "Startup Parameters"

The Older versions of SQL Server Configuration Manager used to show the "Startup Parameters" as part of "Advanced" Tab.

Different ways to check your SQL Server(s) Authentication mode

Checking the Authentication mode using T-SQL:

1. Using "xp_LoginConfig" extended Stored Procedure
   

   EXEC Master.dbo.xp_LoginConfig 'login mode'
   

   
   
2. Using "SERVERPROPERTY" Function
   

   SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
   WHEN 1 THEN 'Windows Authentication mode'   
   WHEN 0 THEN 'SQL Server and Windows Authentication mode'   
   END as [Authentication Mode]  
   
   

3. Using Registry
   

   DECLARE @Mode INT  
   EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
   N'Software\Microsoft\MSSQLServer\MSSQLServer',   
   N'LoginMode', @Mode OUTPUT  
   
   SELECT CASE @Mode    
   WHEN 1 THEN 'Windows Authentication mode'
   WHEN 2 THEN 'SQL Server and Windows Authentication mode'
   ELSE 'Not known'
   END as [Authentication Mode]  
   

Checking the Authentication mode using SSMS:

To check the Authentication mode using SSMS,

1. Right-Click on the Server
2. Choose "Properties"
3. Navigate to "Security" Page
4. Check "Server Authentication" Section