I was updating an Aireforge Advise fix script and as it’s important that we’re using the correct syntax, I double check everything. Including commands that I’ve been using for 18 years as they can change between versions.
I checked a few articles including TechNet, Microsoft Docs and some popular forums. All of them referenced index create memory and not index create memory (KB); which was confusing.
I know I’ve seen and used both, but I can’t remember when it changed; also all of the Aireforge test servers 2008-2017 returned index create memory (KB). So, I asked for help on Twitter using #sqlhelp.
After Aaron replied, I remembered hearing about this during a Kevin Kline talk at SQLBits 14. Basically, with sp_configure, you only need type enough text for it to find a unique match; which could be dangerous and potentially break your application if the SQL Server team add a new setting that contains the same phrase.
These examples are just selects but updating via sp_configure works in the same way. Therefore to update index create memory (KB) you only need to run sp_configure ‘ind’, ‘1024’; which scares the hell out of me.
If you don’t specify enough text to return a unique entry, you’ll get an error but also a result set. So ‘in’ returned an error and 17 rows.
No surprises here but for completeness, selecting from sys.configurations requires you to use the full name.
I don’t see the point of it. I’m sure it was a time saver for someone but I’ll continue to use the exact wording, otherwise you could run into issues if the SQL Server team add a new setting that contains the same phrase; like Index Create Memory (Max).
For ease of maintenance and performance reasons, we always aim to standardised our SQL Server estates (some of which contain over 100 instances). Everything is synchronised for their specific workload pools, so all search servers and all reporting servers are identical etc. Not only do we standardise the hardware or the VMs, but we ensure that every setting bar the IP and name are identical. This is easy is to achieve if you’ve recently built your estate from clones, but after a while the server configuration can drift, which could impact the performance and stability.
When you have an issue on standardised kit, your first question is usually what’s changed or how is that server different to the others. With over 100 instances to check, this can take a while to diagnose, but you usually find that a setting has changed or a job has been disabled. If you don’t find anything, then you still have an answer and can focus your time elsewhere. You can get a long way with scripts, either dumping to a central table or using a multi-script tool like CMS or Aireforge Script, or even policy-based management, but we found that these approaches still didn’t cut it.
Aireforge Studio instantly compares a large number of settings and uses some logic to ignore false positives, such as differences with server names within jobs. The list of what it compares increases with every release but here’s an overview.
What does Aireforge Studio compare?
Naming (server, instance etc.)
Version information (version, edition, service packs, cumulative updates etc.)
Instance configuration settings (memory, fill factor, max worker threads, recovery interval, CLR settings, compression settings etc.)
Instance properties (clustering info, edition, service pack & patch level information etc.)
Assemblies (creation / modification date, CRL name etc.)
Service information (account, start up type (manual or automatic))
Registry information (Port, working directory etc.)
Server (DDL) triggers
SQL Server Agent Jobs
Examples of what you can use it for.
Full comparisons or partial comparisons; just checking jobs, versions, users etc.
Synchronising the estate. Keep all of your servers synchronised, taking some of the guess work out of service issues and misbehaving servers.
Auditing the estate. You can easily compare every server (limited to 500 but contact the team if you need more).
Testing / Performance Tuning. Create a backup of the configuration before making changes during testing. Store the output with your test results so you can look back and recreate the exact configuration during the tests.
Compare single output files to check for differences over time.
Share configurations with colleagues or use saved configurations as a reference.
Create example files to distribute with your software to aid installations.
Use Aireforge Studio to remotely check customer configurations, reducing support costs and turnaround times.
If you would like to download Aireforge Studio, you can do so by using the following the link.
Aireforge OmniCompare 0.9.0 was the first release to ship with the CLI module. This is a separate application, allowing comparisons to be called from 3rd party applications, such as SQL Server Agent jobs, custom scripts and software deployment tools including Octopus Deploy. OmniCompare CLI will then feedback basic information using termination or error codes; much like log shipping alerts.
The information required to perform the comparison and the credentials required to access the servers are stored within the .adc export file, therefore the main OmniCompare application will be required to perform any future changes such as new servers or new / updated comparisons.
What is OmniCompare?
OmniCompare 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). All comparisons in OmniCompare are available in OmniCompare CLI.
A common use case for OmniCompare is checking production against staging and staging against test. This manual task could be automated using a SQL Server Agent job or building OmniCompare into your deployment process, checking for differences and aborting the release if any are found.
Comparing trace flags via the command-line
Creating the comparison file
Open OmniCompare and select the servers you wish to compare. Once selected, you can now choose a full comparison or restrict it to individual checks.
For the purposes of this blog, we’ll limit the comparison to trace flags.
Note: Although trace flag comparisons work for both SQL Server and AzureDB, many of them are not interchangeable as AzureDB is a very different database to SQL Server. AzureDB also has many trace flags enabled by default (17 at the time of writing).
Checking current values
If no differences are found, nothing will be displayed. Although it’s often interesting to check the current values. This can be done by changing the results filter from Differences only to All.
Showing all results has highlighted that each instance has the non-standard trace flag 1222 enabled, which enabled by Redgate SQL Monitor to improve deadlock information.
Exporting the comparison file
OmniCompare CLI is a lightweight application, therefore the comparison file must be created using the main OmniCompare application and exported. This exported file contains the servers to check, authentication credentials and the comparisons to perform.
To create the .adc file, select the command line icon at the top right of the comparisons section. You will then be prompted to enter a secure password and the location of the saved file.
The OmniCompare CLI executable resides in the OmniCompare program folder. The argument for the application will be logged out if none are passed in or the help argument (-h) is use.
Performing a CLI comparison
To perform a trace flag comparison against the servers detailed above we simply pass in the comparison file and password.
For the purposes of this blog I have enabled full logging, although this should be used for debugging only as a lot of information is logged; especially during full comparisons of large estates.
omnicomparecli.exe -i d:\tf.adc -p <password> -v full
This command could then be run from a SQL Server Agent job or from software deployment tools or from your own custom scripts.
We will now enable trace flag 1117 on the SQL2014 instance to demonstrate how the application changes. OmniCompare CLI has highlighted the change and returned an error code of 1. This error code could be caught and an alert raised or a deployment aborted.
The information detailed above could be logged to file using the -o command, although a simpler option would be to run the comparison via the OmniCompare application.
Discrepancies between configuration settings or database objects can be difficult to identify, especially with larger estates or multiple developer environments. Custom scripts or policy-based management could be used to check some of these settings but they will require updating and only work if the script or rule has been created.
OmniCompare checks a large and growing number of settings, highlighting unexpected changes and preventing configuration drift between instances that should be the same.
Other useful pre-deployment checks
Counts of database objects (tables, columns, indexes, functions, views etc.).
Operators, users or permissions have been applied,
and database settings such as delayed durability, RCSI etc.
So we talked a lot about 0.8.3 and how great it would be but then skipped it and went straight to 0.9.0. First of all, apologies for promising an upgrade and then delaying it but we had good reasons for this.
Version 0.8.3 was supposed to include a couple of new features (mainly CLI) and bug fixes but during our design discussions we decided to completely rewrite how OmniCompare works internally, along with changing most of our development processes. These changes now enable us to easily introduce some really exciting features for 1.0.0 and even 2.0.0 (big plans here). They also significantly reduce our development time so we can focus our attention on new features and even new products (OmniGuard being one).
I won’t list every new feature as these are listed in the release notes but the main ones are:
Improved UI for all screens.
They’re now simpler to use, support DPI scaling (for those lucky enough to have 4k monitors) and it just looks better.
OmniCompare CLI (separate blog post coming soon).
Bug fixes around connections and improvements to the connections dialog.
Version specific comparisons.
We now support major versions but we’ll support minor version changes soon. This is important because MSFT updated a number of DMV’s in 2008 and 2008 R2 during service pack updates and also added some useful ones too.
Tons of new comparisons including users, passwords, OS information, trace flags (really interesting) and more AzureDB comparisons.
Lots of changes in the background to help with new features and development.
Updated the website with changes to the download / update process.
Finally, we now have a Wantoo Ideas Board which enables the community to suggest or vote for new features (any feedback is welcome).
We also have a busy month ahead of us with the 0.9.1 release next week (will happen this time!) and some exciting new integrations with the makers of the worlds most trusted database tools.
If you haven’t downloaded OmniCompare yet you can do so here or update by simply opening the application (assuming you have an Internet connection). Thanks for reading and taking an interest in what we’re doing,