Voice of the DBA

Tag: high availability

  • 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.

  • The Reliable DBA

    Many of us would consider ourselves to be reliable at work. Our employers count on us, co-workers may assume we’re handling certain tasks, and it’s human nature to think that we are meeting our obligations and responsibilities. Maybe not 100% of the time, but certainly I know my goal is to complete tasks I’ve committed to, on time, and to avoid dropping any balls that I’m juggling. I think I am mostly successful here, but certainly am late or forgetful about things at times.

    However, as a group, department, or even a set of services, does your organization think highly of the databases? Are your systems meeting their SLAs for performance and availability? If not, does your group respond in a timely manner? We sometimes think that we are, but without feedback and communication can we be sure? This certainly could be taken the other way, with other groups constantly complaining about your performance, even while you are meeting your commitments.

    I ran across a talk from Uber on reliability. It’s more of a high level architecture talk about distributed systems and being able to detect and respond to issues. Certainly Uber works at a rate and scale that few of us will reach in our organizations. Add to the highly public nature and real time demands, and reliability is extremely important for their business. Mistakes can have dramatic hard dollar effects instantly, and there is a lot of pressure on their staff.

    For most of us, our databases do continue to become more important, and even when we are meeting our SLA commitments, are we providing reliable service from the entire staff? Is information being shared, with root cause analysis or retrospectives that help knowledge transfer among all of the individuals that might respond to an issue? Are you dependent on a superstar that must be called in to solve issues with the database, network, or storage?

    I’ve been the main person on call, the expert for a system that received calls on weekends, vacation, and other downtime. It’s no fun to be in this position, and it certainly distorts work life balance, not to mention upsetting the rest of my family. My goal is to be there if necessary, but train others so that they can provide a consistent, similar level of service to customers and clients if need be.

    Becoming too dependent on any one person isn’t much different than becoming too dependent on one server or disk drive or network cable. At some point you’ll have a failure and that item will no longer be available. If you don’t have a spare, you’ll have regrets.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Database Mirroring Needs FQDNs

    A quick basic post, and one that I’ve forgotten. Since blogging is a good way to remind myself of things, here goes.

    I was testing Database Mirroring (DBM) recently for an upgrade situation. I’ve set up it up in the past, but since it’s deprecated, I’ve moved on to working with Availability Groups (AG) for the most part. However, mirroring might still be in use for you, or you’re looking to perform a simple rolling upgrade, and DBM works well.

    I restored a database on a new instance, opened the firewalls for 5022, and then went through the mirroring wizard. Once I was done, I enabled mirroring on the secondary database (the one restoring) and that worked fine.

    Then I ran this on the primary:

    ALTER DATABASE Baseball SET PARTNER = 'TCP://192.168.1.201:5022'

    After a few minutes, I got an error:

    Msg 1418, Level 16, State 1, Line 10
    The server network address “TCP://192.168.1.201:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    I tried all sorts of things, including shutting off firewalls, and disabling the rebuilding mirroring. My endpoints were fine, the domain accounts running the instances had access, but it wouldn’t work. I tore down mirroring and added it back, verifying each machine could see the other by name. I tried again.

    ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02:5022'

    I knew I had a problem when this took more than 5sec to respond. Again, an errror.

    Msg 1418, Level 16, State 1, Line 10
    The server network address “TCP://SQL02:5022” can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

    Finally I tore things down again, deleted endpoints, but this time I connected to the instance with an FQDN and configured things. I made I connected to the mirror with an FQDN as well. Finally things worked:

    ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02.HOME.XXXXX.COM:5022'

    I must have read the documentation numerous times, each time reading the FQDN, but somehow thinking that couldn’t be the issue in a small network.

    Hopefully this blog will help me remember.

  • Failover

    I saw a note from someone that had a database set up in a High Availability (HA) configuration for production. This person had received an 823 error on the primary server, but a failover hadn’t occurred. This wasn’t a critical error, but one that noted some anomalies in a few pages, which potentially could be fixed by the automatic page repair in SQL Server.

    In this case, the individual would have liked to have had the system fail over, just in case there were a chance this would impact production. To make this happen, an alert on the error would be needed, which then forced a failover. This wasn’t part of the native SQL Server configuration, and this individual was concerned. However, there are certainly cases where a failover might not be warranted when there is some sort of reaction such as Automatic Page Repair.

    If you are running an HA system, I assume you want control over when and why a failover occurs. If know there is a delay for client connections after a failover, or potentially less resources on the secondary node, or some other impact when you move to a secondary note, perhaps you want to be more careful and when a failover occurs. Wouldn’t you want configurable rules, even those that might require manual setup from a DBA? What about if you have a system that isn’t really designed to handle the full, normal workload; it’s just for emergencies. Do you want to fail to a secondary node if the primary node could still be used?

    There is a whole spectrum of situations where we might want or not want automated failover for our systems. In fact, if you have something like Mirroring or Log Shipping, it can be a complex process to fail back. In those cases, you really want to be sure something has broken enough that a failover is warranted. I’m sure there are plenty of cases where you might not even want to script a failover because you’d rather take a short outage than fail to a secondary machine only to need a fail back in a short period of time.

    Most of us worry a failover won’t happen when the primary system goes down. That’s the main concern we have, and certainly we want to test and be sure this works as we expect in an emergency. I’d also suggest that it might be worth taking a few minutes to think about what happens if your system fails over when you don’t want it? Those can be more problematic, especially if they occur too often and users are dealing with an unreliable system that seems to disappear or pause as it moves from node to node on a regular basis. That might be worse for your reputation than a system that doesn’t fail over in an emergency.

    Steve Jones

    The Voice of the DBA Podcast

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