SQL Server: Compare The Configuration Of Two Instances Using sys.configurations

Update Feb 21, 2019: A more recent and more comprehensive comparison blog post is now available. Please check it out: SQL Server: Compare Database Tables/Indexes/Views/SP’s etc. For Differences Across Instances

Earlier, I had posted on how to do an identical SQL installation on another server based on an existing one: Clone A SQL Server Software Installation

Well, if the instances have already been installed and you want to do a quick comparison between two instances, then you can simply compare the contents of sys.configurations of the two instances. Today, I had to do this and am doing a small post to share it.

Steps:

1. Create a Linked Server to 2nd instance on 1st Instance
2. Create an empty table to hold the comparison data
3. Insert the 1st instance sys.configurations data into the table
4. Insert the 2nd instance sys.configurations data into the table using Linked Server
5. Compare to get the differences
6. Cleanup – Drop Linked Server and the temporary comparison table

Note: In the TSQL Script below, replace “2ndInstanceName” with the appropriate instance name before using it.

--Create linked server to 2nd instance
EXEC master.dbo.sp_addlinkedserver @server = N'2ndInstanceName', @srvproduct=N'SQL Server'

--Supply login details
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'2ndInstanceName',@useself=N'False',@locallogin=NULL,@rmtuser=N'SA',@rmtpassword='########'
GO

--Create a blank table to hold the sys.configurations data
select @@servername as ServerName, *
INTO DBA_Sys_Configurations
from sys.configurations
WHERE 0=1

--Get current server's sys.configurations data
INSERT INTO DBA_Sys_Configurations
select @@servername as ServerName, *
from DCVINTLIFEADB.MASTER.sys.configurations

--Get 2nd instance's sys.configurations data
INSERT INTO DBA_Sys_Configurations
select '2ndInstanceName' as ServerName, *
from 2ndInstanceName.MASTER.sys.configurations

--Make sure we have data from both instances
SELECT * FROM DBA_Sys_Configurations
--Has both DEV and INT values

SELECT dev.configuration_id,
	dev.name,
	dev.value AS DevValue,
	int.value AS IntValue
FROM
(
	SELECT *
	FROM DBA_Sys_Configurations
	WHERE ServerName = '1stInstanceName'
) dev
INNER JOIN
(
	SELECT *
	FROM DBA_Sys_Configurations
	WHERE ServerName = '2ndInstanceName'
) int
ON dev.configuration_id = int.configuration_id
	AND (dev.value != int.value
		 OR dev.value_in_use != int.value_in_use)

--Returns no rows if there are no differences..else just the differences

--Drop linked server
EXEC master.dbo.sp_dropserver @server=N'2ndInstanceName', @droplogins='droplogins';
GO

--Drop table
DROP TABLE DBA_Sys_Configurations;
GO

Compare Database Level Settings

Above, we compared the instance level settings but there can be enough differences in the database level settings between two databases to warrant different behavior. You could use the below SQL to compare database level differences between two instances side by side (you will be using the database link you created above, so wait to drop it if you plan to do database settings comparison)

select @@servername as servername, * from sys.databases
where name = 'MyDBNameToCompare'
union all
select [2ndInstanceName] as servername, * from [2ndInstanceName].master.sys.databases
where name = 'MyDBNameToCompare'

Conclusion:

This is a quick and simple post. Please alter as you feel necessary to compare other tables too, not just sys.configurations.