Technical Article

Quick SQL Server Configuration Summary Report

,

Quick SQL Server Configuration Summary Report

This script will detect and display a quick summary of your SQL Server installation/environment.

To view the report, open the script in SSMS and execute.There are temp tables created and dropped at end of execution.

Here is a list of information produced in the reported.

Display SQL Server name\Instance name

Display Installation Date

Display Machine Name           

Display Instance Name

Display Edition and BIT Level

Display Production Service Pack Level           

Display Production Name              

Display Production Version

Display Logical CPU Count

Display Maximum Memory (Megabytes)

Display Minimum Memory (Megabytes)  

Display IP Address

Display Port Number

Display Default Domain Name

Display Service Account name

Display Clustered Status

Display Kerberos

Display Security Mode

Display Audit Level

Display User Mode

Display Collation Type

Display SQL Server Errorlog Location

Display SQL Server Default Trace Location

Display Number of Link Servers

Display SysAdmin Members

Display ServerAdmin Members

Display Configuration setting

Display code that automatically executes on startup

Display SQL Service Status

Display Location of Database files

Display Link Servers

Display Database Collation type

Display Database Hard Drive Space Available

Display Database Information

Display Database Backup Information

Display SQL Job Status

Display SQL Mail Information

Display Database Mirroring Status

Display Database Log Shipping Status

Display Report Server (SSRS) Reports Information

Please execute on your test/development environment and verify results. 

I will be adding more to this script in the future so watch for updates. Please let me know you comments.

Thanks,

Rudy

USE MASTER
GO

SET NOCOUNT ON;

/* SQL Server Configuration Report  

Created by Rudy Panigas on Jan 21, 2015 

Jan 4,  2015 Version 1.0 - Initial build
Jan 11, 2015 Version 1.1 - 1.7 - Corrected logic and verified on other versions of SQL Server
Jan 21, 2015 Version 1.8 - Changed output format for better reading
Jan 24, 2015 Version 1.9 -  Added linked server Detection of
Jan 28, 2015 Version 2.0 - Added database collation, database mirroring, log shipping and SSRS information

----------------------- Version Control -------------------------------*/DECLARE @ScriptVersion CHAR(4)
SET @ScriptVersion = 2.0 -- Version number of this script
/*-------------------------------------------------------------------------*/
DECLARE 
  @CurrentDate NVARCHAR(50) -- Current data/time
, @SQLServerName NVARCHAR(50) --Set SQL Server Name
, @NodeName1 NVARCHAR(50) -- Name of node 1 if clustered
, @NodeName2 NVARCHAR(50) -- Name of node 2 if clustered
--, @NodeName3 NVARCHAR(50) /* -- remove remarks if more than 2 node cluster */--, @NodeName4 NVARCHAR(50) /*-- remove remarks if more than 2 node cluster */, @AccountName NVARCHAR(50) -- Account name used
, @StaticPortNumber NVARCHAR(50) -- Static port number
, @INSTANCENAME NVARCHAR(30) -- SQL Server Instance Name
, @VALUENAME NVARCHAR(20) -- Detect account used in SQL 2005, see notes below
, @KERB NVARCHAR(50) -- Is Kerberos used or not
, @DomainName NVARCHAR(50) -- Name of Domain
, @IP NVARCHAR(20)  -- IP address used by SQL Server
, @InstallDate NVARCHAR(20) -- Installation date of SQL Server
, @ProductVersion NVARCHAR(30) -- Production version
, @MachineName NVARCHAR(30) -- Server name
, @ServerName NVARCHAR(30) -- SQL Server name
, @Instance NVARCHAR(30) --  Instance name
, @EDITION NVARCHAR(30) --SQL Server Edition
, @ProductLevel NVARCHAR(20) -- Product level
, @ISClustered NVARCHAR(20) -- System clustered
, @ISIntegratedSecurityOnly NVARCHAR(50) -- Security level
, @ISSingleUser NVARCHAR(20) -- System in Single User mode
, @COLLATION NVARCHAR(30)  -- Collation type
, @physical_CPU_Count VARCHAR(4) -- CPU count
, @EnvironmentType VARCHAR(15) -- Physical or Virtual
, @MaxMemory NVARCHAR(10) -- Max memory
, @MinMemory NVARCHAR(10) -- Min memory
, @TotalMEMORYinBytes NVARCHAR(10) -- Total memory
, @ErrorLogLocation VARCHAR(500) -- location of error logs
, @TraceFileLocation VARCHAR(100) -- location of trace files
, @LinkServers VARCHAR(2) -- Number of linked servers found

