How many times have you needed to compare the configuration of two SQL Server instances?  What about comparing DB role membership between different copies of a database on the same or different instances?  As a DBA there are many time when you have to compare the results of one query to another.   You can open up the SSMS GUI and eyeball it if you have dual monitors or paste query results into Excel and use formulas to do the compare but neither method is particularly quick or efficient.  Wouldn’t it be nice to take your favorite scripts and feed them into a “comparisonator” and get a simple difference report?

Using PowerShell you can execute whatever SQL code you like on each instance or database and get exactly that.  For a look at comparing files with PowerShell see here.  You can make use of that functionality to compare SQL result sets.

The code below (in the Resources section) allows you to set the SQL instance names, the SQL to run, and the output folder.  Executing the script from the PowerShell script executes your SQL on each instance, putting each result set into a .csv file, and producing a difference report.  I have used this tool in migrations to compare the results of sp_Configure for two instances, the list of Server Role members on different instances, and the results of SQL queries against tables in different DBs.

To try this out copy the code between the lines below and save it as Compare-Recordset.ps1.  in a PowerShell editor like the PowerShell ISE or PowerGUI paste this call, changing the instances and output folder to fit your environment.

Set-location d:\Temp
cls
.\Compare-Recordsets.ps1 -Instance1 "LAPTOM2\SQL2008_01" `
                        -Instance2 "LAPTOM2\SQL2012_PREEVAL" `
                        -SQL "Exec sp_Configure" `
                        -OutputFolder "D:\Temp\Output\"
                        #-Prefix1 "Use ThisDB; " `
                        #-Prefix2 "Use ThatDB; "

Running the call produces three files and the report on my system.

The two output files are .csv output for additional processing in Excel and for you records.  The report looks like this:

The two report columns (InputObject and SideIndicator) are generated by the PowerShell comparison process and I manually added the row numbers on the right for ease of explanation.  Let's walk down the report and see what we have.

Row #1 tells you the name of the SQL instance while the text arrow ("=>") shows that is on the right side of the comparison.  Note row #5 is the name of the instance on the left as you can see by the text arrow.  Taking a look at rows #2 & #6 we see the field names for the returned recordsets, in this case sp_configure,  One of the options in generating the comparison is to suppress matching rows so the meat of the report is found on rows #3, #4, and #7.  As you can see the SQL2012_PreEval instance contains a row for contained database authentication that is a new feature in 2012 we wouldn't expect to find in 2008.  Also note that the remote login timeout is set differently between the two instances.

If you would like to see all the rows in the comparison, including the identical ones you can open the output files in Excel and pretty it up as much as you like. 

I have found this tool quite helpful in migrations and other cases where I need to do comparisons.  This tool doesn't have a high powered GUI or fancy reporting but it will do a quick comparison limited only by your ingenuity in writing SQL.