Voice of the DBA

Category: Editorial

  • Anonymisation Confusion

    The GDPR starts getting enforced in a few weeks. It’s been law for a couple years, but the authorities have given companies time to comply. I know various entities are frantically working towards compliance as I keep getting updates to Terms of Service. My company is among them, and we are diligently ensuring we can prove that we aren’t violating any rules. That’s good because I’m sure fines will reduce any bonus we might earn this year.

    As I’ve been reading over the law and talking with customers, I’ve learned quite a bit. Redgate Software builds products to help with compliance and we’re updating guidance and information on how to work with data. As I’ve helped to update information and explain concepts I keep running into the term “pseudonymisation”. If you listen to the podcast, you’ll probably hear me struggle to pronounce it, but more importantly, I was initially confused about what this actually meant.

    The Data Protection site from Ireland has a great description of how this differs from anonymisation. You can read through the document, but anonymisation means that the data can’t be some how reverse engineered to find the original data. In terms of privacy, an anonymised set of my data wouldn’t allow anyone to determine the data is about me.

    If the data is pseudonumised, data about me would be replaces with a token, but there might be other means of discovering a data set is about me. As an example, in an eCommerce system, you might have an order key in a dev data set that’s copied directly from production. However, my name would be replaced with something else, like Bob Smith. It’s not apparent that it’s my data, but the protection is limited. If the data were anonymised, the order key would be replaced as well to prevent reverse engineering.

    Many of us have gotten used to being lax with dev and test data, often just restoring from production. It’s handy, convenient, and allows you to find problems in production or verify changes using known values. The downside of this is that we have poor data security. There are no shortage of data breaches from dev and test systems. Certainly plenty of data has been lost from developer laptops as well. Even if you had your laptop encrypted, there’s no real excuse for using real data in less secure environments.

    We need to learn to use anonymised data, and become comfortable with the idea of working on secure data sets. That also means we need the skills to ensure we build good, useful, valid datasets with production-like characteristics.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Reading Through the Logs

    Have you ever tried to read a transaction log? I mean used a query against fn_dblog() to read data and try to reconstruct what happened with a transaction or a series of transactions? It’s a cumbersome process and takes a lot of knowledge, practice, and most importantly, patience. It’s not something I’d want to wish on anyone. There are a few products to help, but no one really does this that often, and it’s almost easier to just change some data by applying your own manual fixes.

    If you’re in the UK, you might have heard about the TSB bank meltdown. If you’re unlucky, you’ve been affected by the outage, which has been going on over a week as a system cutover failed. You can read some reporting about the plans, the rollout of some services, the initial problems , and the warning signs. If you go to the end of the third link, you’ll find this awesome tweet. Beans and bombs, he he.

    There are a lot of potential issues that we could discuss here. I’ve been a part of a failed rollout and I have sympathy for the IT staff dealing with this. The thing that I wonder about is the data. With the magnitude of customers (millions), the seemingly long list of places where things failed (notifications, scheduled payments, inquiries, etc.), and the rate at which people can bang on a system from their phones and various applications, how much data has been mangled and altered?

    I’d guess a lot, in which case, we aren’t just talking about updating rows on the basis of someone’s authority. Whoever is tracking through data needs to essentially read transaction logs, unwind the actions where data was converted incorrectly and then (potentially) subsequently changed. Then they need to work out the reversing entries. The database needs help from DBAs, developers, and probably financial staff to understand why things are in a state. Why are closed accounts are open, why payments are scheduled years in the future, where balances are, and more. With the possible cross contamination of data between accounts, this is an area where TSB needs to be thorough and careful.

    Data is important in today’s complex, interconnected world. There are certain areas where data problems are highly disruptive and can have lasting repercussions if mistakes are made by the data processors. The financial and medical areas certainly fit in these categories, and it’s sad that people are going to go through pain and problems that may affect them for years. Hopefully TSB will get things working soon and data issues corrected. If there’s one thing I learned from this is that for certain issues, I need to ensure I have my own paperwork to prove my side of the story.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Pride in Azure SQL Database

    There’s a series on Azure SQL Database from Jovan Popovic on the SQL Server Database Engine Blog. Jovan has written posts on why database management is easier, the scalability of the platform, and a great one that claims the database engine can’t die. I don’t know that I quite believe that, especially as the guarentee is 99.99% availability. I’d expect 100% if you really think the engine can’t die on you. In any case, Jovan clearly has some pride in his work on Azure SQL Database.

    What I think is interesting in the post on the ever living engine is this sentence: “Azure automatically handles patching, backups, replication, failure detection, underlying potential hardware, software or network failures, deploying bug fixes, failovers, database upgrades and other maintenance tasks.” This notes that all of these operations are completed in less than 0.01% of the database life, hence the 99.99% guarentee. While that’s not 0, it’s close, and more importantly, this is something that to which DBAs ought to pay attention.

    These are often the tasks that many organizations will hire someone to complete. These tasks are becoming less of a time sink as organizations move to infrastructure as code or cloud computing, though they don’t disappear entirely. However, these tasks are mundane, tedious ones in many cases that should be solved once and then deployed easily to multiple instances.

    Azure SQL Database and SQL Server share the same code base. Most features get built and tested in Azure and then will get merged into a release for a CU or new version of SQL Server.  This means that as Microsoft learns how to better build these features, they will migrate them to our boxed SQL Server versions. With success stories in Azure and strong marketing, I’d bet that more and more management will be questioning whether they need more people, or even any people to handle these tasks in the latest versions of SQL Server.

    Don’t panic if you’re a DBA working on SQL Server 2008/RS, 2012, 2014 or other older versions. Those editions still require your time and things will change slowly for plenty of companies. They won’t want to upgrade too many instances at once, especially when there are potential vendor costs as well. You will have a job for some time, and I don’t think that lots of those older instances are disappearing anytime soon.

    That also shouldn’t mean that you rest on your existing skills and don’t learn anything new. You ought to be sure you are beginning to learn more about PowerShell, Azure, automatic indexing, and more. Improve your skills and potentially give you more career options. Even if you don’t change jobs, you’ll enjoy the learning.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Intrinsic or Extrinsic

    I was listening to an interview recently that talked about life and why many of us do what we do. It was a piece that also discussed some of the problems with modern US society and some of the potential unexpected consequences of the way that we have evolved in this country. As the discussion between the individuals proceeded, there was one question that stood out to me.

    The question was about intrinsic v extrinsic motivators. One example given was playing the piano. If you sit down at home and choose to play because you enjoy it or it relaxes you, that’s an intrinsic motivation. There could be all sorts of reasons why, but essentially you’ve made a choice to participate because you want to do so. If you go play at a bar because you need to make money, or your parents force/push you to play, or something other reason that pressures you, those are extrinsic motivators.

    To be clear, one isn’t necessarily better or worse than the other, but they both affect you, as a person, differently. There are also likely a variety of different intrinsic and extrinsic motivations that you have for many of your actions. The world and life isn’t as simple as choices being the result of one of the other. Often our decisions are a blend of both.

    Today, I wanted to ask you to think about the reason you’re in your career. I assume most of you are working in technology, and you have various reasons for entering this work, some of which may not be valid anymore. Perhaps you’ve found new reasons to continue to work with data. You don’t have to publicly answer, but think about this.

    Why do you work with computers? Because you have to or you want to?

    I’m sure this is a blend of factors for you as it is for me. I started with computers because I wanted to. I didn’t have to work with them in school because we didn’t have them at first. Even through much of my university work, computers were not ubiquitous and certainly were not required for most of my classes. I chose to work with computers and technology because I really enjoyed it. Even later, since I needed a career, I chose to work with technology instead of other industries and picked databases, which weren’t my initial choice. I did move to databases primarily for money, though I’d done some development work and enjoyed the database aspect of it.

    Today, I do need to work, so I have some extrinsic motivation to continue on this path, but I also do enjoy technology, and I’d like to think that I’d continue to do this type of work even if I could make enough money in another area. My wife is different, and without money pressure, she likely won’t ever come back to technology.

    Think about your motivations and pressures today. I’d be interested in how you feel if you’re willing to share. Whether you are or not, take a moment and consider how you really feel about your career.

    Steve Jones

    The Voice of the DBA Podcast

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