Voice of the DBA

Tag: Database Weekly

  • Taking Shortcuts

    Many of us work in situations where we feel pressure to get work done at a pace that’s faster than we might like to move. In some sense, this is the nature of life, where customers and clients always want something done immediately. Whether they’re ordering food or building a house, it seems that quite often the customer expects the creator to just work harder and faster, without making mistakes, and deliver the goods quickly.

    That may or may not work, but if does often result in issues in software. While we can fix them, there can sometimes be larger issues, especially where sensitive data is involved. There was an incident recently that reminded me of this, though fortunately, it appears the data loss was minimal in scope and sensitivity.

    The mobile app at a recent security conference leaked data. The builders of the app embedded security keys and passwords that allowed anyone that registered to download a database of attendees. Fortunately this was a limited set and it appears only names were exposed. However, it could have been much worse, especially if this were a typical non-normalized database that might contain all data about an attendee in one row.

    I don’t know the timeline here for development, and I certainly don’t know the requirements. I do know that embedding keys and passwords into application is a bad idea, and even worse when those applications are going to be installed on customer devices. These are fundamental rules, and I certainly hope that whoever worked on this application, and anyone reading about this story, knows not to do this again.

    No matter how rushed we are, it’s important that we follow some practices and include some seucrity in our systems. I’d argue that data security ought to be number one and built into the system from the start. As the GDPR asks, we should be ensuring this is included by design and default. As much as it might seem that new legislation is overreaching and burdensome, I’d argue that mistakes like this one are all too common when we feel pressure to get work done. We shouldn’t be making these mistakes, nor should be be pressured to ignore security for the sake of expediency.

    Steve Jones

  • Opportunity or Restriction?

    I’ve been at the Redgate office all week, broadcasting SQL in the City and meeting with product teams. Quite a bit of our focus is on the GDPR law, with enforcement beginning in May. We’re building and enhancing tools to help you cope. That means I’ve been spending a significant amount of time trying to learn and comprehend the potential impact to data professionals, both to help guide customers, but also to ensure our software will help them ensure compliance in their data systems.

    Over the last year I’ve seen lots of doom and gloom, everyone needs to fix their systems and ensure data can be deleted on demand concerns in the media over the GDPR. Ultimately I think there is work to be done for many companies in the EU or those that sell products and services to the EU, but we don’t quite know to what extent we need to change existing applications and databases. Lawyers and solicitors will sort some of this out across the next few years, though I certainly think any data breaches in the next year will be dealt with more harshly by regulators if organizations haven’t made any effort to secure their systems.

    There are also some simple things that I think most of us should just do. When I first saw the addition of data classification in SSMS, it seemed fairly trivial. However, the more I’ve thought about it, this simple addition is a way of ensuring that I can easily spend a relatively little amount of time to just think about the information in a database. Just tracking this down can be a pain, and if the information isn’t recorded in an easy to access format, it’s easy to forget what items need our focus. Using Extended Properties is a great idea, as the information is kept with the database, but this means that a better interface than the table properties is needed. There are also a few potential problems doing things this way, but this is a good start to becoming better data stewards.

    That’s what we need. A few good starts. We need to see this as an opportunity to clean up practices and move forward in a way that shows us to be professional data professionals that take our responsibility for data security, accuracy, and usage seriously. This is a chance to move forward in a way that reduces our risk of losing data, of becoming the next “headline” corporation or government agency that makes a mistake. Instead, we can embrace this as an opportunity to find new ways of managing our data and extracting information while still complying with data privacy rules.

    I read a white paper that talk about the challenges of IoT data in a GDPR world. There are issues to be concerned about, but this also means there will be opportunity for those that devise better data handling methods, that learn to clearly disclose their purpose and practices. I suspect most people realize that there is a certain amount of information that companies collect, and that if there is some value or usefulness that company gives back, we can accept their data storage. Where individuals often become concerned is when organizations move their data to other parties without their consent.

    To me, this means it becomes more important for an individual company to understand and process their own data. There will be less movement of data between companies, and perhaps less ability to purchase and import data from others. We’ll need to help our organization extract more value from the data we are allowed to hold, meaning those of us that are data professionals will become more important. At least, that’s what I hope happens.

    Steve Jones

  • We Do Need To Upgrade

    I advocate that we should get a long life from our database platforms. In fact, I like to think about a 7-10 year lifetime for many of my database instances. That means I’ll get beyond the 5 year lifetime that Microsoft provides and move into the extended support. Actually, for most of my career, I’ve run instances without support and haven’t had issues. We invest a lot in databases, and I want companies to invest a lot because they are important. That means I need a good payback.

    Microsoft should provide security patches for products throughout the Extended Support period, which means that I should be able to run SQL Server and Windows securely for 10 years. that probably stretches the lifetime, but certainly that gives me time after five years to begin planning and prepping for an upgrade.

    The problems for many customers come when other vendors don’t bother to keep supporting older software and providing patches. Even if Microsoft releases a security fix for your OS, the vendor that makes software you run on top of Windows or SQL Server might not. That’s an issue, and it’s one that will become more of an issue as companies become reluctant to change software that works well.

    This article shows that older ATM machines running XP and Windows 7 are having issues. Some of this is physical access, but some is related to issues in the OS. While Windows 7 should be getting patches, Windows XP is not and needs to be upgraded. In fact, I’d argue that any losses here should be born by ATM owners and not covered by insurance for not having upgraded their systems.

    While I like to get as much time as I can from a system, it’s irresponsible to expect software to run without security patches being actively applied, which includes any upgrades from vendors. In some cases, this might be negligent by the companies doing so, especially for embedded systems. I don’t know that I want governments to force vendors to provide patches, but I’d like companies to write contracts that ensure that patches will exist for come lifetime of the products. In the event the company can’t provide patches (or certification), then they’d need to release their code as open source so that someone can provide a patch.

    The world becomes more intertwined and dependent on computing, from things like convenience services or core systems. We can’t have those systems becoming more vulnerable because companies are unwilling to upgrade and vendors abandon older software. Some sort of compromise is needed to ensure that computer systems are protected from known vulnerabilities.

    Steve Jones

  • Sizing New Servers

    I ran across a post this week from Joe Chang on server sizing. It’s short and talks about some of the issues that you might consider when looking at a single socket v multi-socket systems. What first caught my eye, however, was the opening sentence: “Standardizing on 2 and 4 sockets systems for servers has been an established practice going back to 1996”.

    I think that’s been my experience overall. For most of my early career, we often did purchase 2 or 4 socket systems. A few times I had 8 sockets, but hardware costs and licensing were high. When VMs became the preferred method of building servers, we tended to just ask for 2 or 4 vCPUs. If the system ran slow, we just doubled the vCPUs.

    Time has moved one and hardware has advanced at incredible rates. These days licensing by core has really changed the way that I look at hardware. I couldn’t tell you how many sockets servers have, as I’d likely just ask for a VM with x CPUs (and lots of RAM) allocated. Whether the system was 1, 2, 4, 8, or more sockets wouldn’t be a consideration. In fact, I’ve somewhat given up on trying to track which CPUs have what cores and what the best choice is. I take the simplistic view of a core is a core and the hardware geeks will figure out how to get those into my VM.

    I wonder how many of you actually worry about the hardware in your system beyond gross layout? Do you dig into tracking which CPUs need to be inside a physical box, the type of RAM layout, the drives (beyond size and count)? Or are you like me. Your system is JBOC (just a bunch of cores), JBOR (just a bunch of RAM), and JBOD. If things run slow, after fixing code, I usually JWM (just want more of something).

    Certainly there is a need for someone to pay attention to hardware details, perhaps if for no other reason than to ensure that price/performance is being considered and there are spare parts available. For me, as a data professional, I’ve tended to just look at the performance needed from a system and ask for that. Even in my last position as a DBA, my concern was the SQL Server process and how it worked, limiting my hardware concerns to knowing the CPU count, RAM size, and number of disks. I’ve abstracted away hardware for the most part, focusing on a higher level of the system.

    For those times when I do care about hardware, such as when laptop shopping, a simple query of a few colleagues or a tweet nets me enough information to make a choice. After all, that’s what friends are for.

    Steve Jones