Voice of the DBA

Category: Editorial

  • Interviews: What is ACID?

    I once had a job interview with a large group of employees at a company. They were arranged around a table, with me at one end. The interview was a round robin affair, with someone asking me a question and me answering. They would take turns, going around the table for a couple hours. Not the easiest or hardest interview I’ve had, but one of the more interesting.

    The first question from the gentleman to my left was, “What does ACID mean?”. My answer: “Isolated, Consistent, …”

    I don’t have a problem taking a few seconds to think, but I have that internal clock, much like an American football NFL quarterback. Mine is not the 5 second one many QBs have, but I know that after about 20 seconds, people are wondering if I’ll answer at all. Or if I’m still focused on the interview. If I go 30 seconds, I might have blown the interview. What would you do here? Do you know the other terms? More importantly, can you answer at a level beyond the words of the acronym?

    My response after 10 seconds or so was to admit I couldn’t remember the word. I could google for the meaning, but I did know this applies to relational database transactions, intending to ensure that we always have a known state for our data. We know that each transaction must full complete or be completely undone. We can’t have the classic issue of depositing money in one account and failing to remove it from another. We also cannot have other transactions interfere with other’s work. This means a transaction on a piece of data must complete before the next transaction can modify it. We also must ensure that if the system were to crash, our database could not restart with data in an unknown state. Therefore, in SQL Server, we write to the log first, ensuring the transaction is complete before we can be sure the data changes are hardened. There are more details you could add to my answer, but this is a core foundational part of relational databases.

    I have never been asked this question in another interview, but I do think this is one of those core concepts that helps me understand and explain other parts of SQL Server, and even of how to build software. I think understanding this will help you answer other interview questions with more depth and knowledge. Hopefully, you’ll have a better answer than I did, with the actual words that make up the acronym.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The Library Job Hunt

    I was reminded of the ways I’ve looked for jobs by a post from the Job Headhunter about library time. Rather than looking for what jobs are available, the recommendation is to take a targeted approach of using the library as a place to focus your energies and find the job that you really want, or at least type of job you want.

    Far too many of us look for jobs when we need them, often feeling financial pressures to accept the first offer we get. The pace of hiring usually prevents us from considering multiple offers, as the timing of interviews may not align. We make decisions for expediency, more than any other reason.

    We also usually fall from job to job, perhaps accepting a position that doesn’t quite suit us, or keeps us in the same position when we’d rather move. Again, time and financial pressures can force bad decisions. There’s also the fact that despite the training and schooling many of us have gone through, we don’t usually think about other options in the hectic, day-to-day chaos of our lives.

    The Job Headhunter recommends spending time in the library researching and thinking about different jobs. Certainly you could do this from the comfort of your home with technology, but I agree with the premise. The library can help you focus without the distractions at home. You can spend time thinking about your life, your goals, your interests, your skills, and more. Come up with a plan that drives your career forward. Certainly your research might give you some unexpected answer, but likely this will help you focus on some intersection of your skills and interests.

    Life is short, and there are opportunities out there for you. Choose the ones you want, hone your skills, and actively pursue the employment that fits in your life. The work, the company, the location, whatever is most important to you.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The Full Court CosmosDB Press

    This year at the PASS Summit, Dr. Rimma Nehme (b|t) will give another keynote address. She has spoken at a number of previous Summits, and is a very popular speaker. This time her topic is CosmosDB, which is where she’s been working at Microsoft for some time. I was lucky enough to see Dr. Nehme at Build 2017 talk some CosmosDB, and I’m sure she’s got more to say this now.

    Actually, I’ve been a bit surprised by all of the writing about CosmosDB that I see taking place. Perhaps it’s that the CosmosDB Twitter account is very active, but it seems that so many .NET developers and other non-SQL Server professionals are talking about how they are building projects and platforms on CosmosDB. I follow plenty of SQL people, but it seems there is a tremendous amount of activity on CosmosDB (and R/Data Science), more than I see with plain old SQL Server (POSQL?).

    When I first saw DocumentDB, the forerunner of CosmosDB, I thought it was a good platform for some workloads. Certainly the heavy write a document, read the same thing of the XBOX profiles and some of their games seems like a good use of the technology. When they added MongoDB, I was slightly more interested, but still not a lot. I really haven’t seen many workloads that seem to exceed what SQL Server can do in a relational platform. At least, not if you write efficient code and buy enough hardware. The change to CosmosDB, however, was really interesting. Allowing graph APIs, key values, and documents in one service is something I am sure is attractive to many developers. Especially the heavy use of JSON, which seems to be a format many developers prefer over datasets.

    I know Microsoft would like more people to use CosmosDB as it’s completely a PaaS, pay-as-you-go service, and that could easily increase their revenue if they can get many people using the product. The have invested quite a bit into the platform, with latency guarantees, multiple consistency models, and plenty of scalability. It’s certainly on my list of topics to dig more into how CosmosDB works and where it might be applicable to solving data problems.

    I think there are applications where a CosmosDB datastore will work well, but I wonder if this is more of just a front end, gather data service, that will require additional databases behind the service were data can be combined and aggregated for other reporting. I don’t know that querying your graph tables alongside key-values and documents will be as easy as it might be in a relational system, but hopefully we’ll see some people (perhaps me) experimenting and reporting back on the complexities and advantages of such a datastore.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Voting for Change

    Microsoft does listen to us. They’ve made a number of changes in SQL Server 2016/2107 in response to community votes and requests. The main way to make these is through Connect, though keep in mind that lots of items get filed, and I don’t know how much consideration is given to suggestions for improvements and enhancements. I’m sure a significant amount of time is spent tracking down bugs and attempting to reproduce them.

    The best way to work for change is to advocate your request and get others to vote on items. I’m sure there are hundreds, maybe thousands of good requests out there, and most of us don’t have time to review them. We’ll often vote only when we hear about an issue from friends or on Twitter. Even then, many of us place different priorities on issues, and might not vote for suggestions that we don’t find useful. However, attention does get votes, so let people know what you would like to see.

    That being said, there are a few that I ran across and thought would be worth mentioning. One that I think is an easy choice, and should have been included a long time ago is the ability to increase the number of SQL Agent log files. Just as we can increase the engine log file count, we ought to be able to do the same thing for Agent logs. Auditing and proper security should ensure we have plenty of log files for busy systems. Plus, this should be easy to change. In line with that, why not more system health session files?

    I like this one for hidden columns, which might be really handy for legacy code. What I’d really like is the string or binary data truncated error to be enhanced. I can’t, for the life of me, understand why this hasn’t gotten some sort of fix. It’s been lingering for far too long, wasting countless development hours. Maybe they could fix it with new virtual tables.

    There are plenty of other issues in Connect, some of which might be very useful, quite a few of which are silly. I still think shining light on some of these and getting more votes might change the future of the product. At least, that’s what I’m optimistic about.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and Libsyn.