Voice of the DBA

Author: way0utwest

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

  • Scaling Up

    A petabyte of disk space

    For most of my production DBA career, I worked with databases whose size was measured in MB. A few got into the GB range, with the largest dataset I ever managed being 800GB. That was in the days of 35GB and 70GB drives, when SANS were just being deployed widely into companies.

    I did have the opportunity in the SQL Server v6.5 days to interview with a few companies that had TB sized databases. One was running a 13TB v6.5 system and when I heard that on the phone interview, I declined to continue the process.

    I like a challenge as much as the next person, but at that time I had a 6 year old and an infant son and preferred being able to go home at night and see them. I am not sure how I would approach that challenge today, but I did think this would be a good Friday poll.

    Would you want to manage a large scale SQL implementation?

    By large scale I am thinking of a 10+TB OLTP system or a 30+TB warehouse or cube. Something that is out of the ordinary in terms of scale, and which would present challenges outside of the systems you currently work with.

    My thought is that at this stage of my life, I don’t need the long hours required to deal with some operations on these systems. Simple checks, test restores, and all sorts of maintenance become larger issues when you are dealing with TBs of data. Unless we get substantially faster hardware in the future (in terms of IOPS), I can’t imagine how we will handle PB sized databases.

    Let us know if you are up to that challenge today, and if you work on VLDBs, I would be interested in knowing how you like it.

    Steve Jones

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

    Podcasts:

    1. Video Podcast 20.4MB WMV
    2. Video Podcast – 14.9MB MP4
    3. Audio Podcast – 3.4MB MP3

  • Operating in Failure

    This editorial was originally published on Sept 25, 2006.

    This quote is great: “Any large system is going to be operating in failure mode most of the time.”

    It’s from Peter Coffee’s essay on learning from paper based systems and what works in their digital equivalents. Peter examines the conversion of a paper based system for keeping track of Boy Scouts’ work on merit badges as the process moves to an online system. He points out a few flaws in the design matching the process.

    More importantly, he points out that the system fails in a few ways, which is to be expected, but it doesn’t fail gracefully. I think this is a good point and one that most developers fail to take into account. Most systems will “fail” in some way and should be able to handle that failure. Whether it’s data entered incorrectly, a mishmash of keys hit, incomplete data, or something else. We seem to expect that our systems will move along perfectly and that our systems will “force” the users to work in the approved method and process, thereby improving efficiency and ensuring things work well.

    I’ve encountered this in many places, but none more apparent then surveys that we used to send out to customers. We’d require answers to many questions to ensure that we could properly fill the parent and child tables in the database and ensure proper reporting. The developers were happy to run along and build this and make it work as expected. My issue, however, was that the surveys were sometimes long and complicated and we should be sure to capture some information in the event the user stops early or has connectivity issues. In other words, assume we will have issues and plan for them.

    You’ve probably encountered this in other places as well. Every system, especially web based ones, should be expected to fail at some point and you should gracefully deal with those issues. We’re all human and we make mistakes and the systems we build will have bugs, problems, or even unintended uses by our clients.

    So plan to fail and you’ll be better off.

    (This editorial is published at: http://www.sqlservercentral.com/articles/Editorial/72149/)

  • iPhone or Android?

    With the announcement of the iPhone on Verizon, I’m getting ready to switch carriers. I’ve had decent luck with a jailbroken iPhone on T-Mobile, but my coverage is spotty, I’ve been working with the two-gees on an Edge network, and I’m ready to pick something up that’s more modern and powerful.

    I was hoping for an LTE iPhone on Verizon, but that didn’t happen. It might happen with the iPhone 5, but I’m not sure. My guess is Verizon is going to see how their new Android phones work on LTE and gauge capacity first. Then we might see one in the fall.

    So my choices boil down to:

    • iPhone 4 on Verizon (3G)
    • 4G Android on Verizon
    • 4G Android on Spring

    I suppose I should consider a Windows Phone 7 device, but honestly I don’t know that I’ll get the app support I want. To me the differences between iOS and Android are non-existant on the hardware. Sure one phone might be slightly more powerful, but not by a lot. The interfaces are similar, I can get a real keyboard on Android, but the rest is just noise. The marketplace is important, and music sync is something I want.

    I looked around a bit and it seems that music sync is something that could work with Android, and I have a few choices there. So really it’s a question of the various apps I use working on Android. It seems that many of them are either on Android, or there are similar ones. So I could make it work on Android, and I wouldn’t necessarily need to re-invest a lot to move to Android.

    Ultimately I’m not sure that I want to move away from iOS if I don’t gain much. I am definitely leaning towards an iPhone 4, and living with the slower 3G speed. Heck, it will be an upgrade for me in cities, and out near the ranch it won’t matter. No 4G is coming here anytime soon. Half the time we can’t get 3G.

    I saw an interesting article about the iPhone on Verizon and how it’s not their best smartphone. I question if that’s true, since the raw hardware specs don’t mean everything. I have a few weeks to think about it and research a bit, so that’s what I’ll do.