Voice of the DBA

Tag: syndicated

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

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

  • Multi-tenant Architecture

    When you design a database, or at least when I do, I think it’s important to build a database schema that is flexible, and anticipates change. You can’t foresee every change required, but you can consider the types of changes that might be required, the places where data can grow, and plan for some evolution in your design.

    Recently I saw a note asking what people thought a multi-tenant architecture implied. To me this has always been a series of data slices, often separate clients’ data, co-mingled in a single database. It might be separate schemas (not usually), but often was separated by the data itself, with each row having something like a clientid (or businessID, regionID, etc.) in each row.

    However the SQL Azure documentation (Connection constaints, first sentence) apparently implies this means a separate database on the same instance. I wasn’t sure this was correct, but apparently this is an interpretation of the term. According to this MSDN Whitepaper, multi-tenant architecture can be a shared server (separate dbs), shared database (separate schema), or shared schema architecture.

    As a note, Wikipedia’s definition could be one or multiple databases. A Joel-on-Software discussion seems to indicate the definition I was used to, and various blogs I’ve read seem to interpret things differently.

    In any case, however you define it, a multi-tenant architecture seems to imply that multiple groups of users or applications are sharing some resource. This might be the database, the SQL Server instance, or possibly, the Windows host server (mult-instant configuration.

    Mutli-tenant architectures are good in many cases, overall. They more efficiently use resources, and allow you to handle a potential larger group of users with limited resources. They can be overwhelmed, but there are ways to mitigate these issues. I will talk about some pros and cons in another post.

  • CTRL Scroll and Getting Old

    The handiest thing that I’ve used lately for a computer is the Ctrl button and the scroll wheel on my mouse. If you’ve never tried it, do it now on this web page. Scroll different directions and you’ll see what I mean.

    Here’s the default view of my blog:

    scroll1

    It’s not bad, and from about 2, 2.5 ft away, I can read this. However if I CTRL and scroll up a couple clicks, I get this:

    scroll2

    Not a huge difference in the images, but much easier to read.

    As I get older it gets harder to read images on the screen, especially when I’m trying to get a lot done. It’s easy to scroll down, but I was finding myself scooting forward on my chair and leaning in. Not the best ergonomics. So I sat back (in my new chair), and made all my fonts bigger. I upped the default sizes in Word, OneNote, EditPlus, etc.

    Getting old is hard, but technology makes this easier. I even run a larger font in my Kindle apps than I Delaney because it’s just easier to read.

    And more fun than browsing the limited supply of large print books at the library.