Comparing SQL Server configurations with OmniCompare

OmniCompare was inspired through the work we do at xTEN, where we work with large, high throughput SQL Server estates. Some of these systems use replicated databases which, for ease of maintenance and performance reasons, we’ve standardised everything for specific workloads. Not only the hardware or the VMs, but practically every setting bar the IP and name.

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 servers 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 MultiScript from Redgate and / or using policy-based management, but we found that these approaches still didn’t cut it.

OmniCompare 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 we compare is still growing and being tuned to work with older systems but here’s the current list.

Screenshot

What does OmniCompare 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.)
  • Database settings (auto shrink, snapshot isolation, page verify option, CDC, CT, compatibility level, recovery model etc.)
  • Database file settings (auto growth, initial size, naming, state)
  • Users
  • Registered servers
  • 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
  • Endpoint information
  • 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 (tested up to 200 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 OmniCompare to remotely check customer configurations, reducing support costs and turnaround times.

We’re also working with Redgate product managers to see how OmniCompare can improve their already impressive DLM suite. The program is currently in beta, so expect bugs, but please let us know if you come across any and send in your logs.

If you would like download OmniCompare, you can do so by using the following the link

Download OmniCompare

8 thoughts on “Comparing SQL Server configurations with OmniCompare

  1. Since Phil put up this blog post, downloads of OmniCompare have taken off. Thanks to you all; it’s great that what we’ve spent time on it proving of interest to people!

    Version 08.1b is now out, with a couple of little bug fixes from 0.8b, and we’ll be keeping an eye on things to fix any more bugs as soon as we spot them. If you try OmniCompare and have any ideas, suggestions, comments or even complaints, please get in touch with us at OmniCompare@aireforge.com

    Cheerio and thanks for the support,
    Drew

    Like

  2. Hey, this tool looks promising, but when I try to run it on my laptop for a test run it crashes at start-up. I just see the ‘create new profile’ window, and then “omnicompare has stopped working’.

    I have installed version 0.8.1.

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s