Voice of the DBA

Author: way0utwest

  • First Wave of Sessions for PASSSummit Now Available

    The first wave of sessions for the #PASSSummit have been released. You can see the list at: http://www.pass.org/summit/2018/Learn/ConferenceSessions.aspx

    I am honored to have a session on the list. This year, I’ll be doing a session called Minimizing the Impact of Data Breaches in Dev and Test Databases. This is a partner session, and I’ll talk about some of the issues with data security in your development area and show some ways that you can mitigate the problems.

    There are some other really interesting sessions on the schedule, such as Ola’s  Analyzing Performance Problems Using XEvents, DMVs & Query Store and Faster SSIS from Andy Leonard.  Check out the complete list and start to find those sessions that will help you in your job. Use that information to help build a case for you to come to the Summit.

    I expect we’ll see releases of more sessions across the next few weeks or months. Keep an eye out for new sessions so you can update your schedule.

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

  • Relaxing on the Tab Management Terraces with #SQLPrompt

    I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I’ve written about a few of the other items on the map:

    Tabs, Tabs, Tabs

    If you’re like me, you have a lot of tabs open in SSMS all the time. In fact, sometimes I think I have far too many open at one time. Likely that’s a sign of some distraction and too little focus, but it’s a habit that goes back through decades of SQL work for me.

    I used to love isql/w, as a very stable editor for my work. When Enterprise Manager came, I still often used isqlw. SSMS came along, I wasn’t thrilled at all, as it was a big, heavyweight tool. I’ve grown to like it and I feel comfortable, but I still have crashes at times.

    That’s where tab management comes in. SQL Prompt reopens my tabs, which I greatly appreciate, especially as I don’t always remember to save some random code I’m testing. Usually lots of development stuff is in a database, but having the tabs open automatically is a life saver.

    I don’t often use the Tab History item, but it is handy. I ought to use it more, because it is a good way to look back through work I’ve been doing and sometimes find a piece of code that I’m searching for.

    The coloring is great, especially as I work on a lot of different databases. Many times the color fades into the background, so it doesn’t necessarily help me with not working on the right instance/database, but when I wonder if I’m on a dev or test system, coloring is really helpful. Plus it looks great in demos and keeps the audience focused.

    SQL Prompt has lots of great features, many of which I’ve written about. After formatting and intellisense, this is the thing that I use most often.

    Give SQL Prompt a try. You’ll love the way it helps you write code.

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