hAYknnRZ

1----------------------------------------------------------------------------------
2 -- Created: by Eitan Blumin 26/06/18
3 --	Description:
4 --				Compares server level objects and definitions as outputted by the first script (GenerateInstancePropertiesForCompare.sql).
5 --
6 --	Instructions:
7 --				Run GenerateInstancePropertiesForCompare.sql on "First" server. Save output to a CSV file.
8 --				Run GenerateInstancePropertiesForCompare.sql on "Second" server. Save output to a CSV file.
9 --				Use this script ( CompareInstanceProperties.sql ) to load the files into a table, and output any differences
10 --					Don't forget to change file paths and server names accordingly.
11 -- Disclaimer:
12 --				Recommended to run in TEMPDB, unless you want to retain results in the long-term.
13 --				Note that this script runs TRUNCATE TABLE if InstanceProperties already exists. So if you want long-term retention, you should remove that.
14 ----------------------------------------------------------------------------------
15
16SET NOCOUNT ON;
17GO
18-- Create Table for Comparisons
19IF OBJECT_ID('InstanceProperties') IS NULL
20BEGIN
21	CREATE TABLE dbo.InstanceProperties
22	(
23		ServerName VARCHAR(300) COLLATE database_default,
24		Category VARCHAR(100) COLLATE database_default,
25		ItemName VARCHAR(500) COLLATE database_default,
26		PropertyName VARCHAR(500) COLLATE database_default,
27		PropertyValue VARCHAR(8000) COLLATE database_default
28	);
29	CREATE CLUSTERED INDEX IX ON InstanceProperties (ServerName, Category, ItemName, PropertyName);
30END
31ELSE
32	TRUNCATE TABLE dbo.InstanceProperties;
33GO
34
35BULK INSERT dbo.InstanceProperties
36FROM 'C:\temp\SQLDB1_InstanceProperties.csv'
37WITH (FORMATFILE = 'C:\temp\InstanceProperties.fmt')
38
39GO
40
41BULK INSERT dbo.InstanceProperties
42FROM 'C:\temp\SQLDB2_InstanceProperties.csv'
43WITH (FORMATFILE = 'C:\temp\InstanceProperties.fmt')
44
45GO
46
47BULK INSERT dbo.InstanceProperties
48FROM 'C:\temp\SQLDB3_InstanceProperties.csv'
49WITH (FORMATFILE = 'C:\temp\InstanceProperties.fmt')
50
51GO
52
53BULK INSERT dbo.InstanceProperties
54FROM 'C:\temp\SQLDB4_InstanceProperties.csv'
55WITH (FORMATFILE = 'C:\temp\InstanceProperties.fmt')
56
57GO
58
59-- Cleanup unicode remnants
60UPDATE dbo.InstanceProperties SET ServerName = REPLACE(ServerName, N'ן»¿', '')
61WHERE ServerName LIKE 'ן»¿%'
62GO
63
64--SELECT * FROM dbo.InstanceProperties -- debug
65GO
66
67-- Perform comparisons (don't forget to change server names accordingly)
68
69DECLARE
70	@ServerA VARCHAR(300) = 'SQLDB1',
71	@ServerB VARCHAR(300) = 'SQLDB2'
72
73DECLARE @MatchedItems AS TABLE (Category VARCHAR(100), ItemName VARCHAR(500), PRIMARY KEY (Category, ItemName))
74;
75WITH SrvA AS
76(SELECT * FROM dbo.InstanceProperties WHERE ServerName = @ServerA)
77, SrvB AS
78(SELECT * FROM dbo.InstanceProperties WHERE ServerName = @ServerB)
79
80INSERT INTO @MatchedItems
81SELECT DISTINCT ISNULL(A.Category, B.Category) AS Category, ISNULL(A.ItemName, B.ItemName) AS ItemName
82FROM SrvA AS A
83INNER JOIN SrvB AS B
84ON
85	A.Category = B.Category
86AND A.ItemName = B.ItemName
87
88SELECT ServerA = @ServerA, ServerB = @ServerB, ExecutionTime = GETDATE()
89;
90WITH SrvA AS
91(SELECT * FROM dbo.InstanceProperties WHERE ServerName = @ServerA)
92, SrvB AS
93(SELECT * FROM dbo.InstanceProperties WHERE ServerName = @ServerB)
94, ItemNonMatches AS
95(
96	SELECT DISTINCT ISNULL(A.Category, B.Category) AS Category, ISNULL(A.ItemName, B.ItemName) AS ItemName, CASE WHEN A.ServerName IS NULL THEN @ServerA ELSE @ServerB END AS MissingOn
97	FROM SrvA AS A
98	FULL JOIN SrvB AS B
99	ON
100		A.Category = B.Category
101	AND A.ItemName = B.ItemName
102	WHERE
103		A.ServerName IS NULL
104	OR B.ServerName IS NULL
105)
106SELECT Issue = 'Missing Item in ' + MissingOn, Category, ItemName, PropertyName = CONVERT(varchar(500),NULL)
107, ValueOnServerA = CASE WHEN MissingOn = @ServerA THEN 'Missing' ELSE 'Exists' END
108, ValueOnServerB = CASE WHEN MissingOn = @ServerB THEN 'Missing' ELSE 'Exists' END
109FROM ItemNonMatches
110WHERE
111-- Ignore 2nd level categories
112	Category NOT LIKE '%: %'
113
114UNION ALL
115
116SELECT Issue = 'Value is different', Category = ISNULL(A.Category, B.Category), ItemName = ISNULL(A.ItemName, B.ItemName), PropertyName = ISNULL(A.PropertyName, B.PropertyName)
117, ValueOnServerA = A.PropertyValue
118, ValueOnServerB = B.PropertyValue
119FROM SrvA AS A
120FULL OUTER JOIN SrvB AS B
121ON
122	A.Category = B.Category
123AND A.ItemName = B.ItemName
124AND A.PropertyName = B.PropertyName
125WHERE EXISTS (SELECT * FROM @MatchedItems MI WHERE MI.Category = ISNULL(A.Category, B.Category) AND MI.ItemName = ISNULL(A.ItemName, B.ItemName))
126AND (
127	A.PropertyValue <> B.PropertyValue
128	OR (
129		(A.PropertyValue IS NULL OR B.PropertyValue IS NULL)
130		AND NOT (A.PropertyValue IS NULL AND B.PropertyValue IS NULL)
131	   )
132	)
133GO