Voice of the DBA

Category: Editorial

  • The Usefulness of Database Features

    SQL Server is constantly growing and changing, as are most database platforms. There are lots of platform changes, among them enhancements to the T-SQL language. Microsoft has added window functions, in-memory structures, the ability to execute code in other languages, and more. Some of these features are well built and some need more work. What’s always interesting to me is what actually gets built and what doesn’t.

    There was an article recently on evaluating features in an RDBMS, and the article uses the JSON data type in Google’s Big Query as an example. The evaluation is interesting, examining whether the feature actually helps the user, or if it is mostly marketing. In this case, the feature is outside of the “normal” conventions of the platform,  but it is useful.

    When I look at SQL Server, there are features that I think are well implemented and others that aren’t. There are some that are very helpful, but some that don’t perform well, at least not at scale. There are also changes that people have requested, but Microsoft hasn’t decided to build (please upvote the numbers table).

    Many of us use a limited number of features inside the database platforms. We SELECT, INSERT, and UPDATE often, sometimes with the built-in functions, sometimes not. Many data professionals know what to use and avoid, but often developers may assume that any feature added will perform well. Most of them do very well with a few rows of data, but can be problematic at the scale of millions of rows. While we would want all features to work well at all scales, that isn’t something that’s going to happen.

    I accept that some features are there to help customers in different ways. I can’t imagine wanting to run Java code from a database call, but some organizations see value in this, so it’s a capability in SQL Server. I’m sure some people can’t fathom why I think a built-in numbers table is needed when there are easy ways to build one. I guess that’s why my priorities, yours, and Microsoft’s will differ, at least with regard to what things should change in the platform. What gets built is up to Microsoft.

    When you use a feature in any datastore, there may be tradeoffs with other techniques, and there may be problem domains where the feature works well or doesn’t. It’s important that you understand and learn the limits and advantages of any particular feature. It’s also important you know how to adopt a feature when it’s better or abandon one when it doesn’t meet your needs.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • A Real World Security Reminder

    A saw a tweet from Brent Ozar about USB ports on slot machines to charge your devices. There are also wireless charging pads. Convenient, but also potential security problems, especially for IT workers that hold privileged access to code or data.

    Please, don’t use public USB ports for charging a device. You never know if there is any data access taking place. Yes, I know that most phones ask you to approve things, but have you ever hit the wrong button on your UI? Know how to turn it off? How quickly would data move or malware install? Just don’t plug in.

    Next, beware of physical security. My wife was using a wireless pad at a Starbucks a few years ago. She was sitting there talking with my daughter, and occasionally checking her phone (she gets LOTS of texts). Someone walked up and started talking with them, pleasantly and unassuming. After a few minutes, they walked away.

    A minute or two later my wife realized her phone was gone. They both looked around and then ran outside. They couldn’t find the person, and since this was a trip to visit colleges, she ended up purchasing a new phone. An expensive trip for us all.

    That reminds me of a few stories in the past from my former CEO, Simon Galbraith. He wrote a piece in 2005 about the issues of losing backup tapes. That used to be a problem, along with the loss of laptops. He also shared a story internally about finding a USB drive near our HQ. Our IT staff investigated the USB drive on an air-gapped computer since USB drives are sometimes spread around with malware.

    In this case, we found that it had been lost by our auditor. No Redgate information was on it, but there was data on it from another client. An accident, but one that could have had severe consequences if someone else found the drive. A good reason to be sure that any data you move around outside of production is masked or anonymized, no matter how secure you think your development laptop or mobile device is in your possession. Mistakes happen.

    We also need to be careful with devices. These days, with BYOD and MFA, it’s especially important that we secure devices and limit the disclosure of data on locked screens. Whether for a practical joke or malicious purpose, having someone else get access to our credentials is not something we want to explain to our employer.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Backup Architecture

    I saw a question posted recently on what data is included in a full SQL Server database backup. I hadn’t seen that question in some time, but the post was a good reminder that this is not an intuitive concept, and new data professionals might not understand how a full backup works. If you don’t know, you should do a little research (and write a #SQLNewBlogger post for yourself).

    The way a SQL Server backup works, either with an on-premises install or the Azure SQL Database version, is well known and documented. Even if you can’t make a “normal full backup” in Azure SQL Database, the process is the same. You don’t have to run the backup, as Azure does that for you, but you can specify a restore and understand which data will be available in your restored database.

    Cosmos DB is a different type of data store, existing only in Azure and storing non-relational data. The service has been promoted quite a bit, and some of you might even be using it. Do you understand how backups, and more importantly, how restores work?

    I ran across an article that discusses the way Cosmos DB continuous backup works. This process isn’t quite what I’d expect. Changes are backed up locally (either LRS or ZRS), which makes sense. However, all changes (called mutations for some reason) are backed up within 100s, asynchronously. That’s good, and it’s not perfect, but it’s pretty good. What’s more, you can restore a container, a database, or the entire account. That matches up closely with what I expect in Azure SQL, including the need to restore into a new account. What isn’t great is that stored procedures, triggers, and UDFs aren’t restored.

    As with any sort of backup and restore operation, you should be sure you understand the way operations occur, the impact of restores, and the costs involved. You get charged for backup space and restores. Maybe the most important thing to know is how to perform a restore. If you have a problem, you want to be sure that you not only know the mechanics of restoring data but how to reconcile any potential changes between the old and new database, as well as how to ensure all other objects (stored procs, etc.) are put back in place and clients are directed to the correct database.

    This process might not be as simple as MS Docs describes, and certainly, I’ve found SQL Server restores are not always as simple as we might like. Practice ahead of time and be sure you can recover a system in a way that meets your clients’ needs.

    Steve Jones

     

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • An Active Job Search

    I caught this link in a Brent Ozar newsletter. It’s a post from Alice Goldfuss noting she’s on the market for hiring. Not that she’s looking for a job, but rather, saying she’s willing to talk to you if you want to hire her. Quite a bold announcement, but she’s quite a talent that many organizations might want. I don’t know that many of us could post something and have companies come find you, but I did like the list of items she lays out for a hiring conversation.

    These are the types of questions that you might keep handy, even as notes, for your next interview. I’ve often had a series of questions like this that I ask when I go into an interview for a job. I’ve learned that I need to be curious and careful if I want a successful position for both me and the company. Both of us are trying to put our best foot forward, sometimes stretching the positives and minimizing the downsides. That means that both sides can be surprised later by how the relationship actually proceeds.

    Many of us proceed from job to job, often taking the first offer we get whenever we are looking for a job. That usually occurs because we need to make money and cover bills. The timing of job interviews and offers usually means that we can’t often consider two at once. Sometimes we can, but it’s been rare for me.

    My view has been that the best time to find a new job is when you have a job. If you are unhappy, or you feel that you want a better position/challenge/compensation/etc., then look for alternatives. If for no other reason, you should be aware of how your current job compares to others. It’s easy to get complacent and not realize that the world of employment has changed dramatically.

    At the same time, I also ask you to actively think about the things that you like and don’t like about your job. Think about what you really want from employment. It could be the compensation, the hours, the challenge, the colleagues, the benefits, or anything else. Make a real list, and then assign some weights to these factors and decide what is important to you. I actually like the Thymometrics that Redgate uses to track employee satisfaction, and I take the data I’ve entered and use that to evaluate my own satisfaction with employment. I do this every year, making an evaluation of my own position and then making a conscious decision to stay. I’ve written about the service, and I love it.

    For 14 years I’ve made the decision to stay in my position, usually because I don’t find other positions offering a better balance of the things important to me. I could make more money, but I’d have to change other things, and I haven’t found the overall value from alternatives to be worth me making a move. I hope you can say the same, but in order to do that, make sure you are actively thinking about what you want from employment, evaluating your current environment, and then actively managing your career in the way the direction that matters to you.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.