Custom comparisons

Custom comparisons let you check values specific to your own environment, such as a setting from a DMV that has no built-in comparison yet, without waiting for a Studio release. Custom comparisons need a paid licence.

Managing comparisons

On the Compare tab, select Configure to open the comparisons list. From there you can create a new comparison, edit or copy an existing one, delete one, or restore the built-in defaults.

A comparison has:

  • a name, author and description
  • a category (free text; it feeds the category filter on the Compare tab)
  • a target: Instance, Database, Table or Index, with an optional name pattern for the last three
  • one or more queries, each tagged with the SQL Server versions it applies to, so one comparison can carry different SQL for different versions
  • a rule for each column the query returns

Writing the query

A query is raw T-SQL, not a query language of its own. It is validated as a single batch that returns exactly one result set: either a single SELECT (not SELECT INTO), or a single DBCC or EXEC statement.

As a worked example, the built-in Backup devices comparison is:

SELECT name, type_desc, physical_name FROM sys.backup_devices;

with name set as the primary key and type_desc and physical_name set to normal compare.

Column rules

Each column returned by the query is given a rule: Primary key (used to match rows between servers), Ignore (dropped from the results), Normal compare (values must match), No compare (shown but not compared), Unique (the value must be unique across servers), and several rules for numeric or matching comparisons (percentage compare, equals, greater than, less than, between, not equals, not between, equals column, within % of column).

Where comparisons are stored

Custom and built-in comparisons are stored in Comparisons.json, in %ProgramData%\Aireforge\Aireforge Studio. Studio watches this file and offers to reload it if it changes outside the app, so hand-editing the JSON works. Restore defaults refreshes the built-in comparisons from the Aireforge website.