How SQL Server Monitoring Benefits Whole Organization

Whether you sit in operations, development, the executive team, or IT management, the benefits of a robust monitoring solution can be drawn at every level.

In this article, we cover the ways a monitoring solution can have a positive impact across your organization, regardless of your job role.

Highlighting what those benefits looks like at each level, this article will help guide you in how to choose the right tool for you and your organization.

Table of contents

Introduction “Only DBAs should care about monitoring”
How monitoring benefits your Operations team
How monitoring benefits your Development team
How monitoring benefits IT management
How monitoring benefits your Executive team
How to choose the right tool for your organization

Any SQL Server monitoring solution should have the DBA at the heart of the solution.

But it shouldn’t end there.

The data-driven culture in modern businesses puts IT at the heart of operations. Effectiveness, accuracy, and efficiency are key to ongoing data analysis and the actions taken as a result.

That means organizations need to feel confident about their systems, the data they’re gathering, and that everything is secure and compliant.

In this article, we take you through the ways a robust SQL Server monitoring solution has a positive impact across your organization, from the development teams to IT management, and from finance to the C-suite.

Read it to discover:

• 5 key needs your monitoring solution should meet for the Operations Team
• When the advantages of SQL Server monitoring extend to your Development Team
• Why even your Executive Team benefit, with deeper insight into risk, efficiencies, and return on investment
• Where IT management can use monitoring for better agility, proactiveness, and data management
• How to choose the right tool for you and your organization

Read on this article for a complete survey of how SQL Server monitoring benefits your organization at every level.

Introduction “Only DBAs should care about monitoring”

It’s true that any SQL Server monitoring solution you decide to use should have the DBA at the heart of the solution, with the primary purpose of making it easier for them to manage the estate.

But it really shouldn’t end there.

The data-driven culture most modern businesses have adopted as part of digital transformation puts IT at the very heart of operations. Because of this, effectiveness, accuracy, and efficiency are key to ongoing data analysis and the actions taken as a result.

Organizations need to feel confident about their systems and the data they are gathering from them, in particular, that they are secure and compliant, especially in the case of customer-facing technology or technologies that drive financial systems.

This means that every part of the business should be empowered to understand the data that drives them and benefit from the analysis a monitoring solution provides.

In this article, we will take you through the ways a robust SQL Server monitoring solution can have a positive impact across your organization, from the development teams to IT management, and from finance to the C-suite.

What purpose does a SQL Server monitoring tool serve?

Before we get into the organizational benefits, let’s take a quick look at what a SQL Server monitoring tool should actually do from a technical perspective.

There are a number of options on the market and you will need to decide which best meets the specific needs of your DBA team. But in general, a monitoring tool should:

• Alert you to issues, ideally before they impact your users.
• Enable you to diagnose the root cause of problems and help make improvements to prevent them from recurring.
• Monitor performance to see trends, learn what is ‘normal’, and see what is impacting the system.
• Give you a single overview of your estate so you can get an instant idea of overall health.
• Help you report on performance and health to the rest of the business.

A monitoring tool does not replace the DBA. Instead, it takes away those tedious, repetitive jobs that take up so much time, and frees them to take on more proactive projects that can bring real business benefit as well as job satisfaction.

How monitoring benefits your Operations team

“It’s saved us at least two hours a day in just monitoring the systems, probably more if you add how it’s enabled us to change the way we work.” – Matt Haigh, Senior DBA, Mamas & Papas

The monitoring tool should be central to the way an Operations team works. It should be the first thing they open in the morning (yes, even before Outlook or Slack), and it should help them prioritize their daily tasks.

With the right solution, the standard daily server checks (which can take hours) are already done, so the DBA can move on to any problems that have arisen, target the cause, and fix them.

During the day, alerts will keep the team informed on any new problems that arise, and the monitoring tool should be flexible enough to provide alerts in the way that best suits how the team works – within the tool, via email, or through another tool like Slack. Ideally, you should also be able to determine alert severity to help prioritize them when they come in.

Increased availability

By being on top of the health and status of your estate, you ensure minimal downtime and efficient disaster recovery. Processes can also be optimized by identifying common causes of operational and performance issues, including deployments.

You can use the data gathered as part of monitoring to better predict when events such as patching, upgrades, and additional disk space will be required, and plan those actions to have minimal business impact.

Whether your users are internal to your company or external customers, keeping the systems working, and keeping them happy, is a top priority. If something isn’t working how it should, they’ll let you know.

By proactively monitoring your SQL Servers, you can be aware of a problem before your users are. If you know what the problem is, you can fix it before it impacts on your users, and in turn, they will bother your Operations team a whole lot less.

