Voice of the DBA

Author: way0utwest

  • T-SQL Tuesday #62 – Healthy SQL

    tsqltuesdayThe invitation for T-SQL Tuesday this month comes from Robert Pearl. It’s called HealthySQL and it’s a topic I like. I’ve spent a lot of time in my career being proactive with SQL Server databases, and keeping them healthy is important.

    If you’d like to participate in the party, just write a blog post and publish it on the second Tuesday of the month.

    However, if you’d just like to participate, take the topic anytime and write your post.

    Healthy SQL Server

    I would guess most of us would prefer to be healthy over being sick. While it’s hard to be extremely strict in taking care of ourselves, many of us do make efforts to eat better, exercise, etc. to maintain our physical health. If we don’t, then we get sick and are usually miserable.

    The same thing can happen to our SQL Server instances, and if they get sick, many of us are miserable as well. Mostly because we might be working late, working overnight, or being yelled at by managers.

    I learned a long time ago that no matter how well I take care of myself, I’ll get sick at some point. And no matter what I do for my SQL Servers, at some point they’ll have issues. However if the issues could have been easily prevented, it’s embarrassing and it calls into question my capabilities as a DBA.

    I’ve got lots of stories of how I’ve prevented issues by keeping my servers healthy, but there are really a few simple things I’ve set up and worked on. I included these in my Avoiding a DBA’s Worst Days with Monitoring talk. I need to blog about these in more detail, but here they are with a few notes.

    Backups

    The core of any data system is the backup. If you have the data in a backup (and the backup is good), then you can recover from any other issues. As a result, I’ve tried to be sure that I have backups setup, monitoring on the backup jobs, and a process to make a copy of the backup somewhere else.

    Space

    Running out of disk space is one of the simplest things to avoid and one of the most embarrassing to deal with. I’ve written about placeholders, but you should be monitoring space, have placeholders available, and proactively look for more space as you get low.

    Security

    Security is important, and we read about issues all the time. However apart from hackers, loose security often means that users, developers, or anyone else can cause issues in your database. Tight security can be a pain, but it really prevents a lot of issues.

    Resources

    Resources refer to the hardware and software that allow your systems to function. If you don’t have enough resources for your workload, no one is happy and you are listening to complaints. Being proactive, looking to tune queries, reduce fragmentation, add indexes, reindex, update stats, and more are important here.

    Deployment

    Face it, we’ll always be changing our systems with new patches, code, etc. Building a decent test routine is important as we want to prevent deployments that will make our servers unhealthy. However having a plan to deal with issues (because you will have issues) means thinking about rollbacks or other methods of undoing problems.

  • It’s a Problem When a SysAdmin Can’t Log In

    I showcased a demo recently that looked at a potential issue with an application where a user used a simple update statement to gain privileged access to a system. That’s scary, and it’s a potential issue for many applications that handle their own authentication.

    The problem with this type of attack is that it can be hard to detect. A system administrator might not log in with their account for some length of time, during which the attacker has free reign in the system. However even if the administrator logs in, they might not detect the attack.

    How often have you entered your credentials for a system and it informs you that something was typed wrong. How many times have you locked out your account accidentally with too many bad password entries?

    Have you ever thought that your account was under attack?

    I know some people will have their account unlocked and retry their password, but I know plenty of administrators that would just reset their password.

    That’s bad.

    You should always check the login times for your privileged accounts. In fact, I’d like to be sure that you are using your privileged accounts regularly so that if someone were to gain access, you’d know it. Or if someone changed your password, you’d be aware.

    Treat your sysadmin account for what it is: a privilege, and a target for attack. We are seeing more and more attacks on our systems, and I expect the problems to only grow, not shrink.

  • Selling Automation to Ops

    The DevOps movement isn’t new in some companies. It’s the same coordination and teamwork that has existed for a long time between the development and operations staffs. Developers take advantage of the skills in Operations to get standardized environments for their work, and let the Ops people manage (and track) changes. Operations people talk to developers about the challenges and issues faced in production, and the let the developers build applications that can easily be deployed. The sharing of information ensures each group knows what the other faces, and the regular contact builds bonds and respect between employees. Neither wants to let the other down or make someone else’s job any harder than it needs to be.

    However that’s not the case in many companies where developers view Operational staff as complainers that slow the process down. Operations staff see developers as wild and irresponsible, tossing code into production that they don’t need to support and haven’t tested. Both of these views are correct in that each side sees a reality in the process that makes their job more difficult.

    Ultimately I believe it’s up to developers to change things. Those of us that build the software need to respect the problems that instability causes and learn to help ensure that our changes can be deployed smoothly. The development side of an organization has more skill in tracking changes in version control, in managing the movement of those changes among environments, and in programming systems. We should be working to help push that knowledge through to Operations personnel that become responsible for our applications.

    That means we need to build scripts and tools to migrate our changes and give them to Operations. I’d recommend that we learn how to automate the configuration of our development systems, as well as script our changes. Most modern platforms allow us to programmatically make changes, so let’s do that. Then let’s take a few hours and show Operations people how to use these scripts, and let them setup and change our development environments. It will be slow at first, but they’ll learn to make changes faster, but also bring stability to every environment from development to QA to production, and can ensure we have the same configurations everywhere. We’ll have one less thing to manage, and our changes will get deployed faster, but also more consistently.

    Ultimately we all want the same thing. Better software delivered to customers faster. We want to Ship Safe, and Ship Often.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Data Lakes

    I heard a new phrase this week: the data lake. It comes from a Radar Podcast episode where Edd Dumbill talks about data as an asset that should be exposed everywhere in an organization. There’s a blog post on the subject as well.

    The idea is somewhat centered on Hadoop, but it could apply elsewhere. Data comes into an organization and then tends to seek and get stored with other data in a large lake. Applications are just a way of accessing the data in the lake, but all the data really lives in a large Hadoop lake of information. In some sense, this isn’t far away from the “single view of the truth” that I’ve seen plenty of organizations attempt. In a relational world this means all data moves from OLTP systems to a large data warehouse, and is then moved to smaller data marts (really warehouse subsets) and is accessed from there.

    It’s a good idea in theory, but in the practice of trying to move data around with any velocity between users, with all the copying, cleaning, transforming, and more going on doesn’t work. OLTP systems are needed because there are transactional actions that must be completed quickly and accurately. Moving this data to other systems becomes harder as data volumes and the sheer number of clients (whether users or other systems) increase. The idea that we can keep all of our processes working quickly enough that users won’t get frustrated is likely a dream. The more a value exists in a set of data, the more users will access it. The more accesses, the slower it often becomes, which starts a cycle of smaller subsets of data and applications that subsist on those small data puddles.

    Excel is probably the most common example of a data puddle that exists in your organization. A set of data, perhaps out of date, but useful enough to make decisions based on. Infinitely flexible and convenient enough that updates, changes, and more often spawn more and more puddles where the information never gets transferred back to the large lake of a database, whether that’s an RDBMS, Hadoop clusters, or something else.

    I think the idea of a large data lake is great, but in a practical sense, much of an organization’s data will never live in the lake. If it does, it will most likely be data that’s been superceeded by information in a puddle somewhere on an employee’s laptop, tablet, or personal cloud.

    Steve Jones