Voice of the DBA

Category: Blog

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

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

  • Resource Governer–Memory Limits

    I had looked at the Resource Governor early on when it was being developed and first released on SQL Server 2008, but I hadn’t spent a lot of time on it. It was the first cut at a throttle that I had been asking for since 2001 or 2002 when I saw IIS get a CPU throttle. I loved the idea of being able to slow down the amount of CPU that an individual query could take, which I thought would really help limit the amount of damage that a particular query could do on your system.

    However I hadn’t spent much time on the memory limits. While studying for the MCM, I downloaded this white paper and watched the Paul Randal video from Technet. In it, I was reminded that the memory limits you set apply to the query memory, not the buffer pool memory.

    What does this mean? It means that while you can limit the memory that a query uses for it’s data, for it’s plans, etc., you can’t prevent a query from flushing the buffer pool when it causes a lot of reads from disk.

    This is a limitation of the Resource Governor, and we might see this changed later, but it’s a good thing to remember that you cannot use Resource Governor as a way to guarentee predictable performance from your SQL Server. The engine is still a shared resource and one person can still affect it’s operation. Resource Governor does allow you to limit the damage, and it can be used to prevent someone from bringing the instance to a standstill. However it does not provide the complete control that you might assume is implied from the name.

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