Increased project delivery speed

Over the last 10 years, DevOps has transformed the way many of us work, and the benefits are tangible. As such, the ability to release software faster, without compromising on quality or security, is the goal of many businesses.

When this approach is adopted across the organization, your DBAs will be expected to deliver projects quickly and effectively, while also understanding the impact of their actions.

Effective SQL Server monitoring gives instant access to data that will help influence project decisions, such as when to deploy changes and the impact those changes may have.

Once your DBAs are relieved of repetitive, reactive tasks through an automated monitoring solution, they have more capacity to deliver projects quickly in line with business expectations.

You also empower them to take ownership of projects in the long term, from planning and delivery, through to analysis and revisions. The tool is just part of the process, but it’s essential in providing the support the Operations team need to succeed.

Do more with less

Redgate’s annual State of SQL Server Monitoring survey has found that in most cases the number of servers in use by organizations is growing, but the size of the team responsible for managing and maintaining them is staying the same.

For a DBA to effectively stay on top of the servers they are responsible for, there needs to be an element of automation, particularly around laborious and repetitive daily tasks. This will free up their time to work on bigger-picture projects that help move the business forward.

This approach also helps with job satisfaction. If you replace mundane tasks with proactive projects you get a happier and more productive team. You should also see fewer recurring problems as tasks become standardized through the tool, and this leads to better long-term planning.

How monitoring benefits your Development team

“We spin up a lot of servers very quickly so the ability to automate the process of getting monitoring set up is critical.” – John Bowker, Product Owner, Xero

While the DBA might be on the front line when it comes to SQL Server monitoring, the development team can benefit enormously when the right system is in place. It enables them to work more closely with the Operations team to get an insight into the estate, servers, and databases they are working on. It even allows them to self-serve a lot of the standard information they require to plan releases and work on improvements.

A monitoring tool can catch deployment problems across different environments, so if you are testing an update you’ll see any impactful issues before they go live. This allows DBAs and developers to work in partnership, using the same data set, to manage updates and releases.

If a deployment causes a problem, a monitoring tool should allow the DBA to identify which deployment was responsible, when it happened, what impact it had, and who did it. This isn’t to assign blame, but to help both teams work quickly to fix any issues and prevent future occurrences.

Digital Outsource Services: a 15-minute fix for a bad deployment

One weekday evening the Digital Outsource Services deployed some new software, an online game, onto one of their servers.

Theresa Boonzaaier, Senior DBA, checked the Analysis graph in Redgate SQL Monitor just after the deployment, and noticed the server was being hit hard: “As soon as we’d deployed it, the CPU usage had gone from 20% to 80%.”

They set the time range in SQL Monitor to the past 24 hours and went to the Top 10 Expensive Queries report. This showed them which queries had been taking up the most processing time since the deployment.

They discovered the culprit was a single badly-written query, originating from the recently deployed application. They were able to go to the development team with the data, and after rewriting the query, CPU usage on the server dropped back down to 20%. “It took us probably 15 minutes to pinpoint the problem,” says Boonzaaier

For Boonzaaier, an early warning system was essential for catching deployment problems: “If we didn’t have SQL Monitor, we wouldn’t have noticed it until we deployed the next thing on that server, and all of a sudden the server would have flatlined.”

As an example of a problem that can arise from code changes and which a monitoring solution can help you resolve, consider code-related deadlocks. These occur when two or more threads within a complex set of commands contain related locks that end up bringing the whole session to a standstill. A deadlock can be viewed as a circular locking chain because every process (SPID) in the blocking chain will be waiting for one or more other processes in that same blocking chain, such that none can complete.

While SQL Server has the functionality to kill one of the deadlocked processes to allow the other to proceed, it doesn’t solve the root cause of the problem, and it means that at least one transaction will need to be re-run once you’ve identified what has been killed.

Monitoring should capture deadlocks when they occur, initially alerting you to the issue but also allowing you to see exactly what happened, so you can re-run the transactions that didn’t process, identify all those involved in the deadlock, and re-write them so they don’t do the same next time.

The tangible evidence collected through SQL Server monitoring gives both developers and DBAs the information they need to work on reducing performance degradation and learn from the impact of code-related issues.

Through this, both teams can work on improving the code, which will give them the confidence to increase release cycles.

How monitoring benefits IT management

“It’s really about making my life easier, making my job easier, improving server uptime – and all of this is made possible because I can be a lot more proactive. That’s a huge gain. Like most IT professionals, I get stressed if there’s something going on with the system that I don’t know about.” – Robbie Baxter, DBA, Interactive Intelligence

