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.
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'
This is a quick and simple post. Please alter as you feel necessary to compare other tables too, not just sys.configurations.