Custom comparisons in Aireforge Studio

The Compare module in Aireforge Studio can compare the instance, database, security and host configurations across hundreds of SQL Server and Azure SQL Database instances but sometimes you need something custom to your environment. This is where custom comparisons can help out.

Custom comparisons enable you to check values that are specific to your setup, such as the last update time in a table, row counts from replicated tables that should be in sync or you could make use of a brand new DMV, by creating your own query rather than waiting for the comparison definition updates from Aireforge.

Creating a custom comparison

Open Aireforge Studio and navigate to the Compare tab, select Configure from the toolbar and then Add Comparison.

compare_customcomparison

From this dialog you can create your own custom comparison. Lets create one to check if lock pages in memory is set.

Name: Lock Pages in Memory

Author: You

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.

Note: This example query will only work for SQL Server 2016 SP1 or newer and will fail on 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.

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 email us or create a support ticket.

Advertisements

2 Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s