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:
- Comparing two or more instances that are about to have DB Mirroring or AlwaysOn established between them.
- Comparing between two or more instances that should be identical due to a business requirement (for example: Making sure that multi-tenancy/sharding instances are identical, or that different on-premise instances are identical, or that a QA instance is identical to a Production instance).
- Comparing two or more instances to find out why one instance performs differently from another even though they shouldn’t (i.e. maybe it’s an instance configuration? Or database setting? Or hardware difference? etc.).
How to use this?
Here are the instructions for how to use these scripts:
- You run the first script (InstancePropertiesGenerateForCompare.sql) on each SQL Server instance that you need to compare.
- Right-click on the results and choose “Save Results As…” to save it as a CSV.
- 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.
- Open the second script (InstancePropertiesComparison.sql) on the central location. Change all the file paths as needed, to point to the CSV files.
- 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.
- 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:
- Utility Scripts/InstancePropertiesGenerateForCompare.sql
- Utility Scripts/InstancePropertiesComparison.sql
A few remarks
- The script does NOT compare replication and log shipping properties.
- The script does NOT compare Full-Text column mappings, but it does compare the list of full-text catalogs.
- Running the first script to generate the properties requires sysadmin privileges on all relevant SQL Server instances.
Got feedback? Ideas? Suggestions? Go ahead and write them in the comments below!