Working with large numbers of SQL Server and Azure instances can be extremely challenging. If you’re like most DBAs and developers responsible for estate management, you’re already juggling other priorities. Since you may have inherited the databases or accidentally stepped into the role, it’s unlikely you have time to manually monitor, trouble-shoot, configure and update, on top of keeping everyone involved happy!
If you can’t easily monitor and configure all those databases, how can you make sure they’re consistently patched and running at peak performance? How can you discover issues such as out of sync user access rights and configuration options, or even just run SQL queries on multiple databases at once?
These are some of the issues we wanted to solve when we created Aireforge Studio. This application supports busy DBAs and developers in charge of database estates, using the same workflow whether you’re working with ten databases or ten thousand. All the features described below are totally free.
Simple Estate Management
One of the major obstacles for DBAs with large estates is simply keeping track of them. The Estate module keeps all of your database instance knowledge in one place. Adding new instances is straightforward with the Import option, which allows you to import SQL Server instances from existing database lists in SSMS or from CSV files. You can also discover instances using SQL Browser.
When the number of database instances runs to hundreds or thousands, quickly navigating the entire list becomes unwieldy. What you need is a way to collect related databases together. The tag manager allows you to group instances and assign informative names such as production, testing, and staging. You can add multiple tags to give further detail, such as regional tags. Then, when you want to perform an action against an entire group (such as comparing their configurations), you can apply the action to a specific tag. With the click of a few buttons, Aireforge Studio allows you to efficiently manage an entire estate.
Screenshot: Categorizing a server in Aireforge Studio
Aireforge Studio also includes a way to quickly check the health of database instances and provides several Health Check scripts out-of-the-box for highlighting immediate and potential SQL Server issues. You can also add your own Health Checks, tailored to your specific estate, and run them over hundreds of servers at a time.
Screenshot: health check results in Aireforge Studio
The Power of The Script Module
Many teams use SQL Server’s Central Management Servers for running queries across multiple servers. We did too, but we also found it to be lacking a lot of the functionality needed from a scripting tool, like being able to select individual instances with ease or changing how results are displayed. Using the Script module, you can run queries against the estate you’ve already loaded into your profile, targeting existing groups or individual servers.
Screenshot: running a query across multiple servers in Aireforge Studio.
Discovering Configuration Differences
Aireforge Studio’s Compare module (originally called OmniCompare) makes it easy to find differences between SQL Server and Azure instances. This module compares a variety of configuration and metadata, including build version, users, audit, trace flags, databases, jobs – essentially everything except the data itself. This type of analysis is often a key first step in diagnosing performance issues. As the size of your estate increases, it becomes difficult to determine whether configurations are out of sync. For example, if one SQL server is known to be suffering from poor performance, comparing tuneables such as the default autogrow value in SQL server can help you understand whether the database’s file size is a factor.
The Compare module is a great way to build a fingerprint of the database instance for analysis. Take a snapshot of configuration before making changes, and another after them, so you can easily identify anything that happened unexpectedly as a result. If you have a server you’d like to act as a benchmark for the rest of your estate, take a snapshot, Compare, then you’ll have a list of the changes required to standardize your servers.
Screenshot: comparison results in Aireforge Studio.
The Compare module also offers a command-line interface (CLI) for those DBAs who want to drive comparisons from the command line via a comparison file. One of the biggest benefits of the CLI is that you can use it to integrate Aireforge Studio into your deployment pipeline, SQL Server Agent, or other custom scripts.
Aireforge Studio was designed for database management professionals of all levels. It’s easy to use for accidental DBAs, but there are plenty of features to help power users get their work done faster, such as the ability to add custom Comparisons and Health Check scripts. We’ve been working with SQL Server for over 30 years and use Aireforge Studio routinely in our work as consultants in high-performance and high-transaction enterprise solutions.
Adding these scripts to Aireforge Studio allows us to perform complex tasks at scale. To get the job done, you can customize everything from the Comparison scripts you run to the built-in Health Checks you require. We also provide the scripts in plain text so you can understand, copy and adapt them to better suit your estate.
Try Aireforge Studio Today
Aireforge Studio Community Edition (which is totally free) makes it easy for busy DBAs and developers to manage large, high-performance estates. Whether you’re an accidental DBA or an experienced power user, it keeps you in control of your databases.
Do you have scripts you created, or ideas for scripts, that you believe would be useful for other Aireforge Studio users? Please get in touch! We’re always looking to expand the tools available to make SQL Server estate managers’ lives easier.