Voice of the DBA

Tag: monitoring

  • What’s Downtime?

    There was a time when I worked for a company that sold products on-line  Since our wares could be purchased at any time of the day or night, we wanted to ensure that our systems were running all the time. This led us to build some sort of monitoring, which we tried. That led us to buy some monitoring software, which we did. This led us to build more tools, and it felt like we were in an endless loop for a period of time.

    Eventually we stepped back and tried to answer the question that many Operations people have asked themselves and others: what is downtime?

    It’s a tough question, and I want to give you a few examples of how I’ve viewed things, and debates I’ve had. For example, we had a database server and a web server. We used a simple script to ensure that the services (IIS and SQL) were running on both machines. If they weren’t, we received a page. Is that sufficient to detect if our system is working?

    We also had a process that would ping our web server from outside the data center, using a public machine. If that works, is the system working?

    In this job, we deployed new code every week, in a DevOps style process that existed before anyone had ever uttered the term. These updates sometimes included schema changes, but almost always included application changes. If a page on our website broke after a deployment, was our system up or down?

    We integrated with some third party software to perform various tasks. There were times that we couldn’t communicate with the third party, or received broken communications. In those cases, were we up or down?

    We built our application to work with multiple browsers, but at times there would be a new piece of functionality that didn’t render or work correctly on either a new (Firefox)  or old browser (IE6). Did that mean the application was down?

    Determining uptime isn’t a single thing. Even when you provide mechanisms that ensure all parts of your application are working, are they working for everyone? Many of us might see this in various online calls, where a system like GoToMeeting or Skype might work for some of the audience and not others. I see this at times with Microsoft sites where some of us can use one of their online systems, but others can’t, sometimes because of the browser of the end user.

    I was thinking about this while researching zero-downtime deployments, which can be hard for database changes. There are people that have success, but many others don’t. At Redgate Software, we are trying to build tools to make this easier for everyone, but there seem to be plenty of edge cases that cause issues. There are also many different processes and flows that groups use to perform database development, which often affects the final deployments. It is hard to build a general solution that needs to apply to specific environments.

    I tend to learn towards measuring uptime of the systems I’m responsible for and letting others worry about intermediate infrastructure. I’ll caveat that with the note that I sometimes only worry about sections of the system and if those are broken. It’s good to be clear when talking about this topic with others. For example, we might be able to take orders, but can’t report on them, or can’t add new customers. That’s downtime for some sections of our application, but less stressful than if we couldn’t take orders.

    Let us know today. How do you measure downtime or uptime, and where is your responsibility?

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Republished: Monitoring for Non Existent Events

    Over in the UK today, so the editorial is being republished: Monitoring for Non Existent Events.

  • Lowering the Noise

    Over my career, I’ve had the chance to work in a variety of environments of all sizes. I’ve managed systems that powered all sorts of applications, with a variety of requirements. In many cases, I’ve had certain databases that needed constant monitoring and care from me to perform as needed. I’ve also had other systems that the business used, but weren’t necessarily critical. Those systems often had a lower priority for my attention if multiple problems occurred at the same time.

    I’ve been meeting regularly with the SQL Monitor team as they try to enhance and tune the next version of their product. As the product has grown and evolved, and SQL Server advances, there are different types of counters and metrics that need to be tracked. One of the major goals is to ensure that they reduce the number of alerts for DBAs and sysadmins that don’t require immediate attention or may be unnecessarily adding to someone’s workload, at least for new installations. You might customize your system to include many alerts. To do this, we have to make decisions for the default alerts and threshholds, which can be a challenge.

    That’s been a goal of mine as an administrator as well. I don’t want to get notifications or alerts of activities that are expected, such as backups. However, if backups fail, I may need to be alerted. Actually, I’m sure I need an alert, but it’s a question of whether I need to know now, at 2am, or get an alert the next morning at 9am. Often I may choose to respond differently to the QA server than I do for the production Sales instance. One might get immediate attention 24×7 while the other is a best available effort, and certainly isn’t likely to get a response on Saturday night.

    While SQL Server becomes better at adapting to changing conditions, there are definitely times when a human must get involved and decide how to solve an issue. That’s the reason that many of us are employed by others. We manage tasks and make decisions that machines can’t do for us. However, our attention and time is limited. Despite what some managers think, there are only so many hours in the day and week, and we need to make decisions about which items to focus on and handle. Perhaps even more important, unnecessary distractions can weaken our focus and cause us to make mistakes that we wouldn’t ordinarily make.

    As we manage more systems, many of which are important to our employers, a good system administrator will tune monitoring systems and ensure that they aren’t receiving or responding to unnecessary alerts. They work to minimize the interruptions and distractions from lower priority items. Maybe most importantly for long term job health, they ensure that systems don’t cause interruptions during their personal time, especially vacations. Good administrators put another human in the loop when they need a break.

    Of course, we also do ensure that low priority systems receive some attention. We can’t ignore that low disk space warning on the development instance forever.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Monitor All the Changes

    Can you monitor every configuration change and setting on all your SQL Server instances? Can you get an alert every time code changes, or even if an option for an object changes, such as the changing of an EXECUTE AS or the rebuild options for an index? Do you want to know about every security change (new logins, grants, revokes, etc.). Can you keep up with every alteration of a SQL Agent job?

    You certainly can, but across any busy enterprise, all these alerts might result in a constant stream of items to review each day. In fact, in some companies, the volume might be high enough that this becomes a full-time job for someone. I’m not sure many of us think that it’s worth an employee’s time to actually review every change.

    Even if you decided it’s worth reviewing every change, is it possible to do a good job actually doing so? Too many alerts usually result in an individual starting to treat all of them as though they are the same priority. It becomes hard to differentiate what’s important to review and what’s not over time if you are always looking at a stream of changes across disparate systems. This is one reason why I never want to get success message, but even failure or change messages can be problematic when the volume is high.

    What can you do in a situation like this? Certainly there are alerts that are critical and need to be addressed right away, aren’t there? There are, but they are probably few. Making the decision about which items are important enough to review daily can be hard. I’ve typically only wanted critical alerts for backup tasks (after some retry) and privileged security alterations (add/change/remove sysadmin/securityadmin/processadmin/serveradmin). Those are items I need to take action on. Most other items, such as failed index rebuilds, job schedule changes, configuration alterations, I just want to capture and log.

    In one of the SQLskills newsletters recently, Paul wrote about an issue where replication settings had changed. The distributor had gone from 72 hours to 72 days, resulting in memory pressure for the workload. While I know this is a problem, is this the type of alert you’d define as critical? I’m not sure I would. In fact, this is the type of alert I’d want logged as a set of changes on this system so that I could review it when it seems that the system is not running as smoothly as it otherwise might be. In fact, this is the type of problem I’d hope I’d catch through performance monitoring, which might pro-actively catch performance degradation that would lead a DBA to review changes and metrics before a user reported the situation.

    Ultimately I want monitoring systems to help me find issues, and only notify me when I might need to take some action. To do this, the system might need to capture everything, but I want most of the items filtered until such time as the information might actually help me solve an issue. This takes some time to setup and tune as you discover holes in your monitoring, or you find that too much data is being passed on. Don’t be more afraid to add more data to capture, but be ruthless about removing extraneous notifications from the system. This is a case when too much information sent to the administrator is as bad as too little.

    Steve Jones