-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQLServerReport
176 lines (149 loc) · 10.5 KB
/
SQLServerReport
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
-- Author: Sirajudeen Mohammed Ibrahim
-- Description: This script pulls SQL Server Inventory, except for FCI Cluster name (which is still WIP mode)
-- Support: This Script has been validated in SQL Server Versions 2016 and above
-- Type: TSQL
-- For: DBA
-- Required Permission: CONNECT SQL,VIEW ANY DEFINITION,VIEW SERVER STATE
-- Created Date: 2021-September
-- Updated Date: 2023-March as covering more than 1 AG data, DB details, Application_Name etc.,
-- Updated Date: 2023-June as including LinkedServer, CDC, Encryption and Replication details
-- Updated Date: 2023-August as including AG Routing and FullText Search Installer Dependency details
-- Code Starts Here --
-- Execute in Registered Server or in Chosen Instance --
-- Detailed Report--
-- Create Temp Table1 --
CREATE TABLE #CPUValues(
[index] SMALLINT,
[description] VARCHAR(128),
[server_cores] SMALLINT,
[value] VARCHAR(5)
)
-- Create Temp Table2 --
CREATE TABLE #MemoryValues(
[index] SMALLINT,
[description] VARCHAR(128),
[server_memory] DECIMAL(10,2),
[value] VARCHAR(64)
)
-- Create Temp Table3 --
CREATE TABLE #Drives(
[drive] VARCHAR(1),
[Mb Free] bigint
)
-- Populate Temp Table1 --
INSERT INTO #CPUValues
EXEC xp_msver 'ProcessorCount'
-- Populate Temp Table2 --
INSERT INTO #MemoryValues
EXEC xp_msver 'PhysicalMemory'
-- Populate Temp Table3 --
INSERT INTO #Drives
EXEC XP_FIXEDDRIVES
-- Main Derivation_1 --
-- Instance, Server related --
SELECT
DISTINCT
CONVERT(varchar(250), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) COLLATE Latin1_General_CI_AS AS [CurrentHost],
CONVERT(varchar(250), ISNULL(STUFF((select ', ' + cluster_name from sys.dm_hadr_cluster FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS [Cluster_Name_HAG],
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + servicename FROM sys.dm_server_services FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS [Service_Name],
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + service_account FROM sys.dm_server_services FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS [Service_Account],
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + status_desc FROM sys.dm_server_services FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS [Service_Status],
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + member_name FROM sys.dm_hadr_cluster_members WHERE member_type=0 FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS [ClusterNodes_HAG],
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + member_state_desc FROM sys.dm_hadr_cluster_members WHERE member_type=0 FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS [CluserNodes_HAG_Status],
(SELECT (DATALENGTH(CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + member_state_desc FROM sys.dm_hadr_cluster_members WHERE member_type=0 FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS)-DATALENGTH(REPLACE(CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + member_state_desc FROM sys.dm_hadr_cluster_members WHERE member_type=0 FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS,'UP','')))/DATALENGTH('UP')) AS HAG_ServerCount,
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + dns_name from sys.availability_group_listeners FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS HAG_Listener_Name,
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + ip_configuration_string_from_cluster from sys.availability_group_listeners FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS HAG_Listener_Ips,
CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '11%' THEN 'SQL Server 2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '12%' THEN 'SQL Server 2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '13%' THEN 'SQL Server 2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '14%' THEN 'SQL Server 2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '15%' THEN 'SQL Server 2019'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('PRODUCTVERSION')) like '16%' THEN 'SQL Server 2022'
ELSE 'UNKNOWN'
END AS SQL_VERSION,
OSVersion = SUBSTRING(RIGHT(@@version, LEN(@@version)-charindex (' ON ',@@VERSION)-2),1,CHARINDEX('<',RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ',@@VERSION)))),
SERVERPROPERTY('ProductLevel') AS SP_CU_Level,
SUBSTRING (@@version, Charindex( '(', @@version )+1, Charindex( ')', @@version )-Charindex( '(', @@version )-1) SP_CU_Installed,
SUBSTRING(CONVERT(VARCHAR(255),SERVERPROPERTY('EDITION')),0,CHARINDEX('Edition',CONVERT(VARCHAR(255),SERVERPROPERTY('EDITION')))) + 'Edition' AS SQL_EDITION,
ISNULL(STUFF((SELECT DISTINCT ', ' + CAST(local_tcp_port AS NVARCHAR(MAX))
FROM sys.dm_exec_connections
WHERE local_net_address is not null and protocol_type='TSQL'
FOR XML PATH('')), 1, 1, '' ), '') AS DB_Port,
ISNULL(STUFF((SELECT ', ' + CAST(PORT AS NVARCHAR(MAX)) from sys.availability_group_listeners FOR XML PATH('')), 1, 1, '' ), '') AS HAG_Port,
ISNULL(STUFF((SELECT DISTINCT ', ' + SUBSTRING(CAST(value_data as nvarchar (max)), 1, 5) from sys.dm_server_registry where cast (value_data as varchar (max)) like '%-T%' FOR XML PATH('')), 1, 1, '' ), '') AS TRACE_Flags,
(SELECT ROUND(CONVERT(DECIMAL(10,2),server_memory/1024.0),1) FROM #MemoryValues) AS Server_Available_Memory,
(SELECT CONVERT(varchar(80),Value_in_use)/1000 FROM sys.configurations WHERE [name] = 'max server memory (MB)') AS Server_Configured_Memory,
(SELECT Server_Cores FROM #CPUValues) AS Server_Cores,
(SELECT Server_type = CASE WHEN dosi.virtual_machine_type = 1 THEN 'Virtual' ELSE 'Physical' END FROM sys.dm_os_sys_info dosi) Server_Type,
(SELECT COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS OnlineCpuCount,
(SELECT cpu_count FROM sys.dm_os_sys_info O) AS LogicalCpuCount,
(SELECT hyperthread_ratio FROM sys.dm_os_sys_info) AS Hyperthread_Ratio,
(SELECT cpu_count/hyperthread_ratio FROM sys.dm_os_sys_info) AS Physical_CPU_Count,
(SELECT sqlserver_start_time FROM sys.dm_os_sys_info) SQL_Last_Start_Time,
'Not yet Derived' AS Cluster_Name_FCI,
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + NodeName FROM fn_virtualservernodes() FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS [ClusterNodes_FCI],
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + UPPER(status_description) FROM sys.dm_os_cluster_nodes FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS ClusterNodes_FCI_Status,
(SELECT (DATALENGTH(CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + UPPER(status_description) FROM sys.dm_os_cluster_nodes FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS)-DATALENGTH(REPLACE(CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + UPPER(status_description) FROM sys.dm_os_cluster_nodes FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS,'UP','')))/DATALENGTH('UP') ) AS FCI_ServerCount,
CONVERT(varchar(250), ISNULL(STUFF((SELECT ', ' + DriveName FROM sys.dm_io_cluster_shared_drives FOR XML PATH('')), 1, 1, '' ), '')) COLLATE Latin1_General_CI_AS AS [CluserNodes_Shared_FCI_Drives]
INTO #MainDerivation_1
-- Interim table for User Databases --
DECLARE @DB_Name TABLE(DBName SYSNAME, Status_Desc CHAR(25))
INSERT @DB_Name SELECT NAME, STATE_DESC FROM sys.databases WHERE DATABASE_ID >4 -- excludes system Databases
-- Main Derivation_2 --
-- Derivation for Custom Fields --
SELECT
(SELECT COUNT(*) FROM @DB_Name) UserDB_Count,
CONVERT(varchar(500), ISNULL(STUFF((SELECT ', ' + DBName FROM @DB_Name FOR XML PATH('')), 1, 1, '' ), ''))User_DB_Names,
(select CONVERT(decimal(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) from sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id WHERE d.database_id > 4) UserDBSize_GB,
(SELECT COUNT(*) FROM @DB_Name WHERE Status_Desc <> 'ONLINE' ) InactiveUserDB_Count,
(SELECT
CASE WHEN CONVERT(decimal(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) IS NULL THEN 0
ELSE CONVERT(decimal(10,2),(SUM(size * 8.00) / 1024.00 / 1024.00)) END
FROM sys.master_files mf INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 AND d.State_Desc <> 'ONLINE') InactiveUserDBSize_GB,
CASE WHEN MD1.Service_Account LIKE '%NT%' THEN 'Configure_SVC-Account' ELSE 'No_Action' END Service_Account_Action,
CASE WHEN (MD1.Server_Configured_Memory >= MD1.Server_Available_Memory OR MD1.Server_Available_Memory <= MD1.Server_Configured_Memory) THEN 'Configure_Memory' ELSE 'No_Action' END Server_Memory_Action,
CASE WHEN MD1.Server_Cores <> MD1.OnlineCpuCount THEN 'Configure_SQL-License/Core' ELSE 'No_Action' END Server_SQL_License_Action,
MD1.*
INTO #MainDerivation_2
FROM #MainDerivation_1 MD1
-- Final_Derivation --
-- With hardcoded Application Name/DB Name logic as deriving from Database name or Host Name --
-- CDC, LinkedServer, Encryption, Replication, AG Routing and FT Search dependency included
SELECT
GETDATE() Report_DateTime,
(SELECT
CASE WHEN User_DB_Names LIKE '%AG_Report%' THEN 'SQLServer_Monitor'
WHEN User_DB_Names LIKE '%%' THEN 'Embed_ApplicationName'
WHEN CurrentHost LIKE '%%' THEN 'Embed_ApplicationName'
ELSE NULL END
FROM #MainDerivation_1) Application_Name,
(SELECT
CASE WHEN (User_DB_Names LIKE '%Report%' OR User_DB_Names LIKE '%PowerBI%') THEN 'True' ELSE 'False' END FROM #MainDerivation_1) RS_PBI_Dependency,
(SELECT
CASE WHEN User_DB_Names LIKE '%SSISDB%' THEN 'True' ELSE 'False' END FROM #MainDerivation_1) SSIS_Dependency,
(SELECT
COUNT(*) FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id WHERE is_linked=1) LinkedServer_Count,
(SELECT
COUNT(*) FROM sys.tables st WHERE is_replicated = 1) Replication_Count,
(SELECT
COUNT(is_cdc_enabled) FROM sys.databases WHERE is_cdc_enabled=1) CDC_Count,
(SELECT
COUNT(*) FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id WHERE is_encrypted <> 0) Encrypted_DBCount,
(SELECT
CASE WHEN COUNT(*)!=0 THEN 'True' ELSE 'False' END FROM sys.availability_read_only_routing_lists) AG_Routing,
(SELECT
CASE WHEN SERVERPROPERTY('IsFullTextInstalled')=1 THEN 'True' ELSE 'False' END)FullTextSearch_Installed,
MD2.*
FROM #MainDerivation_2 MD2
-- DROP Temp Tables --
DROP TABLE #CPUValues
DROP TABLE #MemoryValues
DROP TABLE #Drives
DROP TABLE #MainDerivation_1
DROP TABLE #MainDerivation_2
-- Code Ends Here --