Voice of the DBA

Category: Editorial

  • The Nightmare Letter

    I’m not sure if this imaginary GDPR letter is a nightmare, but I do know that in most of the organizations where I’ve worked, this type of request would result in a crash project for me. I’d be working long hours, contacting lots of people and trying to manage a complex spreadsheet of information about an individual. I’d like to think that I’d compile this information in a general sense to understand our data better and anticipate future requests, building a process that I could repeat, but I know that under pressure that might not always happen. I’m sure I’d grab some data without capturing and saving the metadata or query. I’d probably have to perform duplicate work when the next request came in.

    GDPR enforcement begins in a couple months, and organizations receiving this type of letter will have 30 days to respond. Companies can also charge a reasonable fee based on administrative costs for information requested. The fee that’s reasonable for getting a few of these letters a month might not be sufficient if hundreds or thousands of individuals start requesting this information, and I’m sure companies and authorities will be arguing about the rates.

    With the focus on privacy in the media, and the mishandling of data regularly by companies, I wouldn’t be surprised if there are going to be large numbers of requests by individuals. In fact, I’m wouldn’t be surprised if there are scripts or applications being built now to facilitate the ability for lots of individuals to ask for this information from companies about their data processing.

    Really all of this information should be documented and any decisions made about securing sensitive data should always be followed. Any organization should know how they handle data, where it’s stored, and how it’s secured. This is just practical and good administrative practice. The items about how data is processed and used are good business knowledge points. After all, should we be processing data without some justification for the resources involved? I think too often a company decides to implement some process without evaluating if it makes sense in the context of their mission. If it does, we should know why it does and be able to measure that. If it doesn’t, we ought to stop.

    If you do business in the EU or with EU citizens, you might wish to start ensuring you have a way to export the information requested in this letter. Being prepared for some of these items might make it much easier to respond to any or all of these requests.

    Whether you think this might happen to your organization or not, you might want to just save a copy of this letter. I know I will, with the idea that I might send this off to companies that store my data. Knowledge can help me protect myself by being aware of what’s being done with information related to me. If there are issues, having this information might help ensure my rights are protected. I’ll also be sure that I have a form letter to ask for removal of information. I’ve felt this wasn’t possible in the past, but at least in the EU, where I regularly travel, I can exert some control over my data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Small Changes and Big Impacts

    I’ve been doing some work with RLS lately (Row-Level Security). I think this is one of the most useful features added in SQL Server 2016, and I wish it had been available earlier in my career. I’ve had the need to secure multi-tenant databases, which is a chore. Updates and new functionality constantly brought stress on developers and DBAs that a small mistake would expose one client’s data to another.

    RLS isn’t perfect, and while it simplifies the way in which you can apply security to your tables, it does require strong T-SQL understanding and careful thought. While we often can link a user to a customer, we may decide that we also have administrators or super users that need to access all, or many pieces of data. What might seem like a small change, just adding a catch-all clause, might cause problems for performance. This article at MSDN shows the impact, which could be quite large for million (or billion) row tables.

    I  ran across a developer that was testing access and found performance to be much worse. They were querying AD with OR clauses to allow different groups access, and this ended up causing issues. There are a few ways that this could be solved, but everyone should understand that adding in functions to your code  requires more processing of data. This processing can dramatically impact your system, just like any other code, if you haven’t written efficient operations.

    That’s ultimately the litmus test for most code. Is you code written to work efficiently in your environment and with ever growing data sizes? There are certainly places where a trade off might make sense. If you have a system that will never pass many rows through the FORMAT() function, perhaps that’s acceptable. If you can’t be sure of the data size and it could be large, then you shouldn’t ever be using FORMAT().

    There are some great features in SQL Server, many of which can help you write better applications. You just need to use them appropriately and in the places where they will shine. RLS is one of these, but there can be a performance impact if you aren’t careful. Ensure that you learn to write better code, picking the patterns that work well. No matter what, also ensure that before you deploy your change, you run a full scale performance test to be sure your clients won’t be spending most of their time staring at a screen while some query runs in the background.

    Steve Jones

    The Voice of the DBA Podcast

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

  • A True Lift and Shift

    Microsoft bought a data center. That’s not news, after all, they’ve been building and buying data centers for some time as they look to expand their Azure footprint, as well as continue to power their own internal systems. As they’ve grown, they’ve also learned quite a bit and regularly redesigned their data centers, moving from racks to shipping containers, from large warehouses to outdoor facilities. They are always trying to become more efficient in how they not only power and cool the systems, but also manage the deployment of servers. They don’t want to rack individual servers, nor do they even want to have systems with unnecessary wiring, choosing to order custom motherboards and other hardware. In fact, they are looking to move beyond containers to ITPACs, whatever they are.

    I’m getting off track here, since I think data centers are really cool and well designed. I used to evaluate and rent space in them, so I get intrigued. Back to my point. Microsoft bought a data center.

    Is this news? I find it interesting because in this case, Microsoft bought a data center from Chevron for a facility valued at US$80mm. That’s not a lot for a data center, but it comes on the heels of a deal between the companies to use cloud services for Chevron and make Microsoft the primary provider. It seems like Chevron wants to get out of the data center business and just rent the services it needs from Azure. Since they work in the oil and gas industry, with lots of data analysis and predictive requirements, perhaps this makes sense.

    That’s a good sales tactic from Microsoft. Use our stuff, which can save you money, and by the way, we’ll buy your old DC so you don’t have to lose that investment. An argument like that might sway quite a few large customers, allow Azure to continue to grow, and also ensure a strong base for Azure revenues for some time to come. That kind of moves makes me think I ought to buy more Microsoft stock.

    This won’t make a difference for the smaller companies, but face it, many of them likely have a limited number of servers, dozens perhaps. There are still potential cost savings for them if they can move to a managed solution in the cloud, and I know quite a few Redgate customers that are considering a variety of moves, often a combination of IaaS and PaaS. Not everyone wants to move, but there are plenty of advantages for some companies to consider cloud type solutions.

    Microsoft Azure is growing quite a bit and I continue to be impressed with the capabilities and options available. From Managed Instances to Machine Learning on demand to Cognitive Services, I think there is tremendous opportunity for companies to use technologies that might have been cost-prohibitive just a few years ago. Of course, if you’re a basic, just need a RDBMS for an app, you probably are just as happy to run (or rent) space in a smaller data center, where you have more control over resources, options, and costs.

    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.

  • Ad Hoc Logs

    A long time ago I started working in a team of people as a general systems administrator. I worked in a team of six people managing a large, 1000+ node network with a number of servers. In my first exposure to SQL Server, we had a very unstable application that forced us to provide 24×7 support. With a couple of contractors, we had to ensure someone was on-site all the time, often working with Microsoft and our corporate developers to stabilize the applications. We were constantly trying new techniques to fix our application, and with staff stretched thin, we struggled to understand what might have happened in the previous 12 hours when we reported for work. At the time, I suggested leaving a text document on each server’s desktop, updated with a note for each change.

    That worked well and I brought that technique with me to future positions. In another job, we constantly remotely connected to systems, and having a standard file on the desktop was helpful. As we became more security conscious, and stopped using shared logins, we moved our logging to Exchange public folders. Every action taken by an admin needed to be cut and pasted into a new post. That wasn’t a perfect system, but we built habits over time and we had an audit trail that helped us in understanding the changes we’ve made and assisted in troubleshooting.

    Today I’m curious. I want to ask the question about the data corrections, those quick changes, those fixes that get production working. Do you log everything? Is there some system in place to ensure you know what’s happening?

    I’ve been wondering about this and thinking hard as the date for GDPR enforcement approaches. One of the items that I’d glean from the law’s text is that any change to correct data, any quick fix made, needs to have an audit trail. We need to prove that we know who, when, and why this change occurred. This is especially important if a data subject requested some correction. You’ll need to prove you actually performed the action.

    I’ve never worked anywhere that some admin (including myself) completely avoided connecting to a production machine and making some change. Sometimes we’ve had great auditing, often not, but ad hoc fixes and changes, especially in the heat of an issue, are a fact of life. I’ve learned to deal with it and try to build lightweight habits to help me capture those changes.

    Let me know today. How bulletproof is your auditing?

    Steve Jones

    The Voice of the DBA Podcast

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