Custom comparisons enable you to check values that are specific to your setup. For example the last update time in a table, row counts from replicated tables that should be in sync, or you could make use of brand new DMVs by creating custom queries rather than waiting for the next release of Aireforge Studio.
Creating a custom comparison
Open Aireforge Studio and then navigate to the Compare tab, select Configure from the toolbar and then Add Comparison.
From this dialog, you can create custom comparisons.
Example custom comparison
Let's create a custom comparison to check if lock pages in memory is set to true.
Note: This will only work for SQL Server 2016 SP1 or newer and will fail in 2016 or below. You could put your own version check in the script for now, but we'll be making the version selection more granular.
Name: Lock Pages in Memory
Category: Create a new one or put it in Instance \ Hardware
Description: Returns sql_memory_model_desc which is either CONVENTIONAL, LOCK_PAGES or LARGE_PAGES (EE Only)
Query: As this only applies to SQL Server 2016 SP1, we only need a single query, but it's possible to create different queries for different versions of SQL Server. This is useful if you want to convert values so that they match between versions (KB to MB etc.).
SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;
Applies to: Select SQL Server 2016. Again, this will break other comparisons if you have 2016 servers that are not running SP1.
Columns: Select how Aireforge Studio should compare the column(s) and match the rows. We should choose Normal Compare for this example, but the other options are:
- Primary Key: Used to match rows in the result sets
- Ignore: Ignored from the result set and not displayed
- Normal Compare: Should be equal across all servers
- No Compare: Displayed in results but not compared between servers
- Unique: Should be unique amongst servers
Now save the custom comparison and perform an Aireforge Studio compare. If you create a useful script and you think we should include it, please let us know.