SET @CurrentDate = (SELECT GETDATE())
SET @ServerName = (SELECT @@SERVERNAME)
PRINT 'SQL Server Configuration Report - Version '+@ScriptVersion
PRINT '----------------------------------------------------'
PRINT 'Report executed on '+@ServerName+' SQL Server at '+@CurrentDate
PRINT ' '

--> SQL Server Settings <--
PRINT '** Loading sp_configure details **'
PRINT ' '
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
SELECT 
         [name]
,[description]
,[value] 
,[minimum] 
,[maximum] 
,[value_in_use]
INTO #SQL_Server_Settings
FROM master.sys.configurations;

EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
PRINT ' '
PRINT '**sp_configure details loaded **'
PRINT ' ';

-- Detecting setting
----------------------------------------------------------------
PRINT '******** SQL Server Summary ********'
PRINT ' '
SET @SQLServerName = (SELECT @@ServerName) -- SQL Server name
PRINT 'Detection of SQL Server name\Instance name --> '+@SQLServerName
------------------------------------------------------------------------
SET @InstallDate = (SELECT  createdate FROM sys.syslogins where sid = 0x010100000000000512000000)
PRINT 'Detection of Installation Date --> '+@InstallDate
------------------------------------------------------------------------
SET @MachineName = (SELECT CONVERT(char(100), SERVERPROPERTY('MachineName'))) 
PRINT 'Detection of Machine Name --> '+@MachineName
------------------------------------------------------------------------
IF (SELECT CONVERT(char(50), SERVERPROPERTY('InstanceName'))) IS NULL
SET @InstanceName = 'Default Instance'
ELSE
SET @InstanceName = @InstanceName
PRINT 'Detection of Instance Name --> '+@InstanceName
SET @EDITION = (SELECT CONVERT(char(30), SERVERPROPERTY('EDITION')))
PRINT 'Detection of Edition and BIT Level --> '+@EDITION 
------------------------------------------------------------------------
SET @ProductLevel = (SELECT CONVERT(char(30), SERVERPROPERTY('ProductLevel')))
PRINT 'Detection of Production Service Pack Level --> '+@ProductLevel 
SET @ProductVersion = (SELECT CONVERT(char(30), SERVERPROPERTY('ProductVersion')))
PRINT 'Detection of Production Name --> '+@ProductVersion
------------------------------------------------------------------------
IF @ProductVersion LIKE '6.5%' SET @ProductVersion =  'SQL Server 6.5'
IF @ProductVersion LIKE '7.0%' SET @ProductVersion =  'SQL Server 7'
IF @ProductVersion LIKE '8.0%' SET @ProductVersion =  'SQL Server 2000'
IF @ProductVersion LIKE '9.0%' SET @ProductVersion =  'SQL Server 2005'  
IF @ProductVersion LIKE '10.0%'  SET @ProductVersion = 'SQL Server 2008' 
IF @ProductVersion LIKE '10.50%' SET @ProductVersion = 'SQL Server 2008R2' 
IF @ProductVersion LIKE '11.0%' SET @ProductVersion =  'SQL Server 2012' 
IF @ProductVersion LIKE '12.0%' SET @ProductVersion =  'SQL Server 2014' 
--IF @ProductVersion LIKE '14.0%' SET @ProductVersion =  'SQL Server 2016'  -- for future use
--IF @ProductVersion LIKE '15.0%' SET @ProductVersion =  'SQL Server 2018'  -- for future use
------------------------------------------------------------------------
PRINT 'Detection of Production Version --> '+@ProductVersion 
PRINT ' '
------------------------------------------------------------------------
SET @physical_CPU_Count = (SELECT cpu_count FROM sys.dm_os_sys_info)
PRINT 'Detection of Logical CPU Count --> '+@physical_CPU_Count
------------------------------------------------------------------------
/* This section only works on SQL 2012 and higher */
--IF(SELECT virtual_machine_type FROM sys.dm_os_sys_info) = 1
--SET @EnvironmentType = 'Virtual'
--ELSE
--SET @EnvironmentType = 'Physical'
--PRINT 'Detection of Environment Type --> '+@EnvironmentType
------------------------------------------------------------------------
SET @MaxMemory = (select CONVERT(char(10), [value_in_use]) from  #SQL_Server_Settings where name = 'max server memory (MB)')
SET @MinMemory = (select CONVERT(char(10), [value_in_use]) from  #SQL_Server_Settings where name = 'min server memory (MB)')
PRINT 'Detection of Maximum Memory (Megabytes) --> '+@MaxMemory
PRINT 'Detection of Minimum Memory (Megabytes) --> '+@MinMemory
------------------------------------------------------------------------
SELECT DEC.local_net_address INTO #IP FROM sys.dm_exec_connections AS DEC WHERE DEC.session_id = @@SPID;
SET @IP = (SELECT DEC.Local_Net_Address FROM sys.dm_exec_connections AS DEC WHERE DEC.session_id = @@SPID)
PRINT 'Detection of IP Address --> '+@IP;
------------------------------------------------------------------------
SET @StaticPortNumber = (SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID)
PRINT 'Detection of Port Number --> '+@StaticPortNumber
PRINT ' '
------------------------------------------------------------------------
SET @DomainName = (SELECT DEFAULT_DOMAIN())
PRINT 'Detection of Default Domain Name --> '+@DomainName
------------------------------------------------------------------------
--For Service Account Name - This line will work on SQL 2008R2 and higher only
--SET @AccountName = (SELECT top 1 service_account FROM sys.dm_server_services)
--So the lines below are being used until SQL 2005 is removed/upgraded
EXECUTE  master.dbo.xp_instance_regread
@rootkey      = N'HKEY_LOCAL_MACHINE',
@key          = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
@value_name   = N'ObjectName',
@value        = @AccountName OUTPUT
PRINT 'Detection of Service Account name --> '+@AccountName
------------------------------------------------------------------------
IF (SELECT CONVERT(char(30), SERVERPROPERTY('ISClustered'))) = 1
SET @ISClustered = 'Clustered'
ELSE
SET @ISClustered = 'Not Clustered'
PRINT 'Detection of Clustered Status --> '+@ISClustered 
------------------------------------------------------------------------
--cluster node names. Modify if there are more than 2 nodes in cluster
SELECT NodeName INTO #nodes FROM sys.dm_os_cluster_nodes 
IF @@rowcount = 0 
BEGIN 
SET @NodeName1 = 'NONE' -- NONE for no cluster
END
ELSE
BEGIN
SET @NodeName1 = (SELECT top 1 NodeName from #nodes)
SET @NodeName2 = (SELECT NodeName from #nodes where NodeName <> @NodeName1)
-- Add code here if more that 2 node cluster
    END

IF @NodeName1 = 'NONE'
BEGIN
PRINT 'Detection of Clustered --> SQL Server is not clustered'
END
ELSE
BEGIN
PRINT 'Detection of cluster node 1 --> '+@NodeName1
PRINT 'Detection of cluster node 2 --> '+@NodeName2
END
PRINT ' '
------------------------------------------------------------------------
SELECT net_transport, auth_scheme INTO #KERBINFO FROM sys.dm_exec_connections WHERE session_id = @@spid
IF @@rowcount = 0 
BEGIN 
SET @KERB = 'Kerberos not used in TCP network transport'
END
ELSE
BEGIN
SET @KERB = 'TCP is using Kerberos'
END
PRINT 'Detection of Kerberos --> '+@KERB
------------------------------------------------------------------------
IF (SELECT CONVERT(char(30), SERVERPROPERTY('ISIntegratedSecurityOnly'))) = 1
SET @ISIntegratedSecurityOnly = 'Windows Authentication Security Mode'
ELSE
SET @ISIntegratedSecurityOnly = 'SQL Server Authentication Security Mode'
PRINT 'Detection of Security Mode --> '+@ISIntegratedSecurityOnly 
------------------------------------------------------------------------
DECLARE @AuditLevel int,
@AuditLvltxt VARCHAR(50)
EXEC MASTER.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @AuditLevel OUTPUT

SELECT @AuditLvltxt = CASE 
WHEN @AuditLevel = 0THEN 'None'
WHEN @AuditLevel = 1THEN 'Successful logins only'
WHEN @AuditLevel = 2THEN 'Failed logins only'
WHEN @AuditLevel = 3THEN 'Both successful and failed logins'
ELSE 'Unknown'
END
PRINT 'Detection of Audit Level --> ' + @AuditLvltxt
PRINT  ' '
------------------------------------------------------------------------
IF (SELECT CONVERT(char(30), SERVERPROPERTY('ISSingleUser'))) = 1
SET @ISSingleUser = 'Single User'
ELSE
SET @ISSingleUser = 'Multi User'
PRINT 'Detection of User Mode --> '+@ISSingleUser 
------------------------------------------------------------------------
SET @COLLATION = (SELECT CONVERT(char(30), SERVERPROPERTY('COLLATION')))
PRINT 'Detection of Collation Type --> '+@COLLATION 
------------------------------------------------------------------------
SET @ErrorLogLocation = (SELECT REPLACE(CAST(SERVERPROPERTY('ErrorLogFileName') AS VARCHAR(500)), 'ERRORLOG',''))
PRINT 'Detection of SQL Server Errorlog Location --> ' +@ErrorLogLocation
------------------------------------------------------------------------
SET @TraceFileLocation = (SELECT REPLACE(CONVERT(VARCHAR(100),SERVERPROPERTY('ErrorLogFileName')), '\ERRORLOG','\log.trc'))
PRINT 'Detection of SQL Server Default Trace Location --> ' +@TraceFileLocation
------------------------------------------------------------------------
SET @LinkServers = (SELECT COUNT(*) FROM sys.servers WHERE is_linked ='1')
PRINT 'Detection of Number of Link Servers --> ' +@LinkServers
------------------------------------------------------------------------
PRINT ' '
PRINT 'Detection of SysAdmin Members'
PRINT ' '
IF (SELECT COUNT(*) FROM sys.server_principals) = 0
BEGIN
PRINT '** NoSysadmin Users Detection of ** '
END
ELSE
BEGIN
SELECT 'sysadmin' as 'Role'
,CONVERT (NVARCHAR(50), name) COLLATE DATABASE_DEFAULT AS 'Login\Member Name' 
FROM sys.server_principals
WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1
END
PRINT ' '
------------------------------------------------------------------------
PRINT 'Detection of ServerAdmin Members'
PRINT ' '
IF (SELECT COUNT(*) FROM sys.server_principals WHERE (type ='R') and (name='serveradmin')) = 0
BEGIN 
PRINT '** No ServerAdmin Users Detection of ** '
END
ELSE
BEGIN
SELECT CONVERT (NVARCHAR(20),r.name) AS'Role'
, CONVERT (NVARCHAR(50),p.name)  AS 'Login\Member Name'
  FROMsys.server_principals r
  JOINsys.server_role_members m  ONr.principal_id = m.role_principal_id
  JOINsys.server_principals p ONp.principal_id = m.member_principal_id
 WHERE(r.type ='R')and(r.name='serveradmin')
 END
PRINT ' '
------------------------------------------------------------------------
PRINT 'Detection of configuration setting' 
PRINT ' '
SELECT [name] as 'Configuration Setting' ,(CONVERT (CHAR(20),[value_in_use] )) as 'Value in Use' FROM #SQL_Server_Settings
------------------------------------------------------------------------
PRINT 'Detection of code that automatically executes on startup'
PRINT ' '

IF (SELECT COUNT(*) FROM sys.procedures WHERE is_auto_executed = 1) = 0
BEGIN 
PRINT '** No code that automatically execute on startup Detection of ** '
END
ELSE
BEGIN
SELECT CONVERT (NVARCHAR(35), name) AS 'Name'
, CONVERT (NVARCHAR(25), type_desc) AS 'Type'
,  create_date AS 'Created Date'
,  modify_date AS 'Modified Date'
FROM sys.procedures
WHERE is_auto_executed = 1
END
PRINT ' ';
------------------------------------------------------------------------
PRINT 'Detection of SQL Service Status' 
PRINT ' '
--> SQL Server Services Status <--
CREATE TABLE #RegResult
(ResultValue NVARCHAR(4))

CREATE TABLE #ServicesServiceStatus
( 
 RowID INT IDENTITY(1,1)
,ServerName NVARCHAR(30) 
,ServiceName NVARCHAR(45)
,ServiceStatus varchar(15)
,StatusDateTime DATETIME DEFAULT (GETDATE())
,PhysicalSrverName NVARCHAR(50)
)

DECLARE 
 @ChkInstanceName nvarchar(128)
,@ChkSrvName nvarchar(128)
,@TrueSrvName nvarchar(128)
,@SQLSrv NVARCHAR(128)
,@PhysicalSrvName NVARCHAR(128)
,@FTS nvarchar(128)
,@RS nvarchar(128)
,@SQLAgent NVARCHAR(128)
,@OLAP nvarchar(128)
,@REGKEY NVARCHAR(128)

SET @PhysicalSrvName = CAST(SERVERPROPERTY('MachineName') AS VARCHAR(128)) 
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)) 
SET @ChkInstanceName = @@serverName

IF @ChkSrvName IS NULL
BEGIN 
SET @TrueSrvName = 'MSQLSERVER'
SELECT @OLAP = 'MSSQLServerOLAPService' 
SELECT @FTS = 'MSFTESQL' 
SELECT @RS = 'ReportServer' 
SELECT @SQLAgent = 'SQLSERVERAGENT'
SELECT @SQLSrv = 'MSSQLSERVER'
END 
ELSE
BEGIN
SET @TrueSrvName =  CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)) 
SET @SQLSrv = '$'+@ChkSrvName
 SELECT @OLAP = 'MSOLAP' + @SQLSrv/*Setting up proper service name*/SELECT @FTS = 'MSFTESQL' + @SQLSrv 
