Voice of the DBA

Tag: sql server

  • 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

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

  • MCM Prep – Only ONE Transaction Log File

    Unlike for data files, SQL Server does not use multiple files for the transaction log in any way that improves performance. The writes are not striped across files. Instead SQL Server will write to one file, then the next, then the next, wrapping to the first file when all the VLFs are full, just as it would with one file. In other words, it moves sequentially through the files for new transactions.

    I haven’t found a BOL reference for this, and don’t really feel like continuing the search, but Kim Tripp blogs about it here and it’s mentioned in the log file internals MCM video.