Voice of the DBA

Author: way0utwest

DevOps Basics–Staging and Committing Changes

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

In the course of normal work, you’ll change your code files. Git requires that you specify those changes that you want to commit, and those that you don’t. This means I can make changes to a few files, but not commit all those changes.

For example, let’s say that I add a couple files to my repo. I’ll add the Tables/Log.sql and Views/LogView.sql to my repo. This will give me a status in git that shows these are new files. In this case, I see the folders as they are new as well.

If I add these files as being tracked, I’ll use “git add Tables” to add that folder and file. I get a new status.

Here my Log.sql file is being tracked (and the folder) as changes that are staged to be committed. If I commit now, I’ll get just that file added, but not Views\LogView.sql.

Staged and Changed

There is one strange thing I’ve run into, at least, strange to me. If I stage my Views folder, I’ll get this:

Now I’ll change the LogView.sql file, removing the SELECT * and adding columns. When I check the status, I now see the file in both the staged and unstaged areas.

This is allowed, but if I commit, I’ll get the original version of LogView.sql as it existed when I ran the git add command. See below that after my commit, I still see the modified file.

If I add and commit that file, I can use git log to see the actual changes. See the line in red with the – is the original line, while the green line with + is the change.

Git add and git commit are the main ways you’ll commit files. If you have issues with a GUI client, then this is a good way for you to debug and clean up your repo. Knowing the command line is always the best way to truly understand what is happening.

June 29, 2017
SSoL–Elevating Privileges

I don’t think most of us need to know Linux, but if you end up managing a system, it’s good to have a little idea of how to get around. This is a short series of posts as I remember the skills I used to have back in university.

One of the things that you might find the need to do on a Linux system is elevate your privileges. By default when we connect and work on Linux, we are working as normal users. We do this in Windows, but when we need more privileges, Windows will give us a UAC prompt. Linux doesn’t.

For example, when I want to check for updates of software, I use apt-get. That doesn’t work for my normal user:

Instead I need to use sudo to elevate privileges. You prefix a command with sudo, enter the root password, and you get elevated privileges, as shown here.

If you just enter sudo -i, then you get the shell to switch and all commands execute as root.

Don’t do this. DON’T. Work as a normal user until you need higher privileges. For the most part you don’t.

June 28, 2017
SQL Server is Lagging a Bit

I don’t expect this to last, or I hope it doesn’t, but SQL Server is lagging behind. I ran across a short piece on the SQL:2016 standards, and I know these standards aren’t necessarily an important consideration for most of us when choosing a database. The standards are a compromise from many industry professionals working together. However, they do influence the vendors a bit. The way the list is written makes me wonder if Oracle provides more input and resources, or exercises more influence than other platforms. I hope not.

In any case, I would expect that Microsoft to be able to make change and catch up quickly with their rapid engineering process. At least, they will if they want to, which means, do these things matter do you as a customer? Or maybe, do these things matter to the really large customers that use, or might switch to, SQL Server?

I’m not sure I understand all of the changes to the standards, but row pattern recognition looks like some sort of LIKE-across-a-row feature. I know there are people that might need this, but I worry about the performance implications of doing this. As we move to larger data sets, perhaps those that come from sensors and other large data producers, this might be an important feature. Hopefully the implementation perform well.

The one thing I expect is more JSON support. As much as I saw the value of XML and hated working with it, I see JSON as a better format, and one that developers use extensively. I do hope that SQL Server gets more extensive JSON support, though I expect plenty of people with a native JSON type to just start stuffing blobs in a column and spending lots of CPU cycles trying to de-serialize the values for queries. If this a native JSON type is an intermediate store before moving the data into a better format, I think this makes sense. However, we can’t prevent poor choices from database designers, and I would expect consultants to love this, as I’m sure more JSON creates more issues for them to fix.

I am glad that date/time work gets some attention in the standards, but since I can never remember the actual syntax and values, I don’t really care. I’ll depend on SQL Prompt to get me through changes here, and then try to get all data in yyy-mm-dd format so I am not confused. LISTAGG is interesting, but I expect JSON to be used more. There are other changes, and I’m not sure how valuable they are for most data professionals.

Adding small functions and features, even those that are used rarely, are ways that we can dramatically improve developer productivity. Looking back over a career, I’m glad that I don’t need to write sorting routines anymore. Using an ORDER BY or x.Sort() is a great time saver. There are certainly some poor implementations of some features, but we can often override and write our own implementations if needed. Or find workarounds that will meet our needs.

I do hope to see the T-SQL language grow and expand over time, with regular enhancements that might help us work with data in easier and more efficient ways in future versions.

Steve Jones

The Voice of the DBA Podcast

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

June 28, 2017
Classifying Sensitive Data

Our databases store all kinds of data in them, depending on the purpose of your database. Most of us create tables and store data in response to some requirement or necessity in our organizations. Our decisions should be driven by good design principles, and I’d hope they are, but we do need to find a place to put all the data that our system will receive.

Depending on the sensitivity or personal nature of data, we may have to alter the way we store data (perhaps encrypt it) or alter the security for parts of the system. This is almost an ad hoc, deal with it at that time task. The exceptions might be when I’ve had to conform to a regulatory statute, such as SOX, PCI, HIPAA, etc. In those cases, I’ve often had to ensure the entire database is protected in some way that ensures it meets the requirements of the statute.

The time when I have had to think about individual columns of data is usually when building a development database where potentially sensitive information can’t be transferred to development machines. In that case, because of the effort of changing data, I’ll try to build scripts that change out individual columns and ensure that sensitive data doesn’t get copied. However, the data that may be deemed sensitive for one company, isn’t always classified that way for another.

At least that’s been my experience. I shared some of this with the Redgate Foundry, who is running research into data classification. They’re looking for people to share opinions, but the project has me curious. I’ve always felt intuitively we could classify data in tables, but perhaps that’s too simplistic a way of looking at the problem. I know that legal groups struggle with some this problem with email and file server documents. Classifying the content in different ways is a challenge.

Is it the same in databases? I’m not sure. I don’t know if there complex rules needed or if this is a simple problem that we easily solve and rarely deal with. I’m curious from those of you that deal with highly regulated industries. Is data classification something that you work with often? How do you decide the data classes and does this impact your administration of the database? If you don’t classify the data, do you worry about the sensitivity of the bits in your database? Let us know today.

Steve Jones

The Voice of the DBA Podcast

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

June 27, 2017