Skip to content

Instantly share code, notes, and snippets.

@EitanBlumin
Last active May 15, 2022 14:48
Show Gist options
  • Save EitanBlumin/8a4a99af14ded78a4640a78765750e1e to your computer and use it in GitHub Desktop.
Save EitanBlumin/8a4a99af14ded78a4640a78765750e1e to your computer and use it in GitHub Desktop.
Compare SQL Server Instance Properties
----------------------------------------------------------------------------------
-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 26/06/18
-- Description:
-- Compares server level objects and definitions as outputted by the first script (InstancePropertiesGenerateForCompare.sql).
--
-- Instructions:
-- Run InstancePropertiesGenerateForCompare.sql on each server. Save output to a CSV file.
-- Use this script ( InstancePropertiesComparison.sql ) to load the files into a table, and output any differences
-- Don't forget to change file paths accordingly.
-- Disclaimer:
-- Recommended to run in TEMPDB, unless you want to retain results in the long-term.
-- Note that this script runs TRUNCATE TABLE if InstanceProperties already exists.
-- So if you want long-term retention, you should remove that (lines 32-33).
----------------------------------------------------------------------------------
SET NOCOUNT ON;
GO
-- Create Table for Comparisons
IF OBJECT_ID('InstanceProperties') IS NULL
BEGIN
CREATE TABLE dbo.InstanceProperties
(
ServerName VARCHAR(300) COLLATE database_default NULL,
Category VARCHAR(100) COLLATE database_default NULL,
ItemName VARCHAR(500) COLLATE database_default NULL,
PropertyName VARCHAR(500) COLLATE database_default NULL,
PropertyValue VARCHAR(8000) COLLATE database_default NULL
);
CREATE CLUSTERED INDEX IX ON dbo.InstanceProperties (ServerName, Category, ItemName, PropertyName);
END
ELSE
TRUNCATE TABLE dbo.InstanceProperties;
GO
DECLARE Paths CURSOR
LOCAL FAST_FORWARD
FOR
SELECT CsvPath
FROM (VALUES
('C:\temp\SQLDB1_InstanceProperties.csv')
,('C:\temp\SQLDB2_InstanceProperties.csv')
,('C:\temp\SQLDB3_InstanceProperties.csv')
,('C:\temp\SQLDB4_InstanceProperties.csv')
) AS v(CsvPath)
DECLARE @CurrPath nvarchar(4000), @Cmd nvarchar(MAX);
OPEN Paths;
WHILE 1=1
BEGIN
FETCH NEXT FROM Paths INTO @CurrPath;
IF @@FETCH_STATUS <> 0 BREAK;
SET @Cmd = N'BULK INSERT dbo.InstanceProperties
FROM ''' + @CurrPath + N'''
WITH (CODEPAGE = ''65001'', FIELDTERMINATOR='','')'
PRINT @Cmd;
EXEC (@Cmd);
END
CLOSE Paths;
DEALLOCATE Paths;
GO
-- Cleanup unicode remnants
UPDATE dbo.InstanceProperties SET ServerName = REPLACE(ServerName, N'ן»¿', '')
WHERE ServerName LIKE 'ן»¿%'
GO
--SELECT * FROM dbo.InstanceProperties -- debug
GO
-- Perform comparisons
DECLARE @ServersCount int
SELECT @ServersCount = COUNT(DISTINCT ServerName) FROM dbo.InstanceProperties WHERE ServerName IS NOT NULL;
DECLARE @MatchedItems AS TABLE (Category VARCHAR(100), ItemName VARCHAR(500), PRIMARY KEY (Category, ItemName))
INSERT INTO @MatchedItems
SELECT Category, ItemName
FROM dbo.InstanceProperties
GROUP BY Category, ItemName
HAVING COUNT(DISTINCT ServerName) = @ServersCount
;
WITH Srv AS
(SELECT DISTINCT ServerName FROM dbo.InstanceProperties WHERE ServerName IS NOT NULL)
, ItemNonMatches AS
(
SELECT Category, ItemName
FROM dbo.InstanceProperties
WHERE
-- Ignore 2nd level categories
Category NOT LIKE '%: %'
GROUP BY Category, ItemName
HAVING COUNT(DISTINCT ServerName) < @ServersCount
)
SELECT Issue = 'Missing Item'
, Category, ItemName, PropertyName = CONVERT(varchar(500),NULL)
, Details = (SELECT [@ServerName] = ServerName
FROM Srv
WHERE NOT EXISTS
(SELECT NULL
FROM dbo.InstanceProperties AS i
WHERE i.ServerName = Srv.ServerName
AND i.Category = inm.Category
AND i.ItemName = inm.ItemName
)
FOR XML PATH('srv'), TYPE)
FROM ItemNonMatches AS inm
UNION ALL
SELECT Issue = 'Value is different', Category, ItemName, PropertyName
, Details =
(SELECT [@ServerName] = ServerName, [text()] = PropertyValue
FROM dbo.InstanceProperties AS v
WHERE v.Category = i.Category
AND v.ItemName = i.ItemName
AND v.PropertyName = i.PropertyName
FOR XML PATH('val'), TYPE)
FROM dbo.InstanceProperties AS i
WHERE EXISTS (SELECT NULL FROM @MatchedItems MI WHERE MI.Category = i.Category AND MI.ItemName = i.ItemName)
GROUP BY Category, ItemName, PropertyName
HAVING COUNT(DISTINCT PropertyValue) > 1
GO
----------------------------------------------------------------------------------
-- Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com)
-- Date: 26/06/18
-- Description:
-- Outputs all the server level objects and definitions so that they can be compared against same definitions on another server.
--
-- Instructions:
-- Run on "First" server. Save output to a CSV file.
-- Run on "Second" server. Save output to a CSV file.
-- Use the other script ( CompareInstanceProperties.sql ) to load the files into a table, and output any differences
-- Disclaimer:
-- Does not check replication or log shipping.
-- For Full text only checks existence.
-- Requires sysadmin privliges on both servers.
----------------------------------------------------------------------------------
SET NOCOUNT ON;
USE master
GO
-- Declare global variables
DECLARE @sqlcmd NVARCHAR(max), @params NVARCHAR(500), @sqlmajorver int
DECLARE @UpTime VARCHAR(12),@StartDate DATETIME
DECLARE @agt smallint, @ole smallint, @sao smallint, @xcmd smallint
DECLARE @ErrorSeverity int, @ErrorState int, @ErrorMessage NVARCHAR(4000)
DECLARE @CMD NVARCHAR(4000)
DECLARE @path NVARCHAR(2048)
DECLARE @sqlminorver int, @sqlbuild int, @clustered bit, @osver VARCHAR(5), @ostype VARCHAR(10), @osdistro VARCHAR(20), @server VARCHAR(128), @instancename NVARCHAR(128), @arch smallint, @ossp VARCHAR(25), @SystemManufacturer VARCHAR(128)
DECLARE @existout int, @FSO int, @FS int, @OLEResult int, @FileID int
DECLARE @FileName VARCHAR(200), @Text1 VARCHAR(2000), @CMD2 VARCHAR(100)
DECLARE @src VARCHAR(255), @desc VARCHAR(255), @psavail VARCHAR(20), @psver tinyint
DECLARE @dbid int, @dbname NVARCHAR(1000), @affined_cpus int
DECLARE @maxservermem bigint, @minservermem bigint, @systemmem bigint, @systemfreemem bigint, @numa_nodes_afinned tinyint, @LowMemoryThreshold int
DECLARE @commit_target bigint -- Includes stolen and reserved memory in the memory manager
DECLARE @committed bigint -- Does not include reserved memory in the memory manager
DECLARE @mwthreads_count int, @xtp int
-- Initialize global variables (logic copied from Microsoft's Tiger Toolbox BPCheck: https://github.com/Microsoft/tigertoolbox/tree/master/BPCheck )
SET @sqlmajorver = CONVERT(int, (@@microsoftversion / 0x1000000) & 0xff);
SET @sqlminorver = CONVERT(int, (@@microsoftversion / 0x10000) & 0xff);
SET @sqlbuild = CONVERT(int, @@microsoftversion & 0xffff);
SELECT @systemmem = total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory;
IF (@sqlmajorver >= 11 AND @sqlmajorver < 14) OR (@sqlmajorver = 10 AND @sqlminorver = 50 AND @sqlbuild >= 2500)
BEGIN
SET @sqlcmd = N'SELECT @ostypeOUT = ''Windows'', @osdistroOUT = ''Windows'', @osverOUT = CASE WHEN windows_release IN (''6.3'',''10.0'') AND (@@VERSION LIKE ''%Build 10586%'' OR @@VERSION LIKE ''%Build 14393%'') THEN ''10.0'' ELSE windows_release END, @osspOUT = windows_service_pack_level, @archOUT = CASE WHEN @@VERSION LIKE ''%<X64>%'' THEN 64 WHEN @@VERSION LIKE ''%<IA64>%'' THEN 128 ELSE 32 END FROM sys.dm_os_windows_info (NOLOCK)';
SET @params = N'@osverOUT VARCHAR(5) OUTPUT, @ostypeOUT VARCHAR(10) OUTPUT, @osdistroOUT VARCHAR(20) OUTPUT, @osspOUT VARCHAR(25) OUTPUT, @archOUT smallint OUTPUT';
EXECUTE sp_executesql @sqlcmd, @params, @osverOUT=@osver OUTPUT, @ostypeOUT=@ostype OUTPUT, @osdistroOUT=@osdistro OUTPUT, @osspOUT=@ossp OUTPUT, @archOUT=@arch OUTPUT;
END
ELSE IF @sqlmajorver >= 14
BEGIN
SET @sqlcmd = N'SELECT @ostypeOUT = host_platform, @osdistroOUT = host_distribution, @osverOUT = CASE WHEN host_platform = ''Windows'' AND host_release IN (''6.3'',''10.0'') THEN ''10.0'' ELSE host_release END, @osspOUT = host_service_pack_level, @archOUT = CASE WHEN @@VERSION LIKE ''%<X64>%'' THEN 64 ELSE 32 END FROM sys.dm_os_host_info (NOLOCK)';
SET @params = N'@osverOUT VARCHAR(5) OUTPUT, @ostypeOUT VARCHAR(10) OUTPUT, @osdistroOUT VARCHAR(20) OUTPUT, @osspOUT VARCHAR(25) OUTPUT, @archOUT smallint OUTPUT';
EXECUTE sp_executesql @sqlcmd, @params, @osverOUT=@osver OUTPUT, @ostypeOUT=@ostype OUTPUT, @osdistroOUT=@osdistro OUTPUT, @osspOUT=@ossp OUTPUT, @archOUT=@arch OUTPUT;
END
ELSE
BEGIN
BEGIN TRY
DECLARE @str VARCHAR(500), @str2 VARCHAR(500), @str3 VARCHAR(500)
DECLARE @sysinfo TABLE (id int,
[Name] NVARCHAR(256),
Internal_Value bigint,
Character_Value NVARCHAR(256));
INSERT INTO @sysinfo
EXEC xp_msver;
SELECT @osver = LEFT(Character_Value, CHARINDEX(' ', Character_Value)-1) -- 5.2 is WS2003; 6.0 is WS2008; 6.1 is WS2008R2; 6.2 is WS2012, 6.3 is WS2012R2, 6.3 (14396) is WS2016
FROM @sysinfo
WHERE [Name] LIKE 'WindowsVersion%';
SELECT @arch = CASE WHEN RTRIM(Character_Value) LIKE '%x64%' OR RTRIM(Character_Value) LIKE '%AMD64%' THEN 64
WHEN RTRIM(Character_Value) LIKE '%x86%' OR RTRIM(Character_Value) LIKE '%32%' THEN 32
WHEN RTRIM(Character_Value) LIKE '%IA64%' THEN 128 END
FROM @sysinfo
WHERE [Name] LIKE 'Platform%';
SET @str = (SELECT @@VERSION)
SELECT @str2 = RIGHT(@str, LEN(@str)-CHARINDEX('Windows',@str) + 1)
SELECT @str3 = RIGHT(@str2, LEN(@str2)-CHARINDEX(': ',@str2))
SELECT @ossp = LTRIM(LEFT(@str3, CHARINDEX(')',@str3) -1))
SET @ostype = 'Windows'
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
SELECT @ErrorMessage = 'Windows Version and Architecture subsection - Error raised in TRY block. ' + ERROR_MESSAGE()
RAISERROR (@ErrorMessage, 16, 1);
END CATCH
END;
DECLARE @port VARCHAR(15), @replication int, @RegKey NVARCHAR(255), @cpuaffin VARCHAR(255), @cpucount int, @numa int
DECLARE @i int, @cpuaffin_fixed VARCHAR(300), @affinitymask NVARCHAR(64), @affinity64mask NVARCHAR(64), @cpuover32 int
IF @sqlmajorver < 11 OR (@sqlmajorver = 10 AND @sqlminorver = 50 AND @sqlbuild < 2500)
BEGIN
BEGIN TRY
SELECT @RegKey = CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('InstanceName')) IS NULL THEN N'Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp'
ELSE N'Software\Microsoft\Microsoft SQL Server\' + CAST(SERVERPROPERTY('InstanceName') AS NVARCHAR(128)) + N'\MSSQLServer\SuperSocketNetLib\Tcp' END
EXEC master.sys.xp_regread N'HKEY_LOCAL_MACHINE', @RegKey, N'TcpPort', @port OUTPUT, NO_OUTPUT
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
SELECT @ErrorMessage = 'Instance info subsection - Error raised in TRY block 1. ' + ERROR_MESSAGE()
RAISERROR (@ErrorMessage, 16, 1);
END CATCH
END
ELSE
BEGIN
BEGIN TRY
SET @sqlcmd = N'SELECT @portOUT = MAX(CONVERT(VARCHAR(15),port)) FROM sys.dm_tcp_listener_states WHERE is_ipv4 = 1 AND [type] = 0 AND ip_address <> ''127.0.0.1'';';
SET @params = N'@portOUT VARCHAR(15) OUTPUT';
EXECUTE sp_executesql @sqlcmd, @params, @portOUT = @port OUTPUT;
IF @port IS NULL
BEGIN
SET @sqlcmd = N'SELECT @portOUT = MAX(CONVERT(VARCHAR(15),port)) FROM sys.dm_tcp_listener_states WHERE is_ipv4 = 0 AND [type] = 0 AND ip_address <> ''127.0.0.1'';';
SET @params = N'@portOUT VARCHAR(15) OUTPUT';
EXECUTE sp_executesql @sqlcmd, @params, @portOUT = @port OUTPUT;
END
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
SELECT @ErrorMessage = 'Instance info subsection - Error raised in TRY block 2. ' + ERROR_MESSAGE()
RAISERROR (@ErrorMessage, 16, 1);
END CATCH
END
BEGIN TRY
EXEC master..xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Replication', N'IsInstalled', @replication OUTPUT, NO_OUTPUT
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
SELECT @ErrorMessage = 'Instance info subsection - Error raised in TRY block 3. ' + ERROR_MESSAGE()
RAISERROR (@ErrorMessage, 16, 1);
END CATCH
SELECT @cpucount = COUNT(cpu_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64
SELECT @affined_cpus = COUNT(cpu_id) FROM sys.dm_os_schedulers WHERE is_online = 1 AND scheduler_id < 255 AND parent_node_id < 64;
SELECT @numa = COUNT(DISTINCT parent_node_id) FROM sys.dm_os_schedulers WHERE scheduler_id < 255 AND parent_node_id < 64;
;WITH bits AS
(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL
SELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL
SELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1),
bytes AS
(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
-- CPU Affinity is shown highest to lowest CPU ID
SELECT @affinitymask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)
ELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()]
FROM bits CROSS JOIN bytes
ORDER BY M, N DESC
FOR XML PATH('')), @cpucount) END
FROM sys.configurations (NOLOCK)
WHERE name = 'affinity mask';
IF @cpucount > 32
BEGIN
;WITH bits AS
(SELECT 7 AS N, 128 AS E UNION ALL SELECT 6, 64 UNION ALL
SELECT 5, 32 UNION ALL SELECT 4, 16 UNION ALL SELECT 3, 8 UNION ALL
SELECT 2, 4 UNION ALL SELECT 1, 2 UNION ALL SELECT 0, 1),
bytes AS
(SELECT 1 M UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
-- CPU Affinity is shown highest to lowest CPU ID
SELECT @affinity64mask = CASE WHEN [value] = 0 THEN REPLICATE('1', @cpucount)
ELSE RIGHT((SELECT ((CONVERT(tinyint, SUBSTRING(CONVERT(binary(9), [value]), M, 1)) & E) / E) AS [text()]
FROM bits CROSS JOIN bytes
ORDER BY M, N DESC
FOR XML PATH('')), @cpucount) END
FROM sys.configurations (NOLOCK)
WHERE name = 'affinity64 mask';
END;
IF @cpucount > 32
SELECT @cpuover32 = ABS(LEN(@affinity64mask) - (@cpucount-32))
SELECT @cpuaffin = CASE WHEN @cpucount > 32 THEN REVERSE(LEFT(REVERSE(@affinity64mask),@cpuover32)) + RIGHT(@affinitymask,32) ELSE RIGHT(@affinitymask,@cpucount) END
SET @cpuaffin_fixed = @cpuaffin
IF @numa > 1
BEGIN
-- format binary mask by node for better reading
SET @i = @cpucount/@numa + 1
WHILE @i < @cpucount + @numa
BEGIN
SELECT @cpuaffin_fixed = STUFF(@cpuaffin_fixed, @i, 1, '_' + SUBSTRING(@cpuaffin_fixed, @i, 1))
SET @i = @i + @cpucount/@numa + 1
END
END
-- Prepare temporary table to hold results
IF OBJECT_ID('tempdb..#InstanceProperties') IS NOT NULL DROP TABLE #InstanceProperties;
CREATE TABLE #InstanceProperties
(
Category NVARCHAR(100) COLLATE database_default,
ItemName NVARCHAR(500) COLLATE database_default,
PropertyName NVARCHAR(500) COLLATE database_default,
PropertyValue NVARCHAR(MAX) COLLATE database_default
);
-- 0. Operating System and Server properties
RAISERROR ('Check 0',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
VALUES
('Server Properties', 'Instance', 'Domain', DEFAULT_DOMAIN())
,('Server Properties', 'Instance', 'Default Collation', CONVERT(nvarchar(max), SERVERPROPERTY('Collation')))
,('Server Properties', 'Instance', 'SQL Edition', CONVERT(nvarchar(max), SERVERPROPERTY('Edition')))
,('Server Properties', 'Instance', 'SQL Version', CONVERT(nvarchar(max), SERVERPROPERTY('ProductVersion')))
,('Server Properties', 'Instance', 'Service Pack Level', CONVERT(nvarchar(max), SERVERPROPERTY('ProductLevel')))
,('Server Properties', 'Instance', 'Product Update Reference', CONVERT(nvarchar(max), SERVERPROPERTY('ProductUpdateReference')))
,('Server Properties', 'Instance', 'Is Always On Enabled', CONVERT(nvarchar(max), SERVERPROPERTY('IsHadrEnabled')))
,('Server Properties', 'Instance', 'Is Clustered', CONVERT(nvarchar(max), SERVERPROPERTY('IsClustered')))
,('Server Properties', 'Instance', 'Is Full Text Installed', CONVERT(nvarchar(max), SERVERPROPERTY('IsFullTextInstalled')))
,('Server Properties', 'Instance', 'Is Integrated Security Only', CONVERT(nvarchar(max), SERVERPROPERTY('IsIntegratedSecurityOnly')))
,('Server Properties', 'Instance', 'Is LocalDB', CONVERT(nvarchar(max), SERVERPROPERTY('IsLocalDB')))
,('Server Properties', 'Instance', 'Is Polybase Installed', CONVERT(nvarchar(max), SERVERPROPERTY('IsPolybaseInstalled')))
,('Server Properties', 'Instance', 'Is XTP Supported', CONVERT(nvarchar(max), SERVERPROPERTY('IsXTPSupported')))
,('Server Properties', 'Instance', 'Is Replication Installed', CASE WHEN @replication = 1 THEN '1' WHEN @replication = 0 THEN '0' ELSE 'INVALID INPUT/ERROR' END)
,('Server Properties', 'Instance', 'Is Advanced Analytics Installed', CONVERT(nvarchar(max), SERVERPROPERTY('IsAdvancedAnalyticsInstalled')))
,('Server Properties', 'Instance', 'Filestream Configured Level', CONVERT(nvarchar(max), SERVERPROPERTY('FilestreamConfiguredLevel')))
,('Server Properties', 'Instance', 'Filestream Effective Level', CONVERT(nvarchar(max), SERVERPROPERTY('FilestreamEffectiveLevel')))
,('Server Properties', 'Instance', 'Filestream Share Name', CONVERT(nvarchar(max), SERVERPROPERTY('FilestreamShareName')))
,('Server Properties', 'Instance', 'Port', RTRIM(@port))
,('Server Properties', 'Instance', N'CPU Affinity Mask', @cpuaffin_fixed)
,('Server Properties', 'Instance', N'Affined CPUs', LTRIM(STR(@affined_cpus)))
,('Server Properties', 'OS', N'Windows Version',
CASE @osver WHEN '5.2' THEN 'XP/WS2003'
WHEN '6.0' THEN 'Vista/WS2008'
WHEN '6.1' THEN 'W7/WS2008R2'
WHEN '6.2' THEN 'W8/WS2012'
WHEN '6.3' THEN 'W8.1/WS2012R2'
WHEN '10.0' THEN 'W10/WS2016'
ELSE @ostype + ' ' + @osdistro
END)
,('Server Properties', 'OS', N'Windows Service Pack Level', CASE WHEN @ostype = 'Windows' THEN @ossp ELSE @osver END)
,('Server Properties', 'OS', N'Architecture', LTRIM(STR(@arch)))
,('Server Properties', 'OS', N'CPU Count', LTRIM(STR(@cpucount)))
,('Server Properties', 'OS', N'Total Memory (MB)', LTRIM(STR(@systemmem)))
INSERT INTO #InstanceProperties
SELECT 'Server Properties', 'OS', v.*
FROM sys.dm_os_sys_info
CROSS APPLY
(VALUES
(N'CPU Sockets', LTRIM(STR(cpu_count/hyperthread_ratio)))
,(N'Max Workers Count', LTRIM(STR(max_workers_count)))
) AS v(PropertyName, PropertyValue)
-- 1. Server Configurations
RAISERROR ('Check 1',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Server Configurations', 'Instance', name, CONVERT(nvarchar(max), value_in_use)
FROM master.sys.configurations
-- 2. Credentials
RAISERROR ('Check 2',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Server Credentials', 'Instance', F.name, F.credential_identity
FROM master.sys.credentials F
-- 3. Linked Servers
RAISERROR ('Check 3',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Linked Servers', F.name, v.*
FROM master.sys.servers F
CROSS APPLY
(VALUES
(N'data source', ISNULL(F.data_source, F.provider_string))
,(N'product', F.product)
,(N'provider', F.provider)
,(N'connect_timeout', LTRIM(STR(F.connect_timeout)))
,(N'query_timeout', LTRIM(STR(F.query_timeout)))
,(N'is_remote_login_enabled', LTRIM(STR(F.is_remote_login_enabled)))
,(N'is_rpc_out_enabled', LTRIM(STR(F.is_rpc_out_enabled)))
,(N'is_data_access_enabled', LTRIM(STR(F.is_data_access_enabled)))
,(N'is_collation_compatible',LTRIM(STR( F.is_collation_compatible)))
,(N'uses_remote_collation', LTRIM(STR(F.uses_remote_collation)))
,(N'collation_name', F.collation_name COLLATE database_default)
,(N'lazy_schema_validation', LTRIM(STR(F.lazy_schema_validation)))
,(N'is_system', LTRIM(STR(F.is_system)))
,(N'is_publisher', LTRIM(STR(F.is_publisher)))
,(N'is_subscriber', LTRIM(STR(F.is_subscriber)))
,(N'is_distributor', LTRIM(STR(F.is_distributor)))
,(N'is_nonsql_subscriber', LTRIM(STR(F.is_nonsql_subscriber)))
,(N'is_remote_proc_transaction_promotion_enabled', LTRIM(STR(F.is_remote_proc_transaction_promotion_enabled)))
) AS v(PropertyName, PropertyValue)
WHERE F.is_linked = 1
AND PropertyValue IS NOT NULL
-- 4. Database settings that might not transfer
RAISERROR ('Check 4',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Databases', F.name, v.*
FROM sys.databases F
CROSS APPLY
(VALUES
(N'state', F.state_desc)
,(N'collation_name', F.collation_name COLLATE database_default)
,(N'compatibility_level', LTRIM(STR(F.compatibility_level)))
,(N'user_access', F.user_access_desc COLLATE database_default)
,(N'is_read_only', LTRIM(STR(F.is_read_only)))
,(N'is_auto_close_on', LTRIM(STR(F.is_auto_close_on)))
,(N'is_auto_shrink_on', LTRIM(STR(F.is_auto_shrink_on)))
,(N'is_in_standby', LTRIM(STR(F.is_in_standby)))
,(N'is_cleanly_shutdown', LTRIM(STR(F.is_cleanly_shutdown)))
,(N'is_supplemental_logging_enabled', LTRIM(STR(F.is_supplemental_logging_enabled)))
,(N'snapshot_isolation_state', F.snapshot_isolation_state_desc)
,(N'is_read_committed_snapshot_on', LTRIM(STR(F.is_read_committed_snapshot_on)))
,(N'recovery_model', F.recovery_model_desc)
,(N'page_verify_option', F.page_verify_option_desc)
,(N'is_auto_create_stats_on', LTRIM(STR(F.is_auto_create_stats_on)))
,(N'is_auto_create_stats_incremental_on', LTRIM(STR(F.is_auto_create_stats_incremental_on)))
,(N'is_auto_update_stats_on', LTRIM(STR(F.is_auto_update_stats_on)))
,(N'is_auto_update_stats_async_on', LTRIM(STR(F.is_auto_update_stats_async_on)))
,(N'is_ansi_null_default_on', LTRIM(STR(F.is_ansi_null_default_on)))
,(N'is_ansi_nulls_on', LTRIM(STR(F.is_ansi_nulls_on)))
,(N'is_ansi_padding_on', LTRIM(STR(F.is_ansi_padding_on)))
,(N'is_ansi_warnings_on', LTRIM(STR(F.is_ansi_warnings_on)))
,(N'is_arithabort_on', LTRIM(STR(F.is_arithabort_on)))
,(N'is_concat_null_yields_null_on', LTRIM(STR(F.is_concat_null_yields_null_on)))
,(N'is_numeric_roundabort_on', LTRIM(STR(F.is_numeric_roundabort_on)))
,(N'is_quoted_identifier_on', LTRIM(STR(F.is_quoted_identifier_on)))
,(N'is_recursive_triggers_on', LTRIM(STR(F.is_recursive_triggers_on)))
,(N'is_cursor_close_on_commit_on', LTRIM(STR(F.is_cursor_close_on_commit_on)))
,(N'is_local_cursor_default', LTRIM(STR(F.is_local_cursor_default)))
,(N'is_fulltext_enabled', LTRIM(STR(F.is_fulltext_enabled)))
,(N'is_trustworthy_on', LTRIM(STR(F.is_trustworthy_on)))
,(N'is_db_chaining_on', LTRIM(STR(F.is_db_chaining_on)))
,(N'is_parameterization_forced', LTRIM(STR(F.is_parameterization_forced)))
,(N'is_master_key_encrypted_by_server', LTRIM(STR(F.is_master_key_encrypted_by_server)))
,(N'is_query_store_on', LTRIM(STR(F.is_query_store_on)))
,(N'is_published', LTRIM(STR(F.is_published)))
,(N'is_subscribed', LTRIM(STR(F.is_subscribed)))
,(N'is_merge_published', LTRIM(STR(F.is_merge_published)))
,(N'is_distributor', LTRIM(STR(F.is_distributor)))
,(N'is_sync_with_backup', LTRIM(STR(F.is_sync_with_backup)))
,(N'is_broker_enabled', LTRIM(STR(F.is_broker_enabled)))
,(N'is_date_correlation_on', LTRIM(STR(F.is_date_correlation_on)))
,(N'is_cdc_enabled', LTRIM(STR(F.is_cdc_enabled)))
,(N'is_encrypted', LTRIM(STR(F.is_encrypted)))
,(N'is_honor_broker_priority_on', LTRIM(STR(F.is_honor_broker_priority_on)))
,(N'replica_id', CONVERT(nvarchar(max),F.replica_id))
,(N'group_database_id', CONVERT(nvarchar(max),F.group_database_id))
,(N'containment', F.containment_desc)
,(N'target_recovery_time_in_seconds', LTRIM(STR(F.target_recovery_time_in_seconds)))
,(N'delayed_durability', F.delayed_durability_desc)
,(N'is_memory_optimized_elevate_to_snapshot_on', LTRIM(STR(F.is_memory_optimized_elevate_to_snapshot_on)))
,(N'is_federation_member', LTRIM(STR(F.is_federation_member)))
,(N'is_mixed_page_allocation_on', LTRIM(STR(F.is_mixed_page_allocation_on)))
) AS v(PropertyName, PropertyValue)
WHERE database_id > 4
-- 5. Messages
RAISERROR ('Check 5',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'User Defined Messages', N'msgid ' + CONVERT(nvarchar(max), F.message_id) + N' (lang ' + CONVERT(nvarchar(max),F.language_id) + N')', V.*
FROM sys.messages F
CROSS APPLY
(VALUES
(N'text', F.text COLLATE database_default)
,(N'severity', LTRIM(STR(F.severity)))
,(N'is_event_logged', LTRIM(STR(F.is_event_logged)))
) AS v(PropertyName, PropertyValue)
WHERE F.message_id > 50000 -- user defined messages only
-- 6. Events
RAISERROR ('Check 6',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Server Event Notifications', E.type_desc, N'service_name', F.service_name
FROM msdb.sys.server_event_notifications F
JOIN msdb.sys.server_events E
ON F.object_id = E.object_id
-- 7. Alerts
RAISERROR ('Check 7',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'SQL Agent Alerts', F.name, V.*
FROM msdb.dbo.sysalerts F
CROSS APPLY
(VALUES
(N'message_id', LTRIM(STR(F.message_id)))
,(N'severity', LTRIM(STR(F.severity)))
,(N'include_event_description', LTRIM(STR(F.include_event_description)))
,(N'notification_message', F.notification_message COLLATE database_default)
,(N'database_name', F.database_name COLLATE database_default)
,(N'event_description_keyword', F.event_description_keyword COLLATE database_default)
,(N'has_notification', LTRIM(STR(F.has_notification)))
,(N'flags', LTRIM(STR(F.flags)))
,(N'performance_condition', F.performance_condition COLLATE database_default)
) AS v(PropertyName, PropertyValue)
-- 8. Extended Procedures
RAISERROR ('Check 8',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
EXEC sp_MSforeachdb 'SELECT ''Extended Procedures: ?'', F.name, N''DLL'', F.dll_name
FROM [?].sys.extended_procedures F'
-- 9. Startup Procedures
RAISERROR ('Check 9',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
EXEC sp_MSforeachdb 'SELECT ''Startup Procedures: ?'', F.name, N''Auto Executed'', ''auto executed''
FROM [?].sys.procedures F
WHERE is_auto_executed = 1'
-- 10. FullText
RAISERROR ('Check 10',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
EXEC sp_MSforeachdb 'SELECT ''Full Text Catalogs: ?'', name, N''Status'', status
FROM [?].sys.sysfulltextcatalogs'
-- 11. Check for missing roles on Second server
RAISERROR ('Check 11',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Server Roles', F.name, N'sid', CONVERT(nvarchar(max), F.sid, 1)
FROM master.sys.server_principals F
WHERE F.[type] = 'R'
-- 12. Check for missing logins on Second server
RAISERROR ('Check 12',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Server Logins', F.name, V.*
FROM master.sys.server_principals F
CROSS APPLY
(VALUES
(N'sid', CONVERT(nvarchar(max), F.sid, 1))
,(N'type', F.type_desc COLLATE database_default)
,(N'is_disabled', LTRIM(STR(F.is_disabled)))
,(N'default_database', F.default_database_name COLLATE database_default)
) AS v(PropertyName, PropertyValue)
WHERE F.[type] <> 'R'
AND F.name NOT LIKE 'NT SERVICE%'
AND F.name NOT LIKE 'NT AUTHORITY%'
AND F.name NOT LIKE '##%##'
-- 13. Database Scoped Configurations
IF OBJECT_ID('sys.database_scoped_configurations') IS NOT NULL
BEGIN
RAISERROR ('Check 13',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
EXEC sp_MSforeachdb N'select N''Databases'', ''?'', name, CONVERT(nvarchar(max), value) from [?].sys.database_scoped_configurations'
END
-- 14. Check logins have the same server roles.
RAISERROR ('Check 14',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Server Logins: Server Role Memberships', PRN.name, srvrole.name, N'Member'
FROM sys.server_role_members membership
INNER JOIN (SELECT * FROM sys.server_principals WHERE type_desc='SERVER_ROLE') srvrole
ON srvrole.Principal_id= membership.Role_principal_id
INNER JOIN sys.server_principals PRN
ON PRN.Principal_id= membership.member_principal_id
WHERE PRN.Type_Desc NOT IN ('SERVER_ROLE')
AND PRN.name NOT LIKE 'NT SERVICE%'
AND PRN.name NOT LIKE 'NT AUTHORITY%'
AND PRN.name NOT LIKE '##%##'
-- 15. Server Level Permissions
RAISERROR ('Check 15',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Server Logins: Server Level Permissions'
, pr.name COLLATE database_default
, pe.permission_name COLLATE database_default + N' ' + pe.class_desc COLLATE database_default + N' ' + CONVERT(nvarchar(max), pe.major_id) + N'.' + CONVERT(nvarchar(max), pe.minor_id)
, pe.state_desc COLLATE database_default
FROM master.sys.server_principals AS pr
JOIN master.sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
WHERE pr.name NOT LIKE 'NT SERVICE%'
AND pr.name NOT LIKE 'NT AUTHORITY%'
AND pr.name NOT LIKE '##%##'
-- 16. Database Permissions
RAISERROR ('Check 16',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
EXEC sp_MSforeachdb 'SELECT ''Database Roles: ?'', dpm.name AS MemberName, dpr.name ASRoleName, N''Member''
FROM [?].sys.database_role_members dr
JOIN [?].sys.database_principals dpm
ON dpm.principal_id = dr.member_principal_id
JOIN [?].sys.database_principals dpr
on dpr.principal_id = dr.role_principal_id
WHERE dpm.type_desc LIKE ''%USER%'''
-- 17. System Objects Permissions
RAISERROR ('Check 17',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'System Objects Permissions'
, p.name COLLATE database_default
, dp.[permission_name] COLLATE database_default + ' ON ' + so.name COLLATE database_default + ' TO ' + p.type_desc COLLATE database_default+ ': ' + p.name COLLATE database_default
, dp.state_desc COLLATE database_default
FROM master.sys.database_permissions AS dp
JOIN master.sys.system_objects AS so
ON dp.major_id = so.object_id
JOIN master.sys.database_principals p
ON dp.grantee_principal_id = p.principal_id
WHERE dp.class = 1 AND so.parent_object_id = 0
-- 18. SQL Agent Operators
RAISERROR ('Check 18',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Operators', F.name, V.*
FROM msdb.dbo.sysoperators F
CROSS APPLY
(VALUES
(N'enabled', LTRIM(STR(F.enabled)))
,(N'weekday_pager_start_time', LTRIM(STR(F.weekday_pager_start_time)))
,(N'weekday_pager_end_time', LTRIM(STR(F.weekday_pager_end_time)))
,(N'saturday_pager_start_time', LTRIM(STR(F.saturday_pager_start_time)))
,(N'saturday_pager_end_time', LTRIM(STR(F.saturday_pager_end_time)))
,(N'sunday_pager_start_time', LTRIM(STR(F.sunday_pager_start_time)))
,(N'sunday_pager_end_time', LTRIM(STR(F.sunday_pager_end_time)))
,(N'pager_days', LTRIM(STR(F.pager_days)))
,(N'email_address', F.email_address COLLATE database_default)
) AS v(PropertyName, PropertyValue)
-- 19. Server Triggers
RAISERROR ('Check 19',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Server Triggers', F.name, V.*
FROM master.sys.server_triggers F
CROSS APPLY
(VALUES
(N'is_disabled', LTRIM(STR(F.is_disabled)))
,(N'type', F.type_desc COLLATE database_default)
) AS v(PropertyName, PropertyValue)
-- 20. DB Owner
RAISERROR ('Check 20',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Databases', db.name, 'owner', sp.name
FROM sys.databases AS db
INNER JOIN sys.server_principals AS sp
ON db.owner_sid = sp.sid
-- 21. SQL Agent Jobs
RAISERROR ('Check 21',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Jobs', F.name, V.*
FROM msdb.dbo.sysjobs F
INNER JOIN sys.server_principals AS sp
ON F.owner_sid = sp.sid
LEFT JOIN msdb..sysoperators AS op1
ON F.notify_email_operator_id = op1.id
LEFT JOIN msdb..sysoperators AS op2
ON F.notify_netsend_operator_id = op2.id
LEFT JOIN msdb..sysoperators AS op3
ON F.notify_page_operator_id = op3.id
CROSS APPLY
(VALUES
(N'enabled', LTRIM(STR(F.enabled)))
,(N'start_step_id', LTRIM(STR(F.start_step_id)))
,(N'notify_level_eventlog', LTRIM(STR(F.notify_level_eventlog)))
,(N'notify_level_email', LTRIM(STR(F.notify_level_email)))
,(N'notify_level_netsend', LTRIM(STR(F.notify_level_netsend)))
,(N'notify_level_page', LTRIM(STR(F.notify_level_page)))
,(N'delete_level', LTRIM(STR(F.delete_level)))
,(N'notify_email_operator', op1.name COLLATE database_default)
,(N'notify_netsend_operator', op2.name COLLATE database_default)
,(N'notify_page_operator', op3.name COLLATE database_default)
,(N'owner', sp.name COLLATE database_default)
) AS v(PropertyName, PropertyValue)
-- 22. SQL Agent Job Steps
RAISERROR ('Check 22',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Job Steps: ' + FJ.name, F.step_name, V.*
FROM msdb.dbo.sysjobs FJ
JOIN msdb.dbo.sysjobsteps F
ON FJ.job_id = F.job_id
CROSS APPLY
(VALUES
(N'subsystem', F.subsystem COLLATE database_default)
,(N'flags', LTRIM(STR(F.flags)))
,(N'cmdexec_success_code', LTRIM(STR(F.cmdexec_success_code)))
,(N'on_success_action', LTRIM(STR(F.on_success_action)))
,(N'on_success_step_id', LTRIM(STR(F.on_success_step_id)))
,(N'on_fail_action', LTRIM(STR(F.on_fail_action)))
,(N'on_fail_step_id', LTRIM(STR(F.on_fail_step_id)))
,(N'server', [server] COLLATE database_default)
,(N'database_name', F.database_name COLLATE database_default)
,(N'database_user_name', F.database_user_name COLLATE database_default)
,(N'retry_attempts', LTRIM(STR(F.retry_attempts)))
,(N'retry_interval', LTRIM(STR(F.retry_interval)))
,(N'os_run_priority', LTRIM(STR(F.os_run_priority)))
,(N'output_file_name', F.output_file_name COLLATE database_default)
,(N'command (checksum)', CONVERT(nvarchar(max), CHECKSUM(F.command)))
) AS v(PropertyName, PropertyValue)
-- 23. Server Endpoints
RAISERROR ('Check 23',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT N'Endpoints', name, V.*
FROM sys.endpoints F
CROSS APPLY (
SELECT PropertyName, PropertyValue
FROM
(VALUES
('state',state_desc COLLATE database_default)
,('type',type_desc COLLATE database_default)
,('protocol',protocol_desc COLLATE database_default)
) as v(PropertyName, PropertyValue)
WHERE v.PropertyValue IS NOT NULL
UNION ALL
SELECT PropertyName, PropertyValue
FROM sys.http_endpoints AS HTTPE
CROSS APPLY
(VALUES
('url_path',url_path COLLATE database_default)
,('is_clear_port_enabled',LTRIM(STR(is_clear_port_enabled)))
,('clear_port',LTRIM(STR(clear_port)))
,('is_ssl_port_enabled',LTRIM(STR(is_ssl_port_enabled)))
,('ssl_port',LTRIM(STR(ssl_port)))
,('is_anonymous_enabled',LTRIM(STR(is_anonymous_enabled)))
,('is_basic_auth_enabled',LTRIM(STR(is_basic_auth_enabled)))
,('is_digest_auth_enabled',LTRIM(STR(is_digest_auth_enabled)))
,('is_kerberos_auth_enabled',LTRIM(STR(is_kerberos_auth_enabled)))
,('is_ntlm_auth_enabled',LTRIM(STR(is_ntlm_auth_enabled)))
,('is_integrated_auth_enabled',LTRIM(STR(is_integrated_auth_enabled)))
,('authorization_realm',authorization_realm COLLATE database_default)
,('default_logon_domain',default_logon_domain COLLATE database_default)
,('is_compression_enabled',LTRIM(STR(is_compression_enabled)))
) as v(PropertyName, PropertyValue)
WHERE HTTPE.endpoint_id = F.endpoint_id
AND (F.protocol_desc = 'HTTP' OR F.type_desc = 'SOAP')
AND v.PropertyValue IS NOT NULL
UNION ALL
SELECT PropertyName, PropertyValue
FROM sys.tcp_endpoints TCPE
CROSS APPLY
(VALUES
('port',LTRIM(STR(port)))
,('is_dynamic_port',LTRIM(STR(is_dynamic_port)))
,('ip_address',ip_address)
,('is_admin_endpoint',LTRIM(STR(is_admin_endpoint)))
) as v(PropertyName, PropertyValue)
WHERE TCPE.endpoint_id = F.endpoint_id
AND F.protocol_desc = 'TCP'
AND v.PropertyValue IS NOT NULL
UNION ALL
SELECT PropertyName, PropertyValue
FROM sys.database_mirroring_endpoints DBME
CROSS APPLY
(VALUES
('role',role_desc)
,('is_encryption_enabled',LTRIM(STR(is_encryption_enabled)))
,('connection_auth',connection_auth_desc)
,('encryption_algorithm',encryption_algorithm_desc)
) as v(PropertyName, PropertyValue)
WHERE DBME.endpoint_id = F.endpoint_id
AND F.type_desc = 'DATABASE_MIRRORING'
AND v.PropertyValue IS NOT NULL
UNION ALL
SELECT PropertyName, PropertyValue
FROM sys.service_broker_endpoints AS SBE
CROSS APPLY
(VALUES
('is_message_forwarding_enabled',LTRIM(STR(is_message_forwarding_enabled)))
,('message_forwarding_size',LTRIM(STR(message_forwarding_size)))
,('connection_auth',connection_auth_desc COLLATE database_default)
,('encryption_algorithm',encryption_algorithm_desc COLLATE database_default)
) as v(PropertyName, PropertyValue)
WHERE SBE.endpoint_id = F.endpoint_id
AND F.type_desc = 'SERVICE_BROKER'
AND v.PropertyValue IS NOT NULL
) AS V(PropertyName, PropertyValue)
-- 24. Resource Governor
RAISERROR ('Check 24',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT 'Resource Governor' AS [Category], N'Instance', 'Classifier Function', CASE WHEN classifier_function_id = 0 THEN 'Default_Configuration' ELSE OBJECT_SCHEMA_NAME(classifier_function_id) + '.' + OBJECT_NAME(classifier_function_id) END
FROM sys.dm_resource_governor_configuration AS rgc
UNION ALL
SELECT 'Resource Governor Pools' AS [Category], name, v.*
FROM sys.dm_resource_governor_resource_pools rp
CROSS APPLY
(VALUES
(N'max_memory_kb', LTRIM(STR(max_memory_kb)))
,(N'min_cpu_percent', LTRIM(STR(min_cpu_percent)))
,(N'max_cpu_percent', LTRIM(STR(max_cpu_percent)))
,(N'min_memory_percent', LTRIM(STR(min_memory_percent)))
,(N'max_memory_percent', LTRIM(STR(max_memory_percent)))
) AS v(PropertyName, PropertyValue)
-- 25. Database Files
RAISERROR ('Check 25',0,1) WITH NOWAIT;
INSERT INTO #InstanceProperties
SELECT N'Database Files: ' + DB_NAME([database_id]), [file_id], V.*
FROM sys.master_files (NOLOCK)
CROSS APPLY
(VALUES
(N'name', name COLLATE database_default)
,(N'type_desc', type_desc COLLATE database_default)
,(N'state_desc', state_desc COLLATE database_default)
,(N'is_percent_growth', LTRIM(STR(is_percent_growth)))
,(N'growth', LTRIM(STR(growth)))
,(N'is_media_read_only', LTRIM(STR(is_media_read_only)))
,(N'is_read_only', LTRIM(STR(is_read_only)))
,(N'is_sparse', LTRIM(STR(is_sparse)))
,(N'is_name_reserved', LTRIM(STR(is_name_reserved)))
) AS v(PropertyName, PropertyValue)
-- 26. Database Features Usage
RAISERROR ('Check 26',0,1) WITH NOWAIT;
SET @CMD = N'
SELECT N''Feature Usage'', ''?'' AS [dbname], feature_name, N''In use'' FROM [?].sys.dm_db_persisted_sku_features (NOLOCK)
UNION ALL
SELECT N''Feature Usage'', ''?'' AS [dbname], ''Change_Tracking'', N''In use'' FROM sys.change_tracking_databases (NOLOCK) WHERE database_id = DB_ID(''?'')
UNION ALL
SELECT TOP 1 N''Feature Usage'', ''?'' AS [dbname], ''Fine_grained_auditing'', N''In use'' FROM [?].sys.database_audit_specifications (NOLOCK)'
+ CASE WHEN OBJECT_ID('sys.external_data_sources') IS NOT NULL THEN N'
UNION ALL
SELECT TOP 1 N''Feature Usage'', ''?'' AS [dbname], ''Polybase'', N''In use'' FROM [?].sys.external_data_sources (NOLOCK)'
ELSE N'' END
+ CASE WHEN OBJECT_ID('sys.security_policies') IS NOT NULL THEN N'
UNION ALL
SELECT TOP 1 N''Feature Usage'', ''?'' AS [dbname], ''Row_Level_Security'', N''In use'' FROM [?].sys.security_policies (NOLOCK)'
ELSE N'' END
+ CASE WHEN OBJECT_ID('sys.column_master_keys') IS NOT NULL THEN N'
UNION ALL
SELECT TOP 1 N''Feature Usage'', ''?'' AS [dbname], ''Always_Encrypted'', N''In use'' FROM [?].sys.column_master_keys (NOLOCK)'
ELSE N'' END
+ CASE WHEN OBJECT_ID('sys.masked_columns') IS NOT NULL THEN N'
UNION ALL
SELECT TOP 1 N''Feature Usage'', ''?'' AS [dbname], ''Dynamic_Data_Masking'', N''In use'' FROM [?].sys.masked_columns (NOLOCK) WHERE is_masked = 1'
ELSE N'' END
INSERT INTO #InstanceProperties
EXEC sp_MSforeachdb @CMD
INSERT INTO #InstanceProperties
SELECT DISTINCT N'Feature Usage', [name], 'DB Snapshot' AS feature_name, N'In Use' FROM master.sys.databases (NOLOCK) WHERE database_id NOT IN (2,3) AND source_database_id IS NOT NULL
UNION ALL
SELECT DISTINCT N'Feature Usage', DB_NAME(database_id), 'Filestream' AS feature_name, N'In Use' FROM sys.master_files (NOLOCK) WHERE database_id NOT IN (2,3) AND [type] = 2 and file_guid IS NOT NULL
-- Return all
SELECT ServerName = CONVERT(varchar(300), SERVERPROPERTY('ServerName')), Category = CONVERT(varchar(100), Category), ItemName = CONVERT(varchar(500), ItemName), PropertyName = CONVERT(varchar(500), PropertyName), PropertyValue = CONVERT(varchar(8000), PropertyValue)
FROM #InstanceProperties
ORDER BY 2, 3, 4
IF OBJECT_ID('tempdb..#InstanceProperties') IS NOT NULL DROP TABLE #InstanceProperties;
@EitanBlumin
Copy link
Author

EitanBlumin commented Jul 1, 2018

Instructions:

  1. You run the first script (InstancePropertiesGenerateForCompare.sql) on each SQL Server instance that you need to compare.
  2. Right-click on the results and choose "Save Results As..." to save it as a CSV.
  3. Copy the output files to some central location (also with a SQL Server) where you'd want to run the comparison.
  4. Open the second script (InstancePropertiesComparison.sql) on the central location, change all the file paths and server names accordingly.
  5. Run the script (recommended in TEMPDB) which would use BULK INSERT into a table and then run the comparisons.
  6. Profit!

@EitanBlumin
Copy link
Author

Usages for this gist:

  1. Comparing between two instances that are about to have DB Mirroring or AlwaysOn established between them.
  2. Comparing between two instances that should be identical due to a business requirement (for example: Making sure sharding instances are identical, or that different on-premise instances are identical, or that a QA instance is identical to a Production instance).
  3. Comparing between two instances in order to try and find out why one instance performs differently from another (i.e. maybe it's an instance configuration? Or a database setting? Or a hardware difference? etc.).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment