Voice of the DBA

Author: way0utwest

Don’t Get On This Page

The GDPR has been in effect for over a year. While the press has died down a little, outside of a few record fines, there is still plenty of activity. At Redgate, we have customers that still worry about compliance and are doing their best to ensure they properly handle and secure data. It’s a tough job, but one that many organizations need to continue to focus on.

And there’s a good reason. Who among you would want their boss to come talk to them about mishandling data? Who wants their boss to come after reading this page, with your organization and fine listed? I’m guessing most of us would prefer to not be on that page, or at least not want our boss to know.

There have been a lot of fines handed out, though most are relatively small. Still, every amount spent towards a fine is money that could be used for shareholders, investment, or even better security and systems to prevent future issues.

What’s interesting is that many of these fines aren’t for data breaches, but rather for other issues. There are some security issues (unauthorized access), and some inappropriate storage. There are also quite a few consent fines, where data is used without the appropriate permissions from the data subjects.

I find the list interesting, and I hope this is the type of thing that does drive change in organizations. Many of these fines might easily be eliminated with a few process and procedure changes and adding some security to prevent unauthorized access. While some companies might be willing to pay fines, I expect that subsequent amounts will rise, and it behooves organizations to change both their behavioral and technical practices.

This might be building archival processes, which would be one of the few ways that might reduce the amount of data that we need to manage and query. Hopefully, something that might improve performance for your clients.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

August 22, 2019
The First Rule

A long time ago I read a book called The First Law. It’s a legal thriller, and I’d recommend it. It has nothing much to do with databases, but I was reminded of the book and the rules of any particular process when I saw a post called The First Database Rule.

The post is from Seth Godin, who is really a marketing individual, but I do think his rule makes a lot of sense for clients. I also think that as both developers and database professionals, we forget that most of our clients would prefer to have our systems follow this rule: it should be as simple to fix an error as it is to make one.

I’ve worked in many systems, and with many people, that expect data entry or loads to go perfectly. They expect things to work and want to blame the process, the person, the file, or something else when something doesn’t work well. What might be worse sometimes is that we expect some data in the database to be immutable, like a PK, assuming that clients will have and enter all the correct data at once. Most of us do allow changes in our databases, but we don’t make it easy, especially if it’s a piece of data like a primary key.

This is one reason I dislike many natural keys as PKs. We make mistakes when we enter them. Heck, I’ve probably typed my name a million times on a computer keyboard, and I still make mistakes. Even in data entry forms. What’s worse, I have some auto-fill selections in my browser that are incorrect and I’ll select the wrong one at times.

Fixing mistakes ought to be easy. We ought to expect that we will get data wrong. We’ll load it wrong, we’ll transform it wrong, and our DBAs will type corrections wrong. Design the system to account for mistakes and ensure that problems can be quickly fixed. Whether by a client or a sysadmin.

Agility ought not to be just how quickly we can change the software. It needs to include the ability to change the data, which is the most important part.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

August 21, 2019
Adding SQL Change Automation to Jenkins for Database CI
In a previous post, I explained how to get Jenkins running in a container for your local CI work. In this one, I’ll expand on the process for database CI with a quick look at getting the SQL Change Automation plugin added to Jenkins

The first step here is to click the Manage Jenkins menu item from the home page.

This gives you a number of items on the right. Scroll down and you’ll find the Manage Plugins option.

Once you click this, you see a list of the installed plugins with updates in the right. You also see a few tabs for managing and adding plugins. Click over to the Available tab for plugins that you can add, but don’t have installed.

In the upper right is a Search box. I entered “redg” and you quickly see the list shrink to the one you want: the Redgate SQL Change Automation plugin.

Check the box next to the plugin (in the Install column) and then click “Install without restart”. I restart isn’t needed in this case to get the plugin to work. Once you click this, you will see the plugin status appear.

It should install quickly and let you know it’s done. I did this 4 or 5 times and every time it just worked.

At this point, you can add this plugin to your builds. When you have a project, this will be one of the possible options. In a future post, I’ll look at actually performing a build with the SQL Change Automation Plugin.

There are other posts in this series:
August 21, 2019
A Memory-Optimized Roundup – T-SQL Tuesday #117

The summary from my fourth T-SQL Tuesday hosting for #117. This time I was scrambling a bit, but since I’ve worked with a few customers in the last year that use MOT tables, I thought this might be a good topic.

Either everyone is on vacation or not many people think about them. Or maybe they aren’t interesting. In any case, here’s the roundup.

Rob Farley has a win with user defined table types that are used in MOT objects. Never knew this was a possibility. Extra points for mentioning source control and DevOps.

Kevin Chant notes he studied them for his MCSE, but hasn’t really found them useful. Mostly because of older versions and restrictions on the feature. Kevin likens this to filtered indexes, which are a nice feature, but rarely used.

Jess Pomfret writes about some of the limitations for a feature she hasn’t used. A good summary of what they are, but don’t let her turn you off of the feature. It has its place.

Ken Fisher has a few notes about why he hasn’t used them. Not enough memory in his systems, older versions, and he doesn’t write the code. Apparently I’ve given him some things to think about and play with, and I’m sure I’ll see more blogs from him soon.

Johan Ludvig Brattås explains how he improved performance with a MOT table.

August 20, 2019