System monitoring is an important part of IT management, and according to the ComputerWeekly/TechTarget IT Priorities survey, it is a growing priority for IT decision-makers.

The specifics of what needs monitoring will vary from organization to organization and will include general infrastructure and application monitoring, alongside more specific services such as SQL Server monitoring.

And while your IT Manager may not want to spend all day looking at a dashboard themselves, you can be sure that giving their teams the right tools to do their jobs effectively is very important.

IT management has to continually assess where to allocate resources, and so anything that can automate work that takes up staff time to do manually will be of huge benefit.

Agility and proactiveness

Business growth and costs are both affected by the efficiency of the systems in use. If your company is on older systems, that will impact its ability to rapidly adapt and respond to change or opportunities.

IT management will be focused on ensuring their systems are fit for purpose, and in order to do this they need their teams to be empowered with the data to make the necessary decisions when it comes to upscale and transformation.

A lot of this project work gets hampered when repetitive tasks, such as daily checks, take up hours each day that could be better spent on more proactive endeavours.

By implementing the right tools that automate these tasks, while empowering employees with the knowledge to take decisive action, agility and proactiveness are much more easily achieved.

Mamas & Papas: Monitoring mustn’t become part of the problem

Matt Haigh is the Senior DBA with the infrastructure team at Mamas & Papas. His team look after 65 SQL Servers across both the head office and the stores, which run Electronic Point of Sale (EPOS) systems.

Haigh’s main challenge is how to balance the day-to-day requirements of maintaining the SQL Servers with the need to add value to the company through project work. To monitor the servers, his team built their own set of tools using Excel Power BI features and PowerShell scripts. Rather than helping them, however, the tools held them back.

As Haigh explains: “It turned into a bit of a nightmare. We had issues installing the tools on new servers, and trying to add new metrics, and we could only get information about the servers when we clicked refresh. But probably the biggest problem was that our inbox was filling up with emails that we had no way of filtering or prioritizing. In the end, I just wanted my inbox back!”

As well as improving the monitoring position, Haigh wanted his team to perform DBA work rather than develop a SQL Server monitoring tool themselves. They were spending 60% of the day doing routine checks and administering the tool. As a consequence, lead times for internal IT projects were suffering and staff motivation was low because the team weren’t doing work that interested them.

Efficient data management

Data, whether it’s storing, processing, analyzing or protecting, sits at the heart of IT operations. Being able to effectively and confidently manage it is a core priority for the business, and therefore requires accurate monitoring.

As estates grow, there is a limit to what you can do manually, making a tool essential. But that tool needs to be able to scale and adapt with your business – there is no benefit to implementing something that your organization grows out of 12 months down the line.

Much like the earlier point about agility, automating the processes involved in managing your data enables your teams to focus their efforts on proactively improving the systems rather than just reacting to problems.

Knowing when to enable growth is also important. You need a monitoring solution that is able to alert you when you are running out of disk space so you can minimize downtime, but the optimal approach is to plan for this in advance so it can be worked into purchasing and development plans.

With the right monitoring tool, you can map the growth trends of your databases to calculate exactly when more space is required, and then plan the best time to purchase that space and install it.

Security, privacy, and compliance

Internal and external security threats are a constant danger to any IT system and the bigger the organization the more lucrative the rewards for the would-be attacker. Customer privacy is also a concern. IT management holds responsibility for the customer data stored on and processed through their systems, and with legislation such as the Stop Hacks and Improve Electronic Data Security (SHIELD) Act in New York, the Consumer Privacy Act in California, the General Data Protection Regulation (GDPR) in Europe, and the Personal Data Protection Bill in India, the need to be compliant has never been stronger.

As data protection legislation tightens, and the risk of and penalties for data breaches grow, so does scrutiny of how sensitive data is stored and used within organizations. Without the right processes or tooling in place, software development can grind to a halt under the weight of data security concerns. It’s imperative to strike a balance between intensified data compliance needs and business demands for speed and agility.

So how can monitoring help here?

There are a few core areas where monitoring can significantly benefit the security, privacy, and compliance side of IT management.

Without the right processes or tooling in place, your operations teams are blind to your estate’s performance and exposed to the risk of unnecessary downtime and compliance breaches.

With proactive estate monitoring and accurate reporting, your teams can detect, diagnose, and resolve performance problems before they impact your operations or customers.

By being on top of the health and status of your estate, you ensure minimal downtime and efficient disaster recovery. Processes can also be optimized by identifying common causes of operational and performance issues, including deployments.

