We’ll keep this short, but we’ve created a scripting tool for running queries against multiple SQL instances via Aireforge Studio. Oh, and it’s FREE.
The Microsoft Central Management Servers (CMS) are great (link), but they’re lacking functionality and haven’t had much attention in recent years. So, given that Aireforge users already have their estates loaded into their profiles, we thought it made sense to build a scripting module into Aireforge Studio. You can use the same servers and groups from the estate manager or select individual servers; giving you more control over which scripts are run and where. You can also export to CSV, validate your queries before running them and change how the results are displayed or grouped.
We have a healthy backlog in JIRA for Script, but any feedback is always appreciated. If you think that we’re missing something, please let us know (via Wantoo) and we’ll do our best to implement it.
We’re pleased to announce that Aireforge Studio is now officially live and supported. Version 1.0.7 ships with hundreds of improvements with a new UI, simpler comparisons with advanced options, 2017 support and our new tuning / best practices module; Advise.
Depending on your version, the application should prompt to update after starting. If not, please download the latest version using the link below.
The new Advise module within Aireforge Studio analyses instances, databases, tables and indexes, before providing recommendations. The growing list of checks vary from best practices and naming convention violations to orphaned users and missing, duplicate or overlapping indexes.
The Compare module within Aireforge Studio performs a growing list of comparisons, which can be restricted if certain values are known to differ, such as core count or edition (e.g. Enterprise vs Developer).
Common use cases for Aireforge Studio Compare are checking production databases against staging and / or staging against test, auditing the SQL Server estate, sharing configurations with colleagues / support teams.
Have you ever been asked to help with a database issue but you can’t get access? Maybe you’re helping out a colleague over Skype, responding to a #sqlhelp request or asking for help yourself and want to provide more information. Quite often, there’s a simple configuration setting that may not be optimal but checking every setting could be time consuming and something could be missed (there’s a lot of settings).
You could start by running some scripts like sp_blitz (really useful) but they don’t always give you the complete picture or you need to extract all settings to store, share or compare them. Aireforge Studio can take an in-depth snapshot of a single instance or the entire estate. This can be really useful to do before or after carrying out any changes or if you need to share your configuration with someone else. You could also attach the saved comparison file to the ticket you’re working on, so you know exactly how it looked before and after any changes were made.
Here’s a quick tutorial on how you could share configuration information about your SQL Server instance or instance(s) with another person or organisation.
Taking a configuration snapshot
If you’re new to Aireforge Studio check out the getting started pages, although it’s a quite simple application. Once downloaded, create a profile and add the server(s) in question.
Go to the Compare tab and select the comparisons you wish to run or select Full to run them all. A full comparison should finish in a few seconds and won’t display any results if you only compared one server or if all of your servers are identical. Now save the results as an .afd (Aireforge Data) file and send it to the other party.
Viewing the results
The receiver will also need to have Aireforge Studio installed. Once installed you can either double click on the .afd file to open, or load the file from within the application. To view the results change Differences to All Rows and the View to Grid. If information has been collected from more than one server, you could also use the summary view to quickly see any differences.
Aireforge Studio performs a lot of preconfigured checks but if it doesn’t gather the information you require, you can create your own custom comparisons.
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.
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
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.).
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.
We created OmniCompare over a year ago now and it has evolved quite a bit since the early days of merely comparing the results from a small number of queries; mainly sys.configurations. The application is getting more powerful with each release and we’re also making good progress on OmniGuard, which protects SQL Server estates from unauthorised access, and OmniTune, which provides powerful tuning information for single or multiple databases.
With these new applications nearing beta release and OmniCompare nearing it’s official release, we had to make the decision to either continue with OmniCompare and create separate applications for our others tools or create a single application which contains our growing suite of tools. We opted for the later, and created Aireforge Studio.
A single application enables us to remain lean in terms of development and marketing but it will provide a better experience for the user. Again, this consumed a large amount of our development time and pushed us back a few month, but we’re keen to create the best foundation for our products before the official release(s).
The beta of Aireforge Studio 0.9.5 is now available to download either by updating OmniCompare or downloading OmniCompare from the website.