Voice of the DBA

Author: way0utwest

  • Webinar: 6 Principles of the GDPR and SQL Provision

    On April 24, I’ll be hosting a webinar that talks about the GDPR and how you can help ensure compliance in your development environments. I’ll talk about some of the issues and show how SQL Provision can help.

    6-principals-of-gdpr-webinar

    The GDPR is complex, but it certainly does ask us to protect and prevent issues with data from development environments. There are many ways you can try and ensure you don’t have any issues, and I’ll show one with Redgate tools.

    Register today and I look forward to talking with you next week.

  • In Depth Learning at the PASS Summit

    This past week the pre-conference sessions for the 2018 PASS Summit were released. There’s a good list of topics and speakers, returning and new presenters, that you can choose from. If you’re planning on attending the Summit, I would review the options and if any of these are relevant to your position, I’d bundle the cost of a session into your request for Summit funding. I’ve seen plenty of people ask for the registration fee without a pre-con and then try to negotiate additional funds later. Do yourself a favor and ask for everything at once.

    Are these sessions worth the time and money? Most are, and a single day packed with information is often easier to digest than a full week. However, you’ll still have days of random learning slots during the regular conference, so plan on spending a day learning and then shifting into a different mode for the rest of the conference. There is a lot of information presented in an all day session, and you’re likely to miss some of it. Most presenters provide a good set of materials to go with the class, but you should plan on taking lots of notes and then spending a few hours here and there across the next month reviewing and practicing what you’ve learned.

    I’ve often found that a pre-conference session is the best way for me to dive deep into a topic that I haven’t had the time to investigate on my own. Time is a precious resource and finding time in my busy work and family life is hard, especially for a full week class. I’d still like to get to a week long sqlSkills class, but I can’t seem to find the time. Maybe after next year when I have no more children at home and fewer commitments.

    I used to take a computer to classes, but the last few years I’ve moved back to paper and pen. I find that sketching out notes by hand is quicker and easier, I ignore typos, and somehow the act of writing cements things in my mind more. I’d recommend you try both, though certainly having your phone around to take a picture of something is incredibly handy at times. I’ve tried OneNote and a digital pen on a laptop, but again, it’s not as quick or smooth as pen on paper. The one thing I did like is that I could add pictures to my notes.

    A conference is a large investment, whether you make it or your employer does. Adding in a day of training on a topic is often a relatively small incremental increase. It’s worth the time and cost, if you’re already going to be gone for multiple days. I’m not sure if I’ll get to a pre-con at the Summit this year, but I’m hoping to get a day of watching and learning on a single topic in at one of the events I attend this year.

    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.

  • Exploring the Caves of Code Analysis in #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 wrote about a few of the items, and this post continues on with a feature that was added last year to SQL Prompt, Code Analysis.

    Code Analysis

    One of the big leaps forward for computer science, in my opinion, was the development of various static code analysis (SCA) tools. These are automated programs that examine the structure of source code and look for potential issues with the way the algorithms are implemented. This was originally the job of a fellow programmer, and still is in many cases, but humans make mistakes, and reviewing someone else’s code is a tedious, somewhat boring task. Over time, many humans become worse at it as we look for certain issues, but may ignore others.

    Over time, SCA tools have included scanning for potential security issues, such as buffer overruns, which can easily permeate many systems if the developers do not follow coding practices designed to avoid issues. I wish we had such advances in SQL tools, but they’re not here yet.

    In SQL Prompt v9, Redgate added some SCA features. These were a set of rules that are used to scan your T-SQL code for potential issues. such as casting data types without specifying a length, or as the Caves of Code Analysis post shows, forgetting to qualify an object. In my example below, the green squiggly line below the code represents an SCA finding, and as I’ve hovered over the line, I see the warning about an old style join.

    2018-04-06 08_39_06-SQLQuery1.sql - DKRSPECTRE_SQL2014.SimpleTalk_1_Dev (DKRSPECTRE_way0u (55))_ - M

    Each of the rules is designed to highlight some issue that is known to be a potential problem. For the most part, these are useful tools and you should use these to examine your code. Some, however, aren’t useful and can be annoying.

    There is a dialog that allows you to enable or disable any of the rules, which are divided into different types. I often disable ST002, which deals with aliases. I prefer the old style equal sign as opposed to the AS syntax. This isn’t a code issue, so I don’t need the warning.

    2018-04-06 08_45_50-Sql Prompt - Code analysis rules

    This is a basic set of SCA tooling for T-SQL code, but it does serve to educate newer developers about the dangers of using certain patterns in their code. It also reminds experienced people if they’ve done something like used COUNT() in a test instead of EXISTS(). Those types of changes can often improve the overall quality of your code.

    Give SQL Prompt a try today and I’m sure you’ll be pleased with just how quicker you can write code and learn about the potential issues you’ve been including in your code.

  • Believing the Data

    This is interesting to me. Let’s leave aside the moral, ethical, or political sides of this. Let’s just look at the data. A cop gave a ticket to a self driving car. Apparently the officer thought the car was within 10 feet of a pedestrian. The logs for the car show that it was 10.4 feet away. A negligible difference to me, but that might matter in a court of law.

    As we use digital data more and more to control vehicles, devices, even behavior, we are going to have problems, complaints, and unforeseen issues amongst humans and computers. Certainly our laws need reforming to deal with the digital world, but we also will have some cultural transformations to undertake.

    Much of our arbitration in the world has dealt with how well humans argue or debate topics. We are inconsistent, easily confused creatures, yet we often use our judgment and impressions to make decisions about whether we believe one individual over another. This has often been the case in law, even as more science with cameras, DNA, and other forensic techniques have tried to provide definitive proof one way or the other.

    As we use more digital devices, there will be, or should be, more logs and audit records of how systems behave. This worries me a bit in Machine Learning systems, but experts seem to believe we can unpack the rationale for decision making if necessary. In any case, we need to learn to trust and believe in the data, even if our eyes and instincts lead us to different conclusions. This doesn’t mean that the data makes the decision, but we can’t discard the data because we don’t like it. Certainly context matters, and in the original case above, we’d want to consider velocity and acceleration, and possibly other factors to determine if the car was too close.

    Ultimately, I believe digital records will start to prevail in more circumstances than we might be comfortable with. We’ll slowly change, and likely subsequent generations will trust data more than we do, perhaps even more than their senses. To me, this means that those of us that deal with data systems need to ensure there is extremely strong security and integrity of data records, and that we disclose algorithms and data processing techniques in a transparent way. Trust requires knowledge, which requires transparency. That’s something we certainly need to improve on as an industry.

    Steve Jones

    The Voice of the DBA Podcast

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