Voice of the DBA

Category: Editorial

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

  • The NoSQL Rise and Fall

    I’m not sure this blog that seems to talk about the problems of NoSQL databases in general makes a lot of sense. If you read the comments, you certainly see lots of complaints, but I also think the post isn’t well written. It lists the problems of RDBMSes as possibly deleting all tables while changing a key or being unable to add a column easily. While I don’t know about all RDBMSes, I don’t know any that could lose all data with anything less than DROP DATABASE.

    At the same time, the NoSQL complaints and problems seem to be generally presented, which isn’t good. The various NoSQL flavors of databases vary widely and the way you look at a columnar or graph database is much different than a document database. Really, the piece ought to be separated to look at a certain class of NoSQL database compared with RDBMSes.

    That being said, I do see less hype around NoSQL in general. The class of problems handled by various types of databases vary, and I am sure that lots of people using NoSQL databases are quite pleased with them. At the same time, I’m not sure a lot of the RDBMSes did get replaced or continue to get replaced. RDBMSes have a lot of value and we have a lot of experience, both as developers and administrators.

    There might be a shortage of NoSQL (pick a type) administrators for operating the databases. Even in some of the famous companies that use them, I find developers often explaining a RCA or the troubleshooting of a particular process. While that might be a choice that an organization makes, a lot of developers that I know don’t want to be supporting systems very often, especially at night and on weekends. A couple of bad incidents usually has developers wondering why there isn’t an expert on the Operations staff.

    I do think that many of benefits of different types of NoSQL databases are a bit over-hyped. Not that a graph database can’t handle your e-commerce system, but it likely takes a lot of code, and likely a lot of mistakes in getting it to work well. I also feel that some of the downsides of RDBMSes are minimized. They are a pain for application developers to work with, and the SQL language isn’t very sensible.

    That being said, we have a lot of experience with these databases, and that should not be discounted. Productivity is a big advantage in trying to build new software in a flexible manner. I am all for experimenting with different platforms to understand where they work well, but also learn to use your existing RDBMS platform well. Write better code and you might be surprised just how scalable your system proves itself to be.

    Steve Jones

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