Voice of the DBA

Author: way0utwest

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.
January 17, 2011
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
January 17, 2011
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.

January 14, 2011
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:
January 14, 2011