Voice of the DBA

Category: Editorial

  • A SQL Server Bug and Data Security

    This week I saw a post from Microsoft Tiger Team on the issues with backup compression and TDE databases. Apparently when they added compression, they didn’t test a few edge cases. Or a few regular cases, like WITH INIT. There is a possibility that your data could be corrupted, which is a major problem. I have found the SQL Server backup and restore capabilities to be very solid over the years, so this is disturbing. If you use TDE, apply SQL 2016 CU7 for RTM or SP1 CU4 ASAP. Test your restores, and be sure you know what you can recover and what you can’t. If Mr. Murphy has anything to say about it, you’ll have an issue soon, so test your restores.
    Overall, SQL Server security is very good, but there are certainly issues with applications and devices that connect to SQL Server. You never know when some item that queries or alters data in SQL Server will cause issues. This week there were a couple notes from Bruce Schneier on FaceID and Bluetooth security, the latter of which has a vulnerability issue. Be sure you are aware of issues here to actually help protect yourself with your devices, but I was amazed to see this piece on infrared camera hacking. A POC on using light to jump air gaps is truly frightening. It seems that truly anywhere that we are processing data, we need to be thinking about security.
    The last few weeks are especially scary for many people, particularly with the Equifax breach. I know they have released numbers on people’s data, but I’d assume that everyone who has ever had credit in the US has a potential issue. I know I am being careful with credit and watching for issues myself. You should, too, and demand better security from companies you do business with. We can improve systems, but it will take more pressure to get companies to put more emphasis on better software and security.
    Steve Jones

  • Help from Cortana

    I’ve been reading about and watching some of the experiments that people are conducting with Cortana and other types of devices. I’m not quite sure what I think, though I would like to experiment with Cortana at some point and see how useful a computer assistant might be. I have tried to make notes of ideas that I think could be helpful, but for some reason I haven’t really gotten the speech bug. I don’t talk to my computer or phone very often, with the exception being texting with my family while driving. In that case, speech is handy and much safer than anything else. My wife is the opposite; she really likes speech.

    It seems many companies have been working to implement more assistant-like interactions with users. Amazon has the Echo and Dot devices with Alexa, Google has the Home speaker, and I’m sure more and more devices are coming over time. I don’t know how useful these are in multi-person situations where people are speaking, but it does seem that some people like the help from devices. Perhaps I’m too much of a DYI person, since I prefer to push buttons and make my own selections. However, it seems some people prefer to talk, even in busy, loud, crowded situations.

    Many of us are data professionals, and even if we aren’t planning on using speech, there is the possibility that bots or other technologies might help us improve the way we work with databases or help our customers use the information we manage. I’ve seen some interesting interactions with Slack, including one company that allows limited developer access to systems through a Slack bot that handles some provisioning work, including raising and closing a ticket, without requiring human interactions.

    I could even see some sort of alerting or “check this soon” interactions that might alert developers or administrators to take actions in a more casual way, perhaps doing so as part of a conversational queue of things to check rather than a list of items in a tool or a stack of email messages. An interactive assistant can be useful not only in providing information, but managing the flow to prevent overload. A personalized assistant might even do a better job or reminding you of items over time, perhaps anticipating future needs, or realizing some task hasn’t been completed in some time. Certainly the same type of bot or agent might perform work on your behalf.

    This week, I’m wondering how many of you might find ways that a narrow, niche assistant might help you. Is there something in your work that an assistant might help you do? Or maybe there’s some way that a device might help you in your life, separately from a simple notification on your mobile or through email. Perhaps in conversation or in a semi-structured fashion. I’m sure many of you have a creative way that you might like to see technology assist you.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Avalanche of Cloud Computing

    Cloud computing is growing fast. The leader, Amazon and Microsoft, continue to show more and more customers using their platforms every quarter. I do truly think that an increasing number of organizations are thinking cloud first for application development. That doesn’t mean all systems, nor does it mean that they won’t opt to bring their code back on premises, but they are considering the cloud.

    Will this trend continue? For most of my life, I’ve seen trends come and go, but the popular ones continue to evolve and grow. I suspect that for now, cloud computing is attractive in many ways, not the least of which is a shift from a large up-front investment to a small pay as you go charge. Lots of finance people would rather see this as a way of investing in computing for their organization, knowing they can cancel and switch their investment elsewhere.

    I think clearly the cloud will continue to grow for now, but will companies ever reverse the trend and move things back on premises? I wonder. It does seem that many people try to move to the cloud and struggle with the application changes, the unexpectedly high monthly costs, and the wandering performance levels of their systems. For executives used to knowing what the predictable level of performance was, even if poor, having systems (appear to) run randomly slower or faster can be maddening. I think some of this is the lack of understanding of cloud paradigms and programming practices for many developers as well as poor application architectures. But that’s the state of the world, so if systems don’t run well, why would you move away from your current data center?

    The major cloud providers are providing lots of services and incentives, and certainly they are working hard to sell you computing resources. For some technologies, this makes perfect sense. I’d never set up an email server or system again, relying on a service like GMail or O365. I’ not sure I’d even want to host a web server again, expecting that something like App Services is just out there, and I can drop my code in a location and I’ll have a running website. And, of course, I love VSTS. If I were a developer starting a company, or a project, I’d consider using something like VSTS or a TeamCity suite with a cloud VCS. I mean, why invest and manage servers? I just want services.

    There are issues with cloud computing, and certainly your flexibility can be limited. Security offerings are both better and worse, and pricing can be steep as you scale up. Of course, if you scale down you haven’t spent a bunch of money on extra hardware. Overall, I think there are some sweet spots for cloud computing, and even though some workloads don’t make sense, I’d expect that it’s worth considering the cloud first.

    I do expect that some services, such as email, may never come back on premises. I also think some of the skills and tools we use will change, evolve, or disappear. For everyone, I’d suggest you experiment and learn some things about the cloud. Get a free account to experiment with, download emulators, or just watch a lot of presentations and content on the cloud. Whether you have plans to move or feel pressure now, I’m sure you’ll have to deal with the cloud at some point in the next ten years, so be prepared.

    Steve Jones

    The Voice of the DBA Podcast

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

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