Before we migrate or upgrade we need to know some critical information that helps us find out about how SQL/System will be licensed based on CPU/Socket/Cores, some other information related to collation to find out what is the best way to consolidate Databases.

so I made this scrip that helped to get the basic informations that i needed, I decided to share it with the community and hopefully will be helpful for you.

 

 

 

USE Master
GO

--Get the default system values
DECLARE @configure TABLE ([name] [varchar](255) NULL,[value] int NULL)

IF OBJECT_ID('tempdb..#TEMP_CONFIG') IS NOT NULL DROP TABLE #TEMP_CONFIG
CREATE TABLE #TEMP_CONFIG ([name] nvarchar(35),[value] int)
INSERT INTO #TEMP_CONFIG SELECT name , CONVERT(int, value_in_use) FROM sys.configurations WHERE name = 'show advanced options' OR name = 'xp_cmdshell'

DECLARE @xp_cmdshell_status VARCHAR (1)
DECLARE @sp_configure_status VARCHAR (1)

SELECT @sp_configure_status = CONVERT(VARCHAR(1), value_in_use) FROM sys.configurations where name = 'show advanced options'
SELECT @xp_cmdshell_status = CONVERT(VARCHAR(1), value_in_use) FROM sys.configurations where name = 'xp_cmdshell'

--Enable Change in Advanced Options
EXEC SP_CONFIGURE N'show advanced options', 1
GO
RECONFIGURE
GO
--Enable XP_cmdshell
EXEC SP_CONFIGURE N'xp_cmdshell', 1
GO
RECONFIGURE
GO

--Get the system CPU info
DECLARE @xp_syscpu TABLE ([DeviceID] [varchar](255) NULL)
DECLARE @xp_syscore TABLE ([NumberOfCores] [varchar](255) NULL)
DECLARE @xp_syspros TABLE ([NumberOfLogicalProcessors] [varchar](255) NULL)

INSERT INTO @xp_syscpu EXEC xp_cmdshell 'WMIC CPU Get DeviceID'
INSERT INTO @xp_syscore EXEC xp_cmdshell 'WMIC CPU Get NumberOfCores'
INSERT INTO @xp_syspros EXEC xp_cmdshell 'WMIC CPU Get NumberOfLogicalProcessors'

SELECT
 (SELECT count(*) FROM @xp_syscpu WHERE [DeviceID] like 'CPU%') AS 'Number of CPU Sockets'
 ,(SELECT TOP 2 * from @xp_syscore WHERE [NumberOfCores] LIKE '[0-9]%%') AS 'Number of Cores per CPU'
 ,(SELECT TOP 2 * from @xp_syspros WHERE [NumberOfLogicalProcessors] LIKE '[0-9]%%') AS 'Number of Logical CPU';

--Reset changes in "XP_cmdshell" to the default system value
DECLARE @configure_value int
SELECT @configure_value = value FROM #TEMP_CONFIG where name = 'xp_cmdshell'
EXEC SP_CONFIGURE N'xp_cmdshell', @configure_value
GO
RECONFIGURE
GO
--Reset changes in "show advanced options" to the default system value
DECLARE @configure_value int
SELECT @configure_value = value FROM #TEMP_CONFIG where name = 'show advanced options'
EXEC SP_CONFIGURE N'show advanced options', @configure_value
GO
RECONFIGURE
GO
IF OBJECT_ID('tempdb..#TEMP_CONFIG') IS NOT NULL DROP TABLE #TEMP_CONFIG

DECLARE @xp_msver TABLE (
 [idx] [int] NULL
 ,[c_name] [varchar](100) NULL
 ,[int_val] [float] NULL
 ,[c_val] [varchar](128) NULL
 )
INSERT INTO @xp_msver EXEC ('[master]..[xp_msver]')

DECLARE @xp_mssql TABLE (
 [attribute_id] [int] NULL
 ,[attribute_name] [varchar](100) NULL
 ,[attribute_value] [varchar](128) NULL
 )
INSERT INTO @xp_mssql EXEC sp_server_info

SELECT
 wi.windows_release AS [Windows Release Number]
 ,CASE wi.windows_release 
 WHEN '5.0' THEN 'Windows 2000'
 WHEN '5.1' THEN 'Windows XP'
 WHEN '5.2' THEN 'Windows 2003 / Windows 2003 R2 / Windows XP Professional x64'
 WHEN '6.0' THEN 'Windows Server 2008 / Windows Vista'
 WHEN '6.1' THEN 'Windows Server 2008 R2 / Windows 7'
 WHEN '6.2' THEN 'Windows Server 2012 / Windows 8'
 WHEN '6.3' THEN 'Windows Server 2012 R2 / Windows 8.1'
 WHEN '10.0' THEN 'Windows Server 2016 / Windows 10'
 ELSE
 wi.windows_release 
 END AS [Windows Release Name]
 ,CASE wi.windows_service_pack_level
 WHEN '' THEN 'NONE'
 ELSE
 wi.windows_service_pack_level
 END AS [Service Pack Level]
 ,(SELECT [c_val] FROM @xp_msver WHERE [c_name] = 'Platform') as [Platform]
 ,(si.cpu_count / si.hyperthread_ratio) AS [Number of physical CPUs]
 ,si.cpu_count AS [Number of logical CPUs]
 ,si.hyperthread_ratio AS [Hyperthread Ration]
 ,CONVERT(VARCHAR,CEILING(si.physical_memory_kb/1000000.00)) +' GB' AS [Physical Memory]
 ,si.virtual_machine_type_desc AS [Virtual Machine Type]
FROM
 sys.dm_os_windows_info wi, sys.dm_os_sys_info si;

select serverproperty('MachineName') as 'Machine Name'
,serverproperty('ServerName') AS 'Instance Name'
,serverproperty('Collation') AS 'Instance Collation'
,(SELECT [attribute_value] FROM @xp_mssql WHERE attribute_name = 'DBMS_VER') AS 'Database Version'
,replace(cast(serverproperty('Edition')as varchar),'Edition','') AS 'Database Edition'
,serverproperty('productVersion') AS 'Database Build Level'
,serverproperty('productLevel') AS 'Database Service Pack Level'
,(SELECT CONVERT(VARCHAR,SUM(size)*8/1024)+' MB' FROM sys.master_files) AS 'Database Total Size'
,serverproperty('IsClustered') AS 'Is Clustered'
,case
when exists (select * from msdb.dbo.backupset where name like 'data protector%') then 'HPDPused'
else 'NotOnDRP' -- where you would replace the
--data protector string with your third party backup solution
end AS 'Third Party Backup Solution'

SELECT
 ds.name
 ,ds.create_date
 ,ds.compatibility_level
 ,ds.collation_name
 ,ds.state_desc
 ,ds.recovery_model_desc
 ,CONVERT(VARCHAR,SUM(mf.size)*8/1024)+' MB' AS [Total disk space]
FROM
 sys.Databases ds
 JOIN sys.master_files mf
 ON ds.database_id=mf.database_id
GROUP BY
 ds.name
 ,ds.create_date
 ,ds.compatibility_level
 ,ds.collation_name
 ,ds.state_desc
 ,ds.recovery_model_desc
GO