Voice of the DBA

Author: way0utwest

  • Half Baked Features

    I gave a talk recently on some of the data protection features being added in SQL Server 2016, along with a few that have been available for some time. I think the talk went well, but I point out lots of potential issues with the features themselves. I had a question from someone that noted Always Encrypted, Dynamic Data Masking and more aren’t really full developed, are they?

    The question threw me, not because I agree, but because I think that there is a complex answer. Encryption and data security features are easy to use. The features really are easy to implement, especially encryption. Most encryption is just another function call for the developer, which is something almost all of us can write. The mechanics of using these features are fairly easy.

    However the architecture, the decisions on how to manage keys and where to deploy features, those are hard. We deal with those relatively infrequently, but when we must make those decisions, we should carefully consider the ways in which our systems might be attacked or mis-used.

    Some of the restrictions that I see in various features are unavoidable. At least, I can’t see a way to avoid them. In some sense, things like a binary collation are almost required because of the nature of how encryption must operate on data. Perhaps there are ways to mitigate issues, but I’m not sure. No security mechanism is perfect and all encryption can be broken. However I think the way these features work is good enough in many situations.

    There are some things, such as allowing Dynamic Data Masking on Always Encrypted columns, which can be implemented. However, the changes are more complex, and involve not just SQL Server changes, but probably also ADO.NET changes. Making the decision on how to actually implement these changes shouldn’t be taken lightly, and I’m happy to have a working feature that might be enhanced in later versions over not getting any options at all in this version.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Software Maturity with Release Management

    For most of my career, releasing new versions of software that I or others had written consisted of manually running scripts or copying files, maybe using an installer once in awhile, but that was rare. In many cases, the process of actually deploying the software involved a developer sitting with an operational person, especially if the operational person had been burned by previous deployments that were poorly executed. Once burned, you usually require the person building the scripts to be available when they are run.

    That’s a poor way of managing the release of software. We write code to allow computers to repeat the instructions we’ve given them over and over. Computers are good at that, and they don’t make mistakes the third or fifth or hundredth time they execute the instructions. We might not give them the correct instructions, but computers don’t make mistakes and do execute what we code. If we find mistakes, we fix them and let the computer then execute them again.

    However it seems that for many individuals that release software, they want to do it manually. Especially when the release is to just one machine (or database) inside their organization. And time and time again, I find that these people spend time building scripts, correcting them, and still finding mistakes it production deployments that they need to fix. Often these fixes are made on the fly, with no testing.

    There’s a good mantra in software that if something is painful, do it more often. Make it easier with automation and execute it multiple times. Learn to code it better. There’s a whole industry that has grown up to help you release software more often, multiple times, and in a controlled manner. These are release management (RM) tools like Octopus Deploy and TFS Release Management. I’ve been using both of these internally, and I couldn’t imagine not using an RM tool at any point in the future. It should be easy to deploy software from one machine to another, and the process should be repeatable. That’s a sign of mature software development.

    If you’re not using a RM tool, I’d urge you to check one out. Most let you get started for free, and a small Proof of Concept (POC) project is the way to get started. Once you learn the ins and outs of how you really deploy software, you will never want to do it manually again.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Proliferation of Roles

    The best practice guidance for SQL Server security is to use roles for permissions, instead of granting rights to users. I’ve always followed this guidance in my career. I’ve learned that if one person needs access, sooner or later someone else will. Even if it’s an automated process, I’ll use a role so that I can build a test login to ensure I’ve configured things correctly.

    In small companies, or in relatively static applications, this makes perfect sense and few people argue with the rule. That is, until they get some requirement that only one account will ever be used. Then they want to just grant rights to a user. After all, why add the role for one person. See my thought above. Sooner or later, someone else will want access.

    However I also have had people complain that if each new required position or process needs their own role, sooner or later we’ll have this proliferation of dozens of roles.

    True.

    I worked in a large Fortune 100 company and we had thousands of groups in our AD forest, and easily dozens of roles in many databases. While that might seem complex and confusing, it wasn’t bad. We named roles to match AD groups or job functions, and adding in new users was simple as we usually mapped them to the same roles as a previous user. If a new system or person needs access, usually their access is the same as some other account.

    However, I know they can be complex, so I’m wondering if you have any tips, tricks, hints, or even gotchas for using roles. I’d also be curious if you think there are cases where roles don’t make sense.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The New Global Dashboard

    Redgate recently released SQL Monitor 5.2, which is the latest upgrade to our monitoring/alerting/troubleshooting product for DBAs. This was the big change that the team has spent a lot of time developing and refining. It’s been available for a few weeks as a hidden URL, but with 5.2, this becomes the default main screen for SQL Monitor.

    I think it’s a good move forward. In general, I don’t like things moving around physically in applications, as I get used to them being in a certain spot, or I expect them. However in this case, it makes sense.

    In a dashboard for monitoring and alerting, you want to know what’s broken. Having a list of 20 servers at the top, and 1 broken one potentially “beneath the fold” (in newspaper parlance) and requiring scrolling would be bad. As a DBA, I’d want to see those items that are problematic. When I look at the monitor.red-gate.com site, I see:

    2016-05-12 08_39_11-Global Dashboard

    The cluster has a long running query, which is an active, high priority alert. If I were to clear this, along with the other active alerts, this “card” would move to the end, and the sm-cluster2 item would take the top left spot.

    Note there are options to configure what is a high or low priority, and even pin specific servers at the top, but the general behavior is to let you know what’s broken now.

    There’s one other cool feature in this. If I have a high level alert, like a machine unreachable, and the alert clears itself (the machine reboots), I may see the machine as “green” on the dashboard when I login. This is because current alerts are shown, not historical ones. I can still get the historical data, but the intention is to make this a responsive tool for right now, not last night.

    I think this is a great change, and I’m excited to see how well it works in practice as customers roll this out.