SELECT @RS = 'ReportServer' + @SQLSrv
SELECT @SQLAgent = 'SQLAgent' + @SQLSrv
SELECT @SQLSrv = 'MSSQL' + @SQLSrv
END 
;
/* ---------------------------------- SQL Server Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLSrv

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1 
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC xp_servicecontrol N'QUERYSTATE',@SQLSrv
UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE 
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus set ServiceName = 'MS SQL Server Service' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

/* ---------------------------------- SQL Server Agent Service Section -----------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@SQLAgent

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1 
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC xp_servicecontrol N'QUERYSTATE',@SQLAgent
UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE 
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus set ServiceName = 'SQL Server Agent Service' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

/* ---------------------------------- SQL Browser Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\SQLBrowser'

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1 
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'sqlbrowser'
UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE 
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus set ServiceName = 'SQL Browser Service - Instance Independent' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

/* ---------------------------------- Integration Service Section ----------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\MsDtsServer'

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1 
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',N'MsDtsServer'
UPDATE #ServicesServiceStatus set ServiceName = 'Integration Service - Instance Independent' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE 
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus set ServiceName = 'Intergration Service - Instance Independent' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

/* ---------------------------------- Reporting Service Section ------------------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@RS

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1 
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@RS
UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE 
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus set ServiceName = 'Reporting Service' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

/* ---------------------------------- Analysis Service Section -------------------------------------------------*/IF @ChkSrvName IS NULL
BEGIN 
SET @OLAP = 'MSSQLServerOLAPService'
END
ELSE
BEGIN
SET @OLAP = 'MSOLAP'+'$'+@ChkSrvName
SET @REGKEY = 'System\CurrentControlSet\Services\'+@OLAP
END

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1 
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@OLAP
UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE 
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus set ServiceName = 'Analysis Services' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