With effective monitoring in place, you’re alerted to security problems that put your data at risk, such as unauthorized access attempts. Plus, maximizing server availability also meets database availability requirements under legislation such as GDPR.

How monitoring benefits your Executive team

“The senior management appreciate these sort of tools. I can show them the graphs and reports… and give them the information they need faster and in a clearer way.” – George Sibbald, Senior DBA, Prudential

The needs of your exec team will be very different from those of your DBAs, developers, or IT management. They don’t want to see the detail that monitoring brings, but rather the high-level information and the impact it’s having.

Your monitoring solution can therefore allow them to self-serve or allow you to build reports to diagnose and explain situations to them at the right level of detail.

Time and cost savings will be important factors for your CTO, CIO, COO, and CFO. Where can you enable efficiencies that support the business, but free up time for more lucrative projects?

A monitoring solution will be of particular help here because it will free up the time your teams take completing mundane and repetitive tasks, and allow them to progress proactive project work instead.

This approach also fosters innovation, which will be appreciated at the exec level. With the time your DBAs and developers get back by implementing a monitoring tool they can work on more interesting and stimulating projects, which creates a space for ideas to grow.

Demonstrating potential return on investment is a simple, evidence-based way to estimate the value the purchase of a monitoring solution will bring to your business. While there are many intangible benefits that grow out of automating your server monitoring, aspects such as DBA time saved, the average cost of downtime, and fines for data breaches can all be considered when defining the value of the purchase.

Continuity is another key benefit that a monitoring tool can bring to your exec team. They can be confident that business continuity is achievable thanks to the knowledge your teams can gain and demonstrate through a monitoring solution.

The abilities to plan ahead, map developmental changes, reduce bad deployments, and improve uptime are all achievable through effective monitoring. It’s then important that the outcomes of these improvements can be reported back to the executive team in the right format and level of detail.

As with IT management, compliance is something your executives will be keen to not only manage but understand, including learning where risks lie, where your organization is covered, and how things are changing in the industry that could impact the company in the future.

The monitoring tool you use should be able to aid compliance and adapt to new challenges in the future.

Your executive team will ideally want to make investments that will benefit the organization in the long term. This includes choosing a monitoring solution that can evidence development, scalability, adaptability, and innovation. They will not want to go through a procurement process six or 12 months down the line because the tool they previously agreed to is no longer fit for purpose.

We will cover how to choose the right tool for your organization in the next chapter.

Along with purchasing your monitoring tool, you will also be purchasing a partnership with the vendor. This could lead to benefits beyond the initial software agreement, and your executive team will be keen to understand who develops the tool and what else they can gain from forming a partnership with them.

How to choose the right tool for your organization

Choosing the right tool for your organization goes beyond feature comparison and quite often even price.

While you will likely have a budget to stick to and need to ensure the costs of the tool don’t exceed it, the following will help you narrow down your choices.

Remember when choosing a tool to consider the primary users – your Operations team – as well as the other members of your organization who will interact directly or indirectly with it.

Build vs buy

There is another whitepaper, book, or even volume of books we could write about the benefits of buying a tool off the shelf vs building your own. While we would like you to buy our tool, we do realize that purchasing a third-party product isn’t always the best solution for your organization.

In some rare cases, you may have a “no third-party software” policy, related to particular security protocols your organization has to adhere to. In other cases, you may have a very specific set up that no vendor currently services, and it would be fruitless for you to buy in a tool that only goes part-way to meeting your monitoring needs.

If you do go down the self-build route, remember that you need to have the skills inhouse to not only develop the tool but also maintain it, including for security, compliance, auditing, and technology upgrades.

In most cases, however, you will find that purchasing a tool dedicated to monitoring your servers is the easiest and most cost-effective approach.

Forget feature comparison tick boxes

Most SQL Server monitoring tools on the market offer similar functionality, so you may be tempted to do a side-by-side feature comparison, either through your own research or using a comparison from the vendor, to check which solution has the edge over the competition. This is a problematic exercise, as you may end up trying to compare against features that aren’t relevant to your needs. Reducing product capabilities to Boolean feature comparisons misses much of the richness of what makes one preferable over another for your business.

It’s rare that one product is unequivocally better than all others, in all ways, and for all users. Different people have different needs, personal preferences, and experience, which makes decisions subjective.

If you want to compare two products, you really have to try them both yourself. There’s a huge amount you won’t find on comparison charts, such as whether you can trust the people you deal with at each company, or how fast and reliable the product support is. Above all, don’t underestimate the importance of how much you just naturally understand and like a product. While hard to quantify, this makes a big difference to how you’ll enjoy using it, particularly one you will be interacting with on a daily basis.

