Voice of the DBA

Tag: software development

  • Considering Other Views

    I love my Pebble 2 watch. It’s simple, the charge lasts for days, and it does what I want out of a device. However, it’s a device that isn’t made anymore, and after a fall, I have a crack in the screen. Since I like having some sort of watch in my wrist, and I like gathering some health data, I started looking for other options. I was reading a review of the Fitbit Versa the other day, and the first part of the review was a literal problem with much software design I’ve seen recently.

    The first item in the review talks about notifications. Those are valuable on a device like this, and one of problems is text size. There’s a quote in there: “The text size is tiny, even when you select the item”. That’s a problem I’ve had with many devices, including the Pebble. In fact, I see this in software overall as a problem with design. It’s one that comes about because I think that far too often we don’t consider a wider range of viewpoints.

    I’m getting older. A consequence of that is my eyes do not focus as well as they used to and I need to increase the fonts on screens. I can easily do this in browsers, and make SSMS fonts larger in most cases, but not all. I can increase the size of some things in Windows, but that can cause issues in others. My phone allows some font changes, but that can be limited. There are times when notifications or other text is hard to read, and this limits the usefulness of these features.

    Technology has been growing and expanding as I’ve aged. However, it seems that more and more often companies are using people in their 20s and 30s to design systems that will increasingly be used by an aging population. From dashboards in vehicles to labels in applications, it seems that far too many designers don’t consider the impact of their font, icon, and graphic choices on older eyes. Even the design of our operating systems don’t seem to have deeply embedded extensive flexibility of changing text and icon sizes. Sure we can alter resolution, but that’s a very intrusive operation and may break other things. Often we just need labels enlarged.

    Software and UX design are hard, and often we get caught up in our own viewpoint of how to build an application. We won’t ever completely solve that as users will always have different ways they want to work with our systems. The ability to change options, and especially deal with accessibility choices will grow as our systems move out to a wider and wider audience. What might have seemed simple and intuitive may be more complex for new users that haven’t evolved with out system.

    Our database software likely isn’t going to be often seen by end users, and certainly we don’t have control over the tools they use, but we still ought to consider  how our objects will be used by others. More descriptive names, extended properties that tools can read, and even views to remap complex structures are good ways to provide an easier interface to the data for report designers and third party tools. Those items come with a cost and have to be maintained as we change schemas, but that might be a small price to pay if we can prevent lots of support tickets requesting details about what OrdrLnPrc means.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.6MB) 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.

  • Great Developers Use Source Control

    I was rewatching Ike Ellis (b|t) talk on the habits of Great SQL Developers from SQL in the City 2018, and his first item was “Use Source Control“. I happen to agree with Ike, which is why I’m writing this, and I really hope you do as well. Certainly at Redgate we’ve built tools that help you get your T-SQL code into a VCS, but whether you want to work manually, use someone else’s tool, use our SQL Source Control or ReadyRoll, I’d ask that you consider getting all your code into some sort of VCS (Version Control System).

    Ike notes that if viewers did this one thing, he’d be thrilled. I agree. Please, learn to use version control. If you wonder why, listen to Ike’s talk. He relates a story that notes that using source control doesn’t make you a better developer, but that better developers do use a VCS. This is a habit that helps build better habits and is a step on the journey to you becoming a great developer.

    Does it help? Well, I think it does in some sense. Developers that use a VCS often build a habit of checking in changes before they try something that might be problematic. They also in a more integrated fashion with their work, and easily rollback problematic code without wasting time (or focus) trying to undo something. They get a previous version back and move forward.

    I do think that this one thing changes the way you view code, and it provides you with a safety net. This is one of those skills that I’d really recommend you learning, as it will pay back it’s value tremendously over time as you learn to depend on the VCS and stop doing things like keeping multiple objects or files around, and trying to sort out what code is where. As you work with others, or even with your past self, you’ll learn to include better comments that help you change focus quickly and understand the particular reason behind a version of code. This will help you learn to be a better developer.

    There are numerous ways to get your T-SQL code into a VCS. There are tools, but there are plenty of PoSh or other scripting methods. In fact, every DBA should get in the habit of scripting out instance level objects (which most tools don’t handle). Store them away, and then repeat as you need to make changes. You might be surprised how often you’ll be glad you have the previous version of a job, a schedule, a linked server, or more. I learned to keep all my scripts, from replication to running a quick report for a business user, in a VCS. I’ve never regretted this choice.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Virtual Data

    Yesterday I republished an editorial from 2014 for the holiday. The topic was production subsets of data, which has been something that many data professionals have struggled with for years. Many of us have built scripts to delete, change, obfuscate, or alter production restores as a way of providing useful, but manageable development database sets. Or maybe it’s just some of us. I’m sure more than a few of us have given up on this task and just restored production databases in entirety to test and development systems.

    I changed over my career to become a fan of additively building a known dataset rather than deleting extra data. I advocate adding rows from production (properly masked/obfuscated) and maintaining this set over time as requirements change. However, this isn’t without it’s own administrative headaches. I think it’s easier, but this does require commitment from everyone to keep going over time. It’s certainly better than each developer adding their own 10 rows of data to a table for testing.

    A year ago, Redgate released SQL Clone, designed to solve some of these issues. Once an image is created, new databases for test and development and be provisioned in seconds. I found this to be an amazing product that really changes how I develop against databases, though it does require me to stop getting caught up trying to undo changes or manage a single database. Instead, I need to ensure I am saving code to version control and then build the habit to drop and rebuild a baseline database.

    As we’ve worked on SQL Clone, I’ve found that there are lots of companies that offer similar ways of virtualizing your data, giving you access to large, production scale systems in seconds. Data masking, obfuscation, and more are features, with some vendors requiring specific hardware. Others, like Red Gate, have software add-ons (Data Masker).  All of these products cost money, which can be an issue for many organizations, but I’m glad that this technology is growing and advancing. With GDPR and other draft legislation, many of us need to take better care of our data and build more secure architectures.

    Containers are another interesting way to virtualiza data, though they don’t solve the scale issues. If you can work with a smaller data set, and maintain that, then containers might provide a fantastic way for you to learn to build, teardown, and rebuild databases in seconds.

    The world of databases hasn’t changed a lot in some ways across my career, but in others, I’m amazed. Data virtualization is one of these areas, and if you haven’t trialed the technology, maybe you should give it a whirl this year.

    Steve Jones

    The Voice of the DBA Podcast

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