Business Development & Marketing Manager at Aireforge.
My posts are written with the help of the Aireforge team, who have more than thirty years’ experience in SQL Server management, optimization and tuning. We create simple, easy to use tools that help you navigate some of the issues we’ve seen over the years.
intuitive are the leading travel technology supplier of the iVector reservation platform. They wanted to ensure they caught any performance issues before they impacted customers, whilst optimising the ‘health’ of their servers. Using Aireforge Studio, they identified areas to troubleshoot in minutes. In hours, they’d made changes to improve usability. Now, they have a roadmap of improvements to make their application perform even better than before.
intuitive provide a complete reservation platform for Tour Operators, Online Travel Agents and Hotel Wholesalers. Their customers’ databases vary as much as they do – from value-for-money holidays that rely on high transactions, to low-volume, exclusive tours. But whatever the customer, the need is the same: fast, scalable and reliable applications.
Their approach to hosting and server management has always
been to know about issues before they impacted customers, but they wanted to
take this even further.
intuitive’s Infrastructure team ran
Aireforge Studio. Within minutes, the application identified areas to improve
performance and reduce resource consumption.
uncovered some significant optimisations very quickly, followed by performance
and efficiency tweaks that will combine to improve the overall user experience.
The team set to work going through the list of recommendations. Within a few
clicks, they were able to make many enhancements, by using the in-app scripts.
Next, intuitive plan to use the
recommendations to improve customer service. They’re also going to replace
their manual list of regular customer health checks with Aireforge Studio’s as
part of their workflow.
By following in-app recommendations,
reduced a key query for within their holidays quote repricing function.
This will result in time savings for their users and an improved experience for
the end customer.
data security and performance.
By following best practices, the team has further improved how they
identify and resolve issues before they impact customers. They can be more
proactive, adding value to the way their client’s data is managed and providing
a great service.
The team plan to use Aireforge Studio’s shared profile to provide a central overview of their databases. They have confidence to make changes because Aireforge Studio provides links to the resources backing their recommendations.
José Guay is an incredibly busy director at a software company. He uses Aireforge Studio to find issues with his client’s databases. By using in-app prioritization and suggestions, he can get results for his customers, in a smarter, faster way. This frees up time to spend it where he’s needed the most – providing expert advice and mentorship for his team.
José wears many hats at work – director, developer, account manager, mentor and database manager. He works at CSW Solutions, a full-service software development company. Their clients are diverse, ranging from Cultural Institutions to Financial Services.
His biggest test is accomplishing everything the team sets out to do. Their standards are high for their clients and they aim to excel in all areas. However, when managing such a variety of customers and services, it’s a constant balancing act. José is a SQL Server expert, but with so much to do, he doesn’t get as much time as he would like to keep on top of the latest technical changes.
José uses Aireforge Studio to get a full overview of his client’s databases, fast. For companies with large estates, it gives him a quick understanding of what issues should be handled as a priority. This means he can handle his workload more strategically. He then uses the in-app suggestions combined with his own experience to implement solutions.
José also uses the application to conduct quarterly checks and reviews. This helps him standardize what’s checked regularly and ensures client’s estates are optimized in accordance with the latest updates to SQL Server.
Happier clients. By working more efficiently, José and his team can optimize databases even faster than before, with results that the customer can benefit from immediately.
More time. By standardizing some database tasks and using in-app knowledge rather than having to always rely on his own, José’s valuable experience can be focused elsewhere.
Increased innovation. The team can stay on top of the latest technical changes without having to spend hours researching through the in-app knowledge base.
Like it or not, all businesses now rely on their data and infrastructure for everything they do. The database is at the heart of any organization, regardless of sector. It contains everything you need to function: customer’s personal details, orders, leads, projects, employee information – the list goes on. So, when there are problems with your databases, it impacts all other aspects of the business. By neglecting database management, your company is taking a serious risk. There’s also a high chance you’re wasting money on infrastructure and jeopardising future growth. Can your business afford to ignore it?
The price you pay for poor database management
Expensive downtime 98% of organizations say a single hour of downtime costs them over $100,000. For enterprise organizations, this figure increases to $1-5 million per hour! Of course, every business is different, but once you’ve considered the loss of financial transactions, staff productivity, etc. you can see how the figures stack up. On top of that, the cost per hour is increasing, as more of our work relies on interconnected systems.
Loss of reputation There are only so many times a client will try to make a transaction before giving up and going elsewhere. Repeated outage is not just unprofessional – it can risk you losing customers if it happens often enough.
Fines SLAs, regulatory breaches, industry fines. Whether you have any explicitly linked to downtime or not, the effect on services can be detrimental enough to trigger a breach.
Loss of productivity If staff can’t access the information they need to work, there’s not much they can do.
High running costs How much are you paying for infrastructure you don’t need? If the IT team don’t have the capacity or experience to routinely make improvements to the server estate, then the answer is almost certainly too much.
The benefits of good database management
Leaner, faster working By speeding up your databases, you’ll help to reduce everything above leaving you with a quick, more effective foundation on which to run your business.
Happy customers, and more of them Your customers will not only be happier with your faster services, but you’ll have the capacity to complete more sales through your new faster transactions, growing your business significantly.
Saving serious money By making your existing infrastructure work harder, you’re less likely to need to buy or lease new hardware. Even better, you might be able to cut down what you’re using. One Aireforge customer reduced the servers needed for their work from 64 to 1 by using the application, saving them over $1 million.
Business growth Imagine all the things your business could achieve with a faster, more efficient database, increasing your sales and more productive teams?
How we help Our database management tool Aireforge Studio makes improvements to your servers easy. Designed for IT teams, the application analyzes your servers and makes recommendations on how you can improve efficiency, stability and security.
We give you what you need to make the changes, so busy teams can take advantage of the latest industry knowledge without spending hours researching them. All advice is linked to supporting articles making the process transparent, giving you the confidence you need to make changes to your invaluable systems.
Do you want to make drastic improvements to your business through effective database management? Send me an email at email@example.com and I’ll be in touch.
Always On Availability Groups are a great way to improve uptime and protect against data loss. However, whilst the databases within the availability groups are synchronized, the instance objects, users and configuration settings that the system relies on are not. This could cause the following to happen when you failover:
Authentication issues. Your users and database roles are synchronized but do not match up to a server login or server role. Your systems are down and you’re helpless.
Missing/incorrect Agent Jobs. A difference in job steps or schedules can result in jobs not being run or data issues due to missing changes and bug fixes.
Missing server objects. Issues with Linked Servers, Trigger or an incorrect configuration setting could result in queries failing or performing much slower than the primary.
It’s very easy for server objects to become out of sync, causing the above. Changing a user, fixing a script within jobs, updating the job schedule: these regular tasks can all cause major issues after failing over.
“You should routinely maintain the same set of user logins and SQL Server Agent jobs on every primary database of an Always On availability group and the corresponding secondary databases.” Microsoft Docs
How do I fix it?
Here are some ways you can protect yourself from Availability Groups becoming out of sync:
Use an active directory to help mitigate the SID issues (be aware that you might still encounter differences using this method– users’ rights or disabled users, for example).
Manually compare the SIDs between your instance and AG using scripts.
Compare job information using a text comparison tool like code compare.
Create your own custom script that covers every possible object and setting and manually compare the results on a regular basis.
Use the free comparison tool in Aireforge Community Edition.
I’m too busy for that – is there a faster way?
Use Aireforge Studio’s instance-level comparison tool to quickly spot differences (it’s free). Identify varying SIDs, user access rights, server configuration (e.g. max threshold) between your instances in a few clicks.
You can incorporate these comparisons into weekly checks. Many of our users even run these daily so they’re less likely to be caught out during failover. As a minimum, you could compare and fix any differences before planned failovers or any significant changes.
I’ve found the changes, but I need help fixing it…
Working with an estate of hundreds of servers? Finding it difficult to keep a shared, central list of server information up to date?
Everyone seems to have their own solution for maintaining the details their team needs for SQL Server Management Studio/Remote Desktop. Maybe you store this information in a spreadsheet or text file on a shared network drive, have a local copy, or use a specialised separate program to coordinate this knowledge. Maybe you even have it printed out and taped to the wall!
All these methods come with their pros and cons, but besides the pricier options, most require regular manual work. We’ve got another way for you to try, and it’s free.
How Aireforge Studio can help
When using Aireforge Studio, you start by setting up all your servers in the Estate module. The list then becomes your central workspace for you to compare settings, run health checks, scripts etc.
To launch SSMS or Remote Desktop, there’s a right-click option, then the relevant address details are pre-populated. No more moving between documents or copy and paste! You can even give servers friendly names to make your workspace faster to navigate.
For teams, there’s an alternative to a centralised document: share a secure, password protected Aireforge profile. That way, you’re only inputting this data once and everyone is always getting the recent information.
Are you taking some time off over the holiday period? Do you have a change freeze scheduled over the next few weeks?
If so, taking a snapshot of your servers’ configuration is a helpful way of making your work less stressful when you return. That way, when you get back after your time off, you can compare the state of your servers post-holiday with how you left them. You wouldn’t leave your house for the break without properly securing it, so why should your servers be any different?
Comparing snapshots is easy in Aireforge Studio. In the application, take a snapshot of your servers this week. Then when you get back, it’s only a few clicks to compare it against the same servers. You get a clear list of the differences between them, so you’ll immediately be able to see any changes anyone has made. No more hidden surprises, and it makes it so much simpler to revert things if you need to.
Watch our video or read the support page to see how. Then, relax and enjoy your time off, knowing you’ll be prepared for your return.
Configuring SQL Server to run at peak performance is a skill that can take years to learn. But when you need to configure and maintain tens, hundreds or even thousands of instances, it’s a task that can overwhelm even the most seasoned DBA.
The Advise module for Aireforge Studio helps to combats this. Advise analyzes your SQL Server instances and provides suggestions for optimization and best practices. Here, we’ll take a look at some of its key features and how it can improve the performance, security and stability of your SQL Server estate.
Advise offers support for all versions of SQL Server, as well as Azure SQL Database and Azure Managed Instances. It combines expert database analysis with a focus on ease of use. At Aireforge, our extensive database management experience underpins Advise’s recommendations. We’ve taken fundamental operational checks, along with best practices from our experience working on mission critical and high-transaction databases, and packaged them into dynamically generated scripts you can run on your own estate. The tool provides a reliable and time-efficient alternative to writing and maintaining your own diagnostic scripts.
Advise supplies over 50 different warnings and suggestions. Let’s take a look at five key areas.
1. Index Management
Poor indexing can severely hit performance. A lack of useful indexes can lead to excessive scanning of indexes, tables and heaps, potentially crippling the I/O subsystem. Inefficient or fragmented indexes will also result in excessive I/O and CPU usage, as fragmented indexes will use a greater number of pages, missing columns will require another table hit / lookup and missing indexes could result in inefficient query plans and / or expensive table scans.
It’s critical to ensure your tables are indexed properly so that data can be quickly accessed. At the other extreme, duplicate indexes or overlapping indexes require more resources to maintain the duplicated data, increasing the amount of processing needed when data is inserted or modified. Duplicate indexes can also result in sub-optimal execution plans as more indexes means more options for the optimizer and a greater risk of timing out before a “good enough” plan is found.
Getting index management right will result in major performance improvements and Advise will suggest changes to speed up your queries and free up resources by removing duplicates and merging overlapping indexes.
Advise detects duplicate indexes, where the same columns are indexed in the same order, giving you the dynamically generated script to drop the duplicate. It also highlights superseded indexes. This is where index A and index B are indexed on identical columns, but B has additional columns. Where A and B have ‘included columns’, Advise may suggest a creating a third index to replace A and B. Data from the missing index statistics are also factored into these checks, suggesting additions to existing indexes where possible or completely new indexes if required.
Advise also detects disabled, unused or hypothetical indexes, that are consuming resources but simply aren’t used. Dropping these kinds of indexes are quick wins that can save huge amounts of storage and again, give the optimizer an easier time.
Screenshot 1: comparing indexes in Aireforge Studio.
Screenshot 2: choosing analysis options in Aireforge Studio.
2. Configure SQL Server’s Automatic Behavior and Settings
Many of the default settings for SQL Server haven’t been updated in years, despite the increased speed of new hardware. Outdated default settings can cause poor performance and operational issues, so Advise looks at the SQL Server settings and suggests adjustments. This includes:
Recommending min/max memory allowance.
Recommended trace flags.
Operating system best practices.
Advise also ensures that non-default settings have not been used that could impact the performance, stability and integrity of a server. Including:
Checking whether auto shrink and auto close are enabled (both of which can cause massive performance issues if misconfigured).
Ensuring auto statistics is enabled to support the optimizer in creating more efficient query plans.
3. Optimize Parallelism Settings
One of the jobs of the SQL Server query optimizer is planning how to run queries on multiple threads. Advise will recommend an optimal parallelism cost threshold. This is the threshold that SQL Server uses to determine when to run a query using multiple threads. A low setting can cause the optimizer to waste time planning multi-threaded queries. A high one can lead to all queries only ever using a single thread. Choosing the optimal cost threshold for parallelism is a delicate balance and Advise helps you tune it correctly.
4. Database File Management
Running out of space when writing to the database file can cause a major service outage. Catching this issue early is critical so Advise warns if a database file is nearly full. Advise will also make best practice suggestions such as an optimum file count per CPU core. It also alerts you if data files in the same group have differing sizes, or if a data file has excessive free space. These checks are applied to all databases, including tempDB.
5. Database Monitoring and Log Analysis
Advise reviews the SQL Server logs to check for possible problems, or opportunities for improvement. This includes alerts if an I/O request takes longer than 15 seconds to complete or if the server is constrained by low memory. These alerts can be the difference between catching performance issues before anyone notices and unplanned downtime that disrupts your entire user base.
Advise Makes Tuning Simple
Advise has many other checks in addition to the ones mentioned above, but common to all of them is that as well as dynamically generated SQL, you’re given human-readable information to understand what the suggested action is, how to take it, and why it’s being shown to you at all. This way, you can not only optimize your database, but have confidence while doing it.
Every check has come from our experience working on real production databases, and we routinely use it in our consulting work in high-performance and high-transaction enterprise solutions. Advise is written by busy DBAs, for busy DBAs. For more information on Aireforge Studio, visit our website.
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.