Vendor reputation

As mentioned in the previous chapter, when you buy a monitoring tool, you are purchasing a partnership with the vendor, so you need to be sure they are the sort of company you’d like to work with. You might like to check things such as their transparency prior to purchase. How do they present the way they work to the outside world, where are they based, do they offer a range of contact options, what is their pricing structure?

You should also check that they and the software meet your compliance requirements, particular to your industry and region.

Finally, are they a reasonable company to work with? How do they come across via email or over the phone? When you meet them in person, such as at events or on sales visits, are their people friendly and presentable? This may seem trivial, but working with a company that shares the same values as you is of real benefit.

What is the support like?

Be clear upfront on what support is included in the purchase price and what costs extra. While all software vendors would love to claim you can use their tools without the need for support, inevitably issues arise due to the complex nature of the estates being monitored and the capabilities of the tool.

When you are running a trial, make sure you contact support through the different methods they offer and test the suitability of these channels. Look out for things such as the length of time it takes to get a reply, the quality of the response you get, who you interact with, and if you feel comfortable with their ability to handle future support requests once you become a customer.

Also, see what sort of community of users exist alongside the company. While online forums should not replace dedicated support, an active community can be very helpful in overcoming specific problems, suggesting additional ways to do things, and generally sharing experiences.

Try before you buy

It goes without saying that you need to try out the software before you buy it. You should be wary of any vendor that isn’t open to letting you test out the tool before you invest in it. Some providers may let you try an online demo of their software in action, without the need to download anything or even provide your details. This is a valuable way of getting a first look at the software to check it’s something you feel is worth progressing to a trial with.

The next step is to download the trial to test the tool on your own servers. Check how long the trial period is so you can download and install it at the right time to properly test it out. Also, ensure the trial includes the full product features and isn’t restricted in any way.

During your trial, you will start to engage with the sales team at the vendor. Use this opportunity to build a proof of concept to ensure you get the most out of it and are evaluating the right features for your organization.

If you are unable to complete a thorough test of the software during the standard trial period, it’s always worth asking for an extension – most providers will help you test the product for a few more days if required. Alternatively, you could negotiate an extended paid-for trial with them to allow you to test the tool over a number of weeks or even months without committing to a longer contract.

Remember, you should get as many potential users of the tool involved in the trial as possible. Try to get representatives from across your organization to test the product in the different ways they expect to use it, to ensure it meets the needs of every stakeholder.

Check the roadmap

Any software product in active development should have a published roadmap available to let you know what the development teams are intending to work on for the next release and in the future. After all, part of what you are paying for is the right to get feature updates for the length of your contract.

When you purchase a tool it’s therefore worth checking that the planned features meet your needs and the product is being developed in a direction that fits your own business goals.

You should also lookout for ways to interact with the software development teams to influence the roadmap and ask them to consider features for the future. This could be as simple as a direct email address for the team or interacting with other users through a forum or a tool such as Uservoice.

Scaling and future-proofing

At this point, remember to take a step back and consider how the monitoring tool you choose will adapt and scale with your organizational strategy. Work with your management and exec teams to get an understanding of future growth plans, and check how these will align to the way the tool scales and adapts to your requirements.

For example, are you planning on adding significantly more servers to your estate within the next three years? Or perhaps you intend to switch from on-prem to cloud hosting? If you are using two (or more) database management systems, will the tool be able to simultaneously monitor the different sets of technology you are using.

Don’t be afraid to ask the software vendor challenging questions about the future of their product and how it will handle the adapting database landscape. You need to be confident that the tool you choose can grow with your organization.

Plan your implementation process

When you are ready to purchase you should properly plan the implementation process of getting the product correctly installed, set up, and customized across your estate.

Check with the vendor what’s involved in setting up. They should be able to give you guidance based on your company size and requirements, about the overheads involved, the time it will take to get up and running, and any specific access requirements needed for successful implementation.

Training

You should also plan for product training, both immediately for users and then ongoing for new starters and for when the vendor ships new features.

Check to see what is provided by the vendor as part of your support package and whether you can take advantage of any specialist tailored training if required. There should be online information readily available to help with onboarding, and the product forums are again a good source of knowledge to answer many common questions.

The training each user needs is likely to differ depending on their role. For example, the DBA will need to fully understand the monitoring processes, plus alerting, analysis, and customization parts of the tool. The Head of IT may just need to familiarize themselves with the reporting dashboard.

Source: Red Gate Software