Voice of the DBA

Author: way0utwest

  • You Need Two SLAs for Disaster Recovery

    Continuing on with my MCM prep, I was listening to the High Availability/DR prep module today and I was once again surprised by something. Typically I have heard all kinds of talk for SLAs, usually in terms of network traffic. For databases, I have had SLA conversations that were for downtime that usually go like this.

    Me: How much uptime do we need?

    Manager: 100%

    Me: We can’t really do that in a cost effective manner.

    Manager: Why not, the telephone companies are always up?

    Me: Well, even the telcos measure their uptime in terms of 9s?

    Manager: (blank look)

    Me: They talk about 99% reliability, 99.9%, 99.2%, each of those being a “9” of availability. The high water mark seems to be companies aiming for five nines or 99.999%.

    Usually at this point I need to write this down so they can understand why five nines are 99.999 and not 99.99999

    Manager: Let’s go for five nines.

    Me: That’s only 5 minutes of downtime a year. We can’t apply patches in 5 minutes. A better number is usually 99.9 for us, which means across the entire year we get a 8 hours of downtime. That’s a good number to aim for across a year.

    Manager: We can’t be down for 8 hours!

    Me: (blank look)

    At this point I usually give up and go in search of someone that will better understand things.

    However an SLA for downtime/uptime isn’t enough for SQL Server. You also have to think in terms of data loss. If we lose a server, what about transactions in flight? What about things not transferred to the mirror server or log shipped server? What about losing disks and no tail of the log backup?

    An SLA for data loss is important as well. And like the conversation above, your business people will say zero data loss. Quiz them to find out what can be recovered and to what extent lost data costs the company. The compute the cost of your various HA solutions to decide how to handle things.

  • Data Scientists

    Would you like to be a data scientist? In some sense I think that many of us working with SQL Server are amateur data scientists. Whether it’s building a report, or just writing code to summarize data, we are working with data in a scientific way.

    What is data science? It’s not really a science, but instead it describes the field of analyzing data and the tools used. It’s primarily used with more traditional sciences to make sense of the vast quantities of data that are accumulated and used to support or debunk some hypothesis.

    Many of us as data professionals have some experience in managing data and manipulating it, but not a lot of formal knowledge in statistics or heavy math. Just reading the recommendations of what might be needed become a data scientist reminds me of a graduate level curriculum, but I’m not sure most of us need that much schooling.

    However I do think that as a data professional that is looking to add strategic value to your company’s applications and trying to stand out, it makes sense to beef up your analytics skills. Finding ways to spot trends and patterns can be very valuable to finding new opportunities in business, and those talents are based on sound statistical analysis techniques.

    Steve Jones

    (Originally published at http://www.sqlservercentral.com/articles/Editorial/72171/)

    Podcasts

  • Isolation Levels–MCM Prep

    I was watching the Isolation Levels video for my MCM prep and learned something about the isolation levels. I knew there were four levels, but I hadn’t realized that SQL Server used the default of level 1, out of the 4 ANSI levels. The levels are:

    • 0 – Read Uncommitted
    • 1 – Read Committed
    • 2 – Repeatable Read
    • 3 – Serializable

    You can read more about them here, but what I learned was that these levels are actually organized in a logical manner. If you look at it this way, the levels can also be described as:

    • 0 – Dirty reads allowed, so data that has been changed in a transaction, but not committed could be included in a query. This means that you could return results in a query that don’t exist. The transaction could roll back.
    • 1 – Potential phantom reads or non repeatable read. Queries only read committed data, but since they don’t lock the whole slice of data, a multi-statement body of work could potentially get new rows between statements or return different results.
    • 2 – Repeatable reads guaranteed, but potentially phantom rows could appear.
    • 3 – No repeatable reads or phantoms possible.

    The flip side of the greater data integrity is that more locks are needed, and held longer as you move up the levels. That can impact concurrency.

    As with everything in databases, there is a tradeoff.

    Note that SQL Server allows you to actually get around some of these issues with row level versioning. Of course, this isn’t free. It comes at the expense of space in tempdb.

  • ETL Security Holes

    The addition of DTS to SQL Server meant that more and more of us could grab data from a variety of sources, easily, and move it to SQL Server systems. SSIS enhanced our capabilities and made it fairly simple for non-programmers to grab web service data, and other formats, and quickly decompose them into relational formats. This made it more likely that we would build data warehouses or reporting systems, and easily keep them up to date with data from our OLTP systems.

    However the ease of using ETL (extraction, transformation and loading) processes to move data did not include the moving the same security controls and restrictions to these new systems. That can create a big security holes, especially when you have secondary systems used for decision support that might not be contained in the production network and not audited to the same level as other production systems.

    Whenever you move data around, there ought to be some set of guidelines for the security protocols required. That can be hard to track when you are pulling data from a secure system and your connections look like every other connection. One of the dangers that I suspect we will run into with Powerpivot is that secure data will be downloaded to Excel worksheets on insecure systems, and all of the controls that we have in place for protecting sensitive information are ignored when we pull data for analysis elsewhere.

    Our data security has gotten better, but we have a long way to go. If you are working with ETL processes, and especially with end-users, make sure that some sort of security policy is in place to help protect this data from being accidently disclosed. It’s not a great suggestion, but until we have better security tools to allow policies to follow the data, that is all we can do.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72157/)

    Podcasts