Site icon Eitan Blumin's blog

Compare SQL Server Instance Properties

A few years ago, I created a couple of T-SQL scripts that can be used for comparing instance-level and database-level properties between two HA/DR replicas. Originally, this supported comparing only two servers. But recently, following a fan request, I upgraded the script to support an unlimited number of servers that you can compare to each other.

So, I figured, if one person found this useful, there must be more out there that would need this, right?

What can this be used for?

These scripts can be used for a variety of use cases:

How to use this?

Here are the instructions for how to use these scripts:

  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.
  1. Copy the CSV files to some central location (also with a SQL Server) where you’d want to run the comparison. This can be any SQL Server instance.
  2. Open the second script (InstancePropertiesComparison.sql) on the central location. Change all the file paths as needed, to point to the CSV files.
  1. Run the script. It would use BULK INSERT into a temp table and then run the comparisons. The discrepancy details will be output in an XML column per each relevant item and property.
  1. Profit!

The script will output missing items, missing properties, and property value discrepancies.

Gimme the scripts!

Alright, sheesh! Where are you in a hurry to?

Here, you can find the two scripts in our Madeira Toolbox:

A few remarks

Got feedback? Ideas? Suggestions? Go ahead and write them in the comments below!

Exit mobile version