Compare 2 SQL Server instances

During sql server installations or migrations of old instances to new instances we need to gather information quick in order to be sure that we are installing or configuring the instances in the same way. Sometimes when it comes to performance issues we need to compare instances settings to spot any differences that might cause it. One of the methods to spot differences while comparing 2 instances is to put them side by side and to check the property that you want using one of the built in tools, SSMS, tsql code, SQL Server Configuration Manager, Services management console, SQL Server Facets and others that might expose the setting that you want. Unfortunately this method is sometimes

  • time consuming
  • ineffective when the properties that you want to check are accessible using different tools
  • not appropriate when you want to share your results with someone else

Fortunately we can overcome all the above using Powershell and SMO to build a basic GUI ( inspired from this script # A GUI WMI explorer and WMI Method Help generator available on www.ThePowerShellGuy.com) that displays some of the information that we need. The script attached to this post is displaying properties for

  • server collections
  • jobs collections
  • logins collections
  •  users collections
  • databases collections

For other collections you can check the SMO Object Model Diagram and add them to the script together with the modifications that might be required.

Below is an example of the script output

Some of the properties are at their turn collections and for those when selected the output will show sometime only the powershell object type and not the collection.

As with almost all of the scripts provided here, this script is only something basic that can help us to provide specific type of answers faster.

The script can be downloaded from here

Leave a Reply

Your email address will not be published. Required fields are marked *