/* ---------------------------------- Full Text Search Service Section -----------------------------------------*/SET @REGKEY = 'System\CurrentControlSet\Services\'+@FTS

INSERT #RegResult ( ResultValue ) EXEC master.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@REGKEY

IF (SELECT ResultValue FROM #RegResult) = 1 
BEGIN
INSERT #ServicesServiceStatus (ServiceStatus)
EXEC master.dbo.xp_servicecontrol N'QUERYSTATE',@FTS
UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END
ELSE 
BEGIN
INSERT INTO #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
UPDATE #ServicesServiceStatus set ServiceName = 'Full Text Search Service' where RowID = @@identity
UPDATE #ServicesServiceStatus set ServerName = @TrueSrvName where RowID = @@identity
UPDATE #ServicesServiceStatus set PhysicalSrverName = @PhysicalSrvName where RowID = @@identity
TRUNCATE TABLE #RegResult
END

SELECT ServerName as 'SQL Server\Instance Name'
, ServiceName as 'Service Name'
, ServiceStatus as 'Service Status'
, StatusDateTime as 'Status Date\Time'
FROM  #ServicesServiceStatus;
------------------------------------------------------------------------
PRINT 'Detection of location of Database files'
PRINT ' '
  
SELECT CONVERT(NVARCHAR(3), database_id) AS 'Database ID'
, CONVERT(NVARCHAR(45), name) AS 'Database Name'
, CONVERT(NVARCHAR(100), physical_name) AS 'Physical Location'
, CONVERT(NVARCHAR(16), type_desc) AS 'Type'
FROM sys.master_files 
PRINT ' '
------------------------------------------------------------------------
PRINT 'Detection of Link Servers'
SELECT * INTO #LinkInfo  FROM sys.servers WHERE is_linked ='1'

SELECT 
    CONVERT(nvarchar(25), name) as 'Name'
  , CONVERT(nvarchar(25), product) as 'Product'
  , CONVERT(nvarchar(25), provider) as 'Provider'
  , CONVERT(nvarchar(25),data_source) as 'Data Source'
 /* Uncomment the following if you want more information */  --, CONVERT(nvarchar(20),location) as 'Location'     
  --, CONVERT(nvarchar(20),provider_string) as 'Provider String'   
  --, CONVERT(nvarchar(20),[catalog]) as 'Catalog'   
  --,connect_timeout 
  --,query_timeout 
  --,is_linked 
  --,is_remote_login_enabled 
  --,is_rpc_out_enabled 
  --,is_data_access_enabled
  --,is_collation_compatible 
  --,uses_remote_collation 
  --,CONVERT(nvarchar(20),collation_name)  
  --,lazy_schema_validation 
  --,is_system 
  --,is_publisher 
  --,is_subscriber 
  --,is_distributor 
  --,is_nonsql_subscriber 
  --,is_remote_proc_transaction_promotion_enabled 
  --,modify_date
FROM #LinkInfo
IF @@rowcount = 0 
BEGIN 
PRINT '** No link server connections Detection of ** '
END
------------------------------------------------------------------------
PRINT ' ';
PRINT 'Detection of Database Collation type'
PRINT ' '
PRINT ' Case sensitivity Descriptions'
PRINT ' Case Insensitive = CICase Sensitive = CS'
PRINT ' Accent Insensitive = AIAccent Sensitive = AS'
PRINT ' Kanatype Insensitive = nullKanatype Sensitive = KS'
PRINT ' Width Insensitive = nullWidth Sensitive = WS'
PRINT ' ';
SELECT NAME, COLLATION_NAME INTO #Collation FROM sys.Databases ORDER BY DATABASE_ID ASC;
SELECT 
      CONVERT(nvarchar(35), name) as 'Database Name'
, CONVERT(nvarchar(35), COLLATION_NAME) as 'Collation Type'
FROM #Collation
PRINT ' ';
------------------------------------------------------------------------
PRINT 'Detection of Database Hard Drive Space Available'   
PRINT ' '

CREATE TABLE #HD_space
(Drive varchar(2) NOT NULL,
[MB free] int NOT NULL)

INSERT INTO #HD_space(Drive, [MB free])
EXEC master.sys.xp_fixeddrives;

SELECT  CONVERT(nvarchar(45), sys.databases.name) as 'Database Name'
, CONVERT(VARCHAR,SUM(size)*8/1024) AS 'Total disk space (Megabytes)'
 FROM sys.databases 
JOIN sys.master_files 
ON sys.databases.database_id=sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY sys.databases.name 

SELECT Drive AS 'Drive Letter'
   ,[MB free]  AS 'Free Disk Space (Megabytes)'
   FROM #HD_space
IF @@rowcount = 0 
BEGIN 
PRINT '** No Hard Drive Information ** '
END
PRINT ' '
------------------------------------------------------------------------
PRINT 'Detection of Database Information'
PRINT ' '
SELECT 
 D.database_id
,D.[name]
,D.[create_date]
,D.[compatibility_level] 
,D.[user_access_desc]
,D.[state_desc]
,D.[recovery_model_desc]
INTO #Databases_Details
FROM SYS.DATABASES D 
INNER JOIN sys.master_files S
ON D.database_id= S.database_id
WHERE s.file_id = 1 

SELECT 
 database_id AS 'Database ID'
,CONVERT(nvarchar(45), [name]) AS 'Name'
,CONVERT(nvarchar(5), [compatibility_level]) AS 'Compatibility'
,CONVERT(nvarchar(15), [user_access_desc]) AS 'User Access'
,CONVERT(nvarchar(10), [state_desc]) AS 'Status'
,CONVERT(nvarchar(10), [recovery_model_desc]) AS 'Recovery Model'
FROM #Databases_Details;
------------------------------------------------------------------------
PRINT 'Detection of Database Backup Information'
PRINT ' '
SELECT 
B.name as Database_Name
, ISNULL(STR(ABS(DATEDIFF(day, GetDate()
, MAX(Backup_finish_date))))
, 'NEVER') as DaysSinceLastBackup
, ISNULL(Convert(char(10)
, MAX(backup_finish_date)
, 101)
, 'NEVER') as LastBackupDate
INTO #Last_Backup_Dates FROM master.dbo.sysdatabases B 
LEFT OUTER JOIN msdb.dbo.backupset A 
ON A.database_name = B.name AND A.type = 'D' 
GROUP BY B.Name 
ORDER BY B.name;

SELECT 
 CONVERT(nvarchar(45),Database_Name) AS 'Database Name'
,DaysSinceLastBackup AS 'Days Since Backup Date'
,LastBackupDate AS 'Last Date Backed Up'
 FROM #Last_Backup_Dates
IF @@rowcount = 0 
BEGIN 
PRINT '** No SQL Backup Information ** '
END;
------------------------------------------------------------------------
PRINT 'Detection of SQL Job Status'
PRINT ' '
SELECT name INTO #Failed_SQL_Jobs FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B 
WHERE A.job_id = B.job_id AND B.last_run_outcome = 0 ;

IF (SELECT COUNT(*) FROM #Failed_SQL_Jobs) = 0 
BEGIN 
PRINT '** No SQL Job Information ** '
PRINT ' '
END
BEGIN
SELECT CONVERT(nvarchar(75), name) AS 'SQL Job Name' FROM #Failed_SQL_Jobs
END
------------------------------------------------------------------------
SELECT name INTO #Disabled_Jobs FROM msdb.dbo.sysjobs 
WHERE enabled = 0 ORDER BY name;

SELECT CONVERT(nvarchar(75), name) AS 'Disabled SQL Jobs' FROM #Disabled_Jobs
IF @@rowcount = 0 
BEGIN 
PRINT '** No Disabled Job Information ** '
END;
PRINT ' '
------------------------------------------------------------------------
PRINT 'Detection of SQL Mail Information'
PRINT ' '
CREATE TABLE #Database_Mail_Details
(Status NVARCHAR(7))

IF EXISTS(SELECT * FROM master.sys.configurations WHERE configuration_id = 16386 AND value_in_use =1)
BEGIN
INSERT INTO #Database_Mail_Details (Status)
Exec msdb.dbo.sysmail_help_status_sp
END

CREATE TABLE #Database_Mail_Details2
(principal_id VARCHAR(4)
,principal_name VARCHAR(35)
,profile_id VARCHAR(4)
,profile_name VARCHAR(35)
,is_default VARCHAR(4))

INSERT INTO #Database_Mail_Details2
(principal_id
,principal_name
,profile_id
,profile_name
,is_default)
EXEC msdb.dbo.sysmail_help_principalprofile_sp ;

IF (SELECT COUNT (*) FROM #Database_Mail_Details) = 0
BEGIN
PRINT '** No Database Mail Service Status Information ** '
END
ELSE
BEGIN
SELECT [Status] AS 'Database Mail Service Status' FROM #Database_Mail_Details
END;
PRINT ' '

SELECT 
principal_id  
, principal_name
,profile_id
,profile_name
,is_default
 FROM #Database_Mail_Details2
IF @@rowcount = 0 
BEGIN 
PRINT ' ** No SQL Mail Service Details Information **'

END;
PRINT ' '
------------------------------------------------------------------------
PRINT 'Detection of Database Mirroring Status'
PRINT ' '
SELECT DB.name,
CASE
    WHEN MIRROR.mirroring_state is NULL THEN 'Database Mirroring not configured and/or set'
    ELSE 'Mirroring is configured and/or set'
END AS MirroringState
INTO #Database_Mirror_Stats
FROM sys.databases DB INNER JOIN sys.database_mirroring MIRROR
ON DB.database_id=MIRROR.database_id WHERE DB.database_id > 4 ORDER BY DB.NAME;

IF (SELECT COUNT(*) FROM #Database_Mirror_Stats) = 0
BEGIN
PRINT ' ** No Mirroring Information Detection of **'
PRINT ' '
END
ELSE
BEGIN
SELECT CONVERT(nvarchar(35),name) AS 'Database Name'
,MirroringState AS 'Mirroring State'
FROM #Database_Mirror_Stats
END;

SELECT db_name(database_id) as 'Mirror DB_Name', 
CASE mirroring_state 
WHEN 0 THEN 'Suspended' 
WHEN 1 THEN 'Disconnected from other partner' 
WHEN 2 THEN 'Synchronizing' 
WHEN 3 THEN 'Pending Failover' 
WHEN 4 THEN 'Synchronized' 
WHEN null THEN 'Database is inaccesible or is not mirrored' 
END as 'Mirroring_State', 
CASE mirroring_role 
WHEN 1 THEN 'Principal' 
WHEN 2 THEN 'Mirror' 
WHEN null THEN 'Database is not mirrored or is inaccessible' 
END as 'Mirroring_Role', 
CASE mirroring_safety_level 
WHEN 0 THEN 'Unknown state' 
WHEN 1 THEN 'OFF (Asynchronous)' 
WHEN 2 THEN 'FULL (Synchronous)' 
WHEN null THEN 'Database is not mirrored or is inaccessible' 
END as 'Mirror_Safety_Level', 
Mirroring_Partner_Name as 'Mirror_Endpoint', 
Mirroring_Partner_Instance as 'Mirror_ServerName', 
Mirroring_Witness_Name as 'Witness_Endpoint', 
CASE Mirroring_Witness_State 
WHEN 0 THEN 'Unknown' 
WHEN 1 THEN 'Connected' 
WHEN 2 THEN 'Disconnected' 
WHEN null THEN 'Database is not mirrored or is inaccessible' 
END as 'Witness_State', 
Mirroring_Connection_Timeout as 'Failover Timeout in seconds', 
Mirroring_Redo_Queue, 
Mirroring_Redo_Queue_Type 
INTO #DB_Mirror_Details
FROM sys.Database_mirroring WHERE mirroring_role is not null;

IF (SELECT COUNT(*) FROM #DB_Mirror_Details) = 0
BEGIN 
PRINT ' ** No Mirroring Configuration Information Detection of**'
END
ELSE
BEGIN
SELECT * FROM #DB_Mirror_Details
END
PRINT ' '
------------------------------------------------------------------------
PRINT 'Detection of Database Log Shipping Status'
PRINT ' '
CREATE TABLE #LogShipping
([status] BIT
, [is_primary] BIT
, [server] sysname
, [database_name] sysname
, [time_since_last_backup] INT
, [last_backup_file] NVARCHAR(50)
, [backup_threshold] INT
, [is_backup_alert_enabled] BIT
, [time_since_last_copy] INT
, [last_copied_file] NVARCHAR(50)
, [time_since_last_restore] INT
, [last_restored_file]  NVARCHAR(50)
, [last_restored_latency] INT
, [restore_threshold] INT
, [is_restore_alert_enabled] BIT)
INSERT INTO #LogShipping
EXEC sp_help_log_shipping_monitor;

IF (SELECT COUNT(*) FROM #LogShipping) = 0
BEGIN 
PRINT '** No Database Log Shipping Information Detection of ** '
END
ELSE
BEGIN
SELECT * FROM #LogShipping
END
PRINT ' ';
------------------------------------------------------------------------
PRINT 'Detection of Report Server (SSRS) Reports Information <--'
PRINT ' '

IF EXISTS (SELECT name FROM sys.databases where name = 'ReportServer')
BEGIN
IF (SELECT COUNT(*) FROM reportserver.dbo.Catalog) = 0
BEGIN 
PRINT '** No Report Server (SSRS) Reports Information ** '
END
ELSE
BEGIN
SELECT CONVERT(nvarchar(20),Rol.RoleName) AS 'Role Name'
,CONVERT(nvarchar(35),Us.UserName) AS 'User Name'
,CONVERT(nvarchar(35),Cat.[Name]) AS 'Report Name'
,CASE Cat.Type WHEN 1 THEN 'Folder' WHEN 2 THEN 'Report' 
WHEN 3 THEN 'Resource' WHEN 4 THEN 'Linked Report' 
WHEN 3 THEN 'Data Source' ELSE '' END AS 'Catalog Type'
,CONVERT(nvarchar(35),Cat.Description) AS'Description'
FROM reportserver.dbo.Catalog Cat 
INNER JOIN reportserver.dbo.Policies Pol ON Cat.PolicyID = Pol.PolicyID
INNER JOIN reportserver.dbo.PolicyUserRole PUR ON Pol.PolicyID = PUR.PolicyID 
INNER JOIN reportserver.dbo.Users Us ON PUR.UserID = Us.UserID 
INNER JOIN reportserver.dbo.Roles Rol ON PUR.RoleID = Rol.RoleID
WHERE   Cat.Type in (1,2)
ORDER BY Cat.PATH 
END
END
ELSE
BEGIN 
PRINT '** No SSRS Reports Information Detection of ** '
END
------------------------------------------------------------------------
-- Performing clean up
DROP TABLE #KERBINFO;
DROP TABLE #nodes;
DROP TABLE #IP;
DROP TABLE #SQL_Server_Settings;
DROP TABLE #ServicesServiceStatus;
DROP TABLE #RegResult;
DROP TABLE #LinkInfo;
DROP TABLE #Collation;
DROP TABLE #HD_space;
DROP TABLE #Last_Backup_Dates;
DROP TABLE #Failed_SQL_Jobs;
DROP TABLE #Disabled_Jobs;
DROP TABLE #Database_Mail_Details;
DROP TABLE #Database_Mail_Details2;
DROP TABLE #Database_Mirror_Stats;
DROP TABLE #DB_Mirror_Details;
DROP TABLE #LogShipping;
DROP TABLE #Databases_Details;

GO
------------------------------------------------------------------------
PRINT ' '
PRINT 'End of SQL Server Configuration Report'
GO

Rate

4.13 (23)

You rated this post out of 5. Change rating

Share

Share

Rate

4.13 (23)

You rated this post out of 